hooyantsing's Blog

研讨_第1次课程_数据库优化

字数统计: 6.8k阅读时长: 36 min
2020/04/12

源辰研讨课

第1次课程

2020.04.12

内容

数据库优化[张影]

[入口]

cmd -> sqlplus

4f8de916c534e1d434e3994b2124983f.png

1.B+树索引

深度三层,根茎叶

ca221cbc020c82e705b3b996c509661b.png

purge关键字,真实删除表,索引约束全部清空

a1687e7b7e4000522d7e5feedaee0d08.png

B树练习脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
/*
经典三大特性
*/
--1. 索引高度较低(可快速定位) 索引高度较低的直观体验
--环境准备
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
drop table t4 purge;
drop table t5 purge;
drop table t6 purge;
drop table t7 purge;
create table t1 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1;
create table t2 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10;
create table t3 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100;
create table t4 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000;
create table t5 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10000;
create table t6 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100000;
create table t7 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000000;
create index idx_id_t1 on t1(id);
create index idx_id_t2 on t2(id);
create index idx_id_t3 on t3(id);
create index idx_id_t4 on t4(id);
create index idx_id_t5 on t5(id);
create index idx_id_t6 on t6(id);
create index idx_id_t7 on t7(id);
--接下来执行如下语句查询, 分析7张表的索引
-- blevel=0: 索引只有叶子块,高度为1. 注意:  t1到t7的 num_rows以10倍速增加,而索引增加速度极慢. 这说明索引的高度确实低.
select index_name,
blevel,
leaf_blocks,
num_rows,
distinct_keys,
clustering_factor
from user_ind_statistics
where table_name in( 'T1','T2','T3','T4','T5','T6','T7');

--2. 高度低有利于索引范围扫描
--只显示统计信息   此命令只能在   sql plus下执行,请参考: blog.csdn.net/zhanglin_1214/article/details/48806553
set autotrace traceonly stat;

解决方案:
1)以Sys用户登录  :     sys as sysdba
2) SQL> @?/sqlplus/admin/plustrce.sql
3) SQL > grant plustrace to public;

--语句1,针对t1表的索引访问和全表扫描访问,如下
select * from t1 where id=1;    --索引 访问
select /*+full(t1)*/ * from t1 where id=1;  --全表访问
--语句2,针对t2表的索引访问和全表扫描访问,如下:
select * from t2 where id=1;
select /*+full(t2)*/ * from t2 where id=1;
--语句3,针对t3表的索引访问和全表扫描访问,如下:
select * from t3 where id=1;
select /*+full(t3)*/ * from t3 where id=1;
--语句4,针对t4表的索引访问和全表扫描访问,如下:
select * from t4 where id=1;
select /*+full(t4)*/ * from t4 where id=1;
--语句5,针对t5表的索引访问和全表扫描访问,如下:
select * from t5 where id=1;
select /*+full(t5)*/ * from t5 where id=1;
--语句6,针对t6表的索引访问和全表扫描访问,如下:
select * from t6 where id=1;
select /*+full(t6)*/ * from t6 where id=1;
--语句7,针对t7表的索引访问和全表扫描访问,如下:
select * from t7 where id=1;
select /*+full(t7)*/ * from t7 where id=1;
/*
  请注意以下统计信息中的  consistent gets 部分。随着记录的增加,索引访问的优势越来越明显。
*/

--索引存储列值(可优化聚合)
--(1)索引特性之存列值优化count
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create index idx1_object_id on t(object_id);
set autotrace on;
select count(*) from t;
/*
   统计信息:  5  recursive calls
          0  db block gets
       1075  consistent gets
       用不到索引 ,因为列中有空值
*/
select count(*) from t where object_id is not null;
/*
统计信息:
       5  recursive calls
          0  db block gets
        224  consistent gets
      索引不能存空值 ,所以加入一个 is not null后即可。
*/
--修改代码让count用到索引
alter table t modify OBJECT_ID not null;
select count(*) from t;
/*
统计信息:
       145  recursive calls
          0  db block gets
        242  consistent gets
        
    另外:因为主键不能为空,所以主键一定能用到索引。
*/

--(2)索引特性之存列值优化sum avg
drop table t purge;
create table t as select * from dba_objects;
create index idx1_object_id on t(object_id);
set autotrace on;
set linesize 1000;
set timing on;
select sum(object_id) from t;

--sum avg不走索引 的代价
select /*+full(t)*/ sum(object_id) from t;
/*
10,11   实测差不多????
*/

--3. 索引本身有序(可优化排序)
--(1)索引特性之有序优化order by
set autotrace traceonly
set linesize 1000
drop table t purge;
create table t as select * from dba_objects;
select * from t where object_id>2 order by object_id;
/*
   输出信息中有:  1  sorts (memory)
          0  sorts (disk)
  小结:无索引的order by 语句必然会排序
*/
--
--索引让order by 语句排序消失
create index idx_t_object_id on t(object_id);
set autotrace traceonly
select * from t where object_id>2 order by object_id;
/*
   输出信息:  0  sorts (memory)
          0  sorts (disk)
*/

--(2)索引特性之有序优化Max/Min
--MAX/MIN 的索引优化
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
alter table t add constraint pk_object_id primary key (OBJECT_ID);
set autotrace on
set linesize 1000
select max(object_id) from t;
/*
系统信息:
         145  recursive calls
          0  db block gets
         86  consistent gets
          1  physical reads
*/
--MAX/MIN 语句用不到索引性能低下
select /*+full(t)*/ max(object_id) from t;
/*
  0  recursive calls
          0  db block gets
       1009  consistent gets
          0  physical reads
*/

--MAX/MIN 用索引与数据量增加的影响
set autotrace off
drop table t_max purge;
create table t_max as select * from dba_objects;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
select count(*) from t_max;
create index idx_t_max_obj on t_max(object_id);
set autotrace on
select max(object_id) from t_max;
/* 系统信息:
         5  recursive calls
          0  db block gets
         71  consistent gets
          2  physical reads
          
      有索引 后,查询最小值 或最大值 可以叶子块的最左边或最右边,比较快.
*/

--组合索引选用
--3. 仅等值无范围查询时,组合的顺序不影响性能
--环境准备:
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
update t set object_id=rownum ;
commit;
create index idx_id_type on t(object_id,object_type);
create index idx_type_id on t(object_type,object_id);
set autotrace off
alter session set statistics_level=all ;
set linesize 366
--type_id,id顺序组合索引
select /*+index(t,idx_id_type)*/ * from  t  where object_id=20  and object_type='TABLE';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--再用id、type_id顺序组合索引
select /*+index(t,idx_type_id)*/ * from  t  where object_id=20  and object_type='TABLE';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--以上两个语句性能一样,表明只有等值查询时,组合索引组合列的顺序不要紧。

--4. 组合索引最佳顺序一般是将等值查询的列置前
--将等值查询的列置前
select /*+index(t,idx_id_type)*/ *  from   t where    object_id>=20 and object_id<2000 and object_type='TABLE' ;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--将等值查询的列置后
select /*+index(t,idx_type_id)*/ *  from  t  where object_id>=20 and object_id<2000   and object_type='TABLE';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
/*
   在统计信息中观察buffer值 ,可以发现 索引 idx_type_id性能要优于 idx_id_type, 即等值列在索引中要放在前面,范围查询放在后面。
*/

--2.3 索引扫描类型的分类与构造
--1. INDEX RANGE SCAN
--请记住这个INDEX RANGE SCAN扫描方式
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);      --这是一个    索引范围扫描
set autotrace traceonly
set linesize 1000
--这里要注意  ownname是你登录用户的名字,我在 sql plus上的登录用户名为sys
exec dbms_stats.gather_table_stats(ownname => 'sys',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
select * from t where object_id=8;
/*
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |   101 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |   101 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     1   (0)| 00:00:01
*/

--2. INDEX UNIQUE SCAN
--请注意这个INDEX UNIQUE SCAN扫描方式,在唯一索引情况下使用。
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create unique index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;
/*
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
*/

--3. TABLE ACCESS BY USER ROWID
--请注意这个TABLE ACCESS BY USER ROWID扫描方式,直接根据rowid来访问,最快的访问方式!
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
--注意,这里连索引都没建!
--create  index idx_object_id on t(object_id);
set autotrace off
select rowid from t where object_id=8;
--ROWID
-----
--AAARDxAABAAAVeiAAH
set autotrace traceonly
set linesize 1000
select * from t where object_id=8 and rowid='AAARDxAABAAAVeiAAH';
/*
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |   219 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY USER ROWID| T    |     1 |   219 |     1   (0)| 00:00:01 |
*/

--4. INDEX FULL SCAN
--请记住这个INDEX FULL SCAN扫描方式,并体会与下面INDEX FAST FULL SCAN的区别
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select * from t  order by object_id;

--5. INDEX FAST FULL SCAN
---请记住这个INDEX FAST FULL SCAN扫描方式,并体会与上面INDEX FULL SCAN的区别
drop table t purge;
create table t as select * from dba_objects ;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select count(*) from t;
/*
-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    43   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 63284 |    43   (0)| 00:00:01 |
*/

--6. INDEX FULL SCAN (MINMAX)
--请注意这个INDEX FULL SCAN (MIN/MAX)扫描方式
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select max(object_id) from t;
/*
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |    13 |   275   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE            |               |     1 |    13 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_OBJECT_ID | 63284 |   803K|            |
*/

--7. INDEX SKIP SCAN
--请记住这个INDEX SKIP SCAN扫描方式
drop table t purge;
create table t as select * from dba_objects;
update t set object_type='TABLE' ;
commit;
update t set object_type='VIEW' where rownum<=30000;
commit;
create  index idx_type_id on t(object_type,object_id);
exec dbms_stats.gather_table_stats(ownname => 'sys',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;
/*
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    98 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |    98 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_TYPE_ID |     1 |       |     3   (0)| 00:00:01
*/

--8. TABLE ACCESS BY INDEX ROWID
--好好地体会前后两个试验,记住这个TABLE ACCESS BY INDEX ROWID
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create  index idx_object_id on t(object_id);
set autotrace traceonly explain
set linesize 1000
select object_id from t where object_id=2 and object_type='TABLE';
--在接下来的试验中,你会看到,哇塞,TABLE ACCESS BY INDEX ROWID消失了。
create  index idx_id_type on t(object_id,object_type);
select object_id from t where object_id=2 and object_type='TABLE';

--3 索引相关优化案例
--3.1 三大特性的相关案例
--1. 分区表各类聚合优化玄机
--语句1:
select max(nbr) max_nbr
from range_part_tab
where deal_date >= TO_DATE('2015-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2015-06-01', 'YYYY-MM-DD');

--走全表扫描   table access full
--------------------------------------------------------------------------------------------
--接下来看语句2:
select max(nbr) max_nbr from range_part_tab partition(p_201505);
--用  index full scan(min/max)
--------------------------------------------------------------------------------------------
--语句3:
select count(*) max_nbr
from range_part_tab
where deal_date >= TO_DATE('2015-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2015-06-01', 'YYYY-MM-DD');
--走全表扫描
--------------------------------------------------------------------------------------------
--语句4:
select count(*) max_nbr from range_part_tab partition(p_201505);
--用  index fast full scan

--------------------------------------------------------------------------------------------
--啥时分区索引性能反而低
--假设有两张表 part_tab,norm_tab,前者为分区表,后者为普通表,记录数一样,在两个表的 col2列都有索引下,比较
-- select * from xxx where col2=8的性能
--环境准备
drop table part_tab purge;
create table part_tab (id int,col2 int,col3 int)
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p6 values less than (60000),
partition p7 values less than (70000),
partition p8 values less than (80000),
partition p9 values less than (90000),
partition p10 values less than (100000),
partition p11 values less than (maxvalue)
);
insert into part_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create  index idx_par_tab_col2 on part_tab(col2) local;
create  index idx_par_tab_col3 on part_tab(col3) ;
drop table norm_tab purge;
create table norm_tab  (id int,col2 int,col3 int);
insert into norm_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create  index idx_nor_tab_col2 on norm_tab(col2) ;
create  index idx_nor_tab_col3 on norm_tab(col3) ;
--------------------------------------------------------------------------------------------
--分区表局部分区扫描的情况
set autotrace traceonly
set linesize 1000
set timing on
select * from part_tab where col2=8 ;
/*
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                  |     1 |    39 |    13   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |                  |     1 |    39 |    13   (0)| 00:00:01 |     1 |    11 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PART_TAB         |     1 |    39 |    13   (0)| 00:00:01 |     1 |    11 |
|*  3 |    INDEX RANGE SCAN                | IDX_PAR_TAB_COL2 |     1 |       |    12   (0)| 00:00:01 |     1 |    11 |
--------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
         52  recursive calls
          0  db block gets
        153  consistent gets
*/
--普通表索引扫描的情况
select * from norm_tab where col2=8 ;
/*
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NORM_TAB         |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_NOR_TAB_COL2 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
         32  recursive calls
          0  db block gets
         80  consistent gets
*/
--小结: norm_tab对应的sql 的consistent gets为153,而分区表part_tab对应的sql的consistent gets为 80
--这个表有分区,但sql却没有这个分区条件,导到处该分区表的局部索引从pstarg1到pstop11遍历11个分区。
--所以当分区表的分区条件无法加上时,全局索引性能要好于分区索引 。

--------------------------------------------------------------------------------------------
--3. 同时取最大最小值的案例
--环境准备
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
alter table t add constraint pk_object_id primary key (OBJECT_ID);
set autotrace on
set linesize 1000
--看看执行计划是什么:
set linesize 1000
set autotrace on
select max(object_id),min(object_id) from t;
/*
执行计划
----------------------------------------------------------
Plan hash value: 1265209789

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |    13 |    40   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 54956 |   697K|    40   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
        145  recursive calls
          0  db block gets
        224  consistent gets
        142  physical reads
          0  redo size
        494  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/
/*
小结: 执行计划并没有走高效的index full scan (min/max)扫描方式,而是走index fast full scan模式。
这是因为oracle不能同时在索引 相同的两段寻找最大值 和最小值 。
以上语句修改成以下利用笛卡尔积的查询方式完成.
*/
--同时取最大最小值的语句的改造写法
select max, min
from (select max(object_id) max from t ) a,
(select min(object_id) min from t ) b;
/*
执行计划
----------------------------------------------------------
Plan hash value: 3319831621

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     1 |    26 |     4   (0)| 00:00:01 |
|   2 |   VIEW                       |              |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |              |     1 |    13 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 54956 |   697K|     2   (0)| 00:00:01 |
|   5 |   VIEW                       |              |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |    SORT AGGREGATE            |              |     1 |    13 |            |          |
|   7 |     INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 54956 |   697K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        118  consistent gets
          0  physical reads
          0  redo size
        472  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/
--性能在 consistent gets上得到了提升.

--------------------------------------------------------------------------------------------------------------------
--3.2 组合索引的经典案例
--1. 组合索引的写法
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum ;
create index idx_id_type on t(object_id,object_type);
UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;
UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;
COMMIT;
set linesize 1000
set pagesize 1
alter session set statistics_level=all ;
select  /*+index(t,idx1_object_id)*/ * from t  where object_TYPE='TABLE'  AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
/*
执行计划
----------------------------------------------------------
Plan hash value: 3713220770

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |       |       |            |          |
----------------------------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
         19  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1512  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         18  rows processed
*/
--组合索引与In写法
select  /*+index(t,idx_id_type)*/ * from t t where object_TYPE='TABLE'  AND  OBJECT_ID IN (20,21);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
/*
执行计划
----------------------------------------------------------
Plan hash value: 3713220770

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |       |       |            |          |
----------------------------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
         14  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1512  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         18  rows processed
*/

--------------------------------------------------------------------------------------------
--2. 组合索引与增加检索条件
--环境准备
drop table t purge;
create table t as select * from dba_objects;
UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;
UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;
Update t set object_id=22 where rownum<=10000;
COMMIT;
create index idx_union on t(object_type,object_id,owner);
set autotrace off
alter session set statistics_level=all ;
set linesize 1000
--写法1  未增加OBJECT_ID列的写法
set autotrace on
select * from t where object_type='VIEW' and OWNER='SYS';
/*
执行计划
----------------------------------------------------------
Plan hash value: 1570829420

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  3286 |   664K|    42   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |  3286 |   664K|    42   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_UNION |    30 |       |    40   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE"='VIEW' AND "OWNER"='SYS')
       filter("OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        595  consistent gets
          0  physical reads
          0  redo size
     125958  bytes sent via SQL*Net to client
       2935  bytes received via SQL*Net from client
        231  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3445  rows processed
*/ 
--写法2   增加OBJECT_ID列的写法
select /*+index(T IDX_UNION)*/
* from t T where object_type='VIEW'
and OBJECT_ID IN (20,21,22)
AND OWNER='LJB';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
/*
执行计划
----------------------------------------------------------
Plan hash value: 3713220770

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |       |       |            |          |
----------------------------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
         18  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1512  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         18  rows processed
*/ 
--小结:三列组合索引的特点:互相依赖。 第三列依赖第二列,第二列依赖第一列

2. INDEX UNIQUE SCAN
--请注意这个INDEX UNIQUE SCAN扫描方式,在唯一索引情况下使用。
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create unique index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;

2.位图索引

与位运算

71913b2310a20ec6ca758c244352d213.png

3.函数索引

a01c1bc973b2299d7674173c67eff852.png

4.反向键索引

解决热块竞争问题。反转rowid,达到块索引记录随机分布的效果。

热块竞争:例如文章通过发布时间索引,那么最旧的文章放在树的左侧,最新的文章放在树的右侧。由于帖子展示总是最新的文章,因此会有大量的流量访问最新的块,造成竞争块。

dca24a5682b75b3e9b4ff02896a87bad.png

0468949438a52b760619c809a47b7159.png

5.全文索引

b0e4691d022228abdad07645b0fa98ae.png

除B树练习脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
--2.1 位图索引
--1. 位图索引之如何高效即席查询
--做位图索引与即席查询试验前的准备:
drop table t purge;
set autotrace off
create table t
(name_id,gender not null,location not null,age_group not null,data
)
as
select rownum,decode(ceil(dbms_random.value(0,2)),1,'M',2,'F')gender,ceil(dbms_random.value(1,50)) location,
decode(ceil(dbms_random.value(0,3)),1,'child',2,'young',3,'middle_age',4,'old'),rpad('*',400,'*')
from dual
connect by rownum<=100000;
--注意,以下收集统计信息操作必须先执行。
exec dbms_stats.gather_table_stats(ownname => 'sys',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
--------------------------------------------------------------------------------------------
--即席查询中应用全表扫描的代价:
set linesize 1000
set autotrace traceonly
select *
from t
where gender='M' and location in (1,10,30) and age_group='child';
/*
执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   237 |  1614   (1)| 00:00:20 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   237 |  1614   (1)| 00:00:20 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR
              "LOCATION"=30) AND "AGE_GROUP"='child')

统计信息
----------------------------------------------------------
        116  recursive calls
          0  db block gets
       5995  consistent gets
       5940  physical reads
          0  redo size
      13993  bytes sent via SQL*Net to client
        889  bytes received via SQL*Net from client
         45  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
        658  rows processed
        
        全表搜索     回表的代价高  consistent gets 5995
*/
--------------------------------------------------------------------------------------------
--即席查询中应用组合索引的代价
drop index idx_union;
create index idx_union on t(gender,location,age_group);
select *
from t
where gender='M' and location in (1,10,30) and age_group='child';
/*
执行计划
----------------------------------------------------------
Plan hash value: 306189815

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     3 |   711 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T         |     3 |   711 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_UNION |     1 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR "LOCATION"=30)
              AND "AGE_GROUP"='child')

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        698  consistent gets
          5  physical reads
          0  redo size
      13048  bytes sent via SQL*Net to client
        889  bytes received via SQL*Net from client
         45  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        658  rows processed
        
         INDEX RANGE SCAN   搜索     回表的代价次之  consistent gets   698
*/ 
--------------------------------------------------------------------------------------------------------------------
--即席查询应用位图索引,性能有飞跃,Oracle自己选择了使用位图索引:
drop index idx_union;  --删除上面的组合索引
create bitmap index gender_idx on t(gender);
create bitmap index location_idx on t(location);
create bitmap index age_group_idx on t(age_group);
select *
from t
where gender='M' and location in (1,10,30) and age_group='child';
/*
执行计划
----------------------------------------------------------
Plan hash value: 642874377

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |   237 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T             |     1 |   237 |    11   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS |               |       |       |            |          |
|   3 |    BITMAP AND                 |               |       |       |            |          |
|   4 |     BITMAP OR                 |               |       |       |            |          |
|*  5 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          |
|*  6 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          |
|*  7 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          |
|*  8 |     BITMAP INDEX SINGLE VALUE | GENDER_IDX    |       |       |            |          |
|*  9 |     BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX |       |       |            |          |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("LOCATION"=1)
   6 - access("LOCATION"=10)
   7 - access("LOCATION"=30)
   8 - access("GENDER"='M')
   9 - access("AGE_GROUP"='child')

统计信息
----------------------------------------------------------
        155  recursive calls
          0  db block gets
        671  consistent gets
         16  physical reads
          0  redo size
      13993  bytes sent via SQL*Net to client
        889  bytes received via SQL*Net from client
         45  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
        658  rows processed
        
         BITMAP CONVERSION TO ROWIDS  搜索     回表的代价次之  consistent gets   671
*/

--------------------------------------------------------------------------------------------
--2. 位图索引之如何快速统计条数
--Count性能试验的环境准备
drop table t purge;
set autotrace off
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
update t set object_id=rownum;
commit;
--------------------------------------------------------------------------------------------
--场景1   Count(*)应用全表扫描的代价
set autotrace on
set linesize 1000
select count(*) from t;
/*
执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 17392   (1)| 00:03:29 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  4865K| 17392   (1)| 00:03:29 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
     128107  consistent gets
      65573  physical reads
       5156  redo size
        422  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/
--------------------------------------------------------------------------------------------
--场景2,  Count应用普通索引的代价
create index idx_t_obj on t(object_id);
alter table T modify object_id not null;
set autotrace on
select count(*) from t;
/*
执行计划
----------------------------------------------------------
Plan hash value: 278572740

---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |  2802   (1)| 00:00:34 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_T_OBJ |  4865K|  2802   (1)| 00:00:34 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
        145  recursive calls
          0  db block gets
      10366  consistent gets
      11490  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/
--------------------------------------------------------------------------------------------
--观察COUNT(*)用位图索引的代价
create bitmap index idx_bitm_t_status on t(status);
select count(*) from t;
select count(*) from t;
/*
执行计划
----------------------------------------------------------
Plan hash value: 4272013625

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |    98   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE               |                   |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |                   |  4865K|    98   (0)| 00:00:02 |
|   3 |    BITMAP INDEX FAST FULL SCAN| IDX_BITM_T_STATUS |       |            |          |
-------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        115  consistent gets
          0  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/

--------------------------------------------------------------------------------------------------------------------
--2.2 函数索引
--1. 函数索引妙用之部分记录建索引
--首先看一个例子,普通索引的情况,如下:
drop table t purge;
set autotrace off
create table t (id int ,status varchar2(2));
--建立普通索引
create index id_normal on t(status);
insert into t select rownum ,'Y' from dual connect by rownum<=1000000;
insert into t select 1 ,'N' from dual;
commit;
analyze table t compute statistics for table for all indexes for all indexed columns;
set linesize 1000
set autotrace traceonly
select * from t where status='N';
/*
执行计划
----------------------------------------------------------
Plan hash value: 2252729315

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |     1 |    10 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_NORMAL |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STATUS"='N')

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        471  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/
--看索引情况
set autotrace off
analyze index id_normal validate structure;
select name,btree_space,lf_rows,height from index_stats;
set autotrace off
analyze index id_normal validate structure;
select name,btree_space,lf_rows,height from index_stats;
/*                                           索引叶子数        高度
ID_NORMAL                         15992192    1000001          3
*/
------------------------------------------------------------------------------------------
--建函数索引的情况:因为绝大部分记录都是y,只有极少数 n,所以对n的情况建立索引 。
drop index id_normal;
create index id_status on  t (Case when status= 'N' then 'N' end);
analyze table t compute statistics for table for all indexes for all indexed columns;
/*以下这个select * from t where (case when status='N' then 'N' end)='N'
写法不能变,如果是select * from t where status='N'将无效!笔者见过有些人设置了选择性索引,却这样调用的,结果根本起不到任何效果!
*/
set autotrace traceonly
select * from t where (case when status='N' then 'N' end)='N';
/*
执行计划
----------------------------------------------------------
Plan hash value: 1835552001

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_STATUS |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')

统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        471  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
*/
--接着观察id_status(即函数索引)索引的情况
set autotrace off
analyze index id_status validate structure;
select name,btree_space,lf_rows,height from index_stats;
/*
                                                  索引叶子数   高度
ID_STATUS                             8000          1          1
*/

------------------------------------------------------------------------------------
--2. 函数索引妙用之减少递归调用
--首先构造自定义函数的环境,如下所示:
drop table t1 purge;
drop table t2 purge;
create table t1 (first_name varchar2(200),last_name varchar2(200),id number);
create table t2 as select * from dba_objects where rownum<=1000;
insert into t1 (first_name,last_name,id) select object_name,object_type,rownum from dba_objects where rownum<=1000;
commit;
create or replace function get_obj_name(p_id t2.object_id%type) return t2.object_name%type DETERMINISTIC is
v_name t2.object_name%type;
begin
select object_name
into v_name
from t2
where object_id=p_id;
return v_name;
end;
/
--------------------------------------------------------------------------------------------
--未建函数索引的函数调用性能
set linesize 1000
set autotrace traceonly
select *   from t1 where get_obj_name(id)='TEST'  ;
/*
执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |  2170 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    10 |  2170 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("GET_OBJ_NAME"("ID")='TEST')

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
       1175  recursive calls
          0  db block gets
      16064  consistent gets
         13  physical reads
          0  redo size
        398  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
*/
------------------------------------------------------------------------------------------------------------
--建自定义函数get_obj_name的函数索引
create index idx_func_id on t1(get_obj_name(id));
select *   from t1 where get_obj_name(id)='TEST'  ;
/*
执行计划
----------------------------------------------------------
Plan hash value: 4083325411

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    10 | 22190 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |    10 | 22190 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_FUNC_ID |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SYS"."GET_OBJ_NAME"("ID")='TEST')

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         14  consistent gets
          1  physical reads
          0  redo size
        398  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
*/

CATALOG