Oracle学习笔记索引执行计划中的关键字（十三）

Oracle学习笔记索引执行计划中的关键字（十三）

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 traceonlyset linesize 1000exec dbms_stats.gather_table_stats(ownname => 'LJB',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 | 207 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1394 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

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 traceonlyset linesize 1000select * 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 |---------------------------------------------------------------------------------------------|统计信息--------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1298 bytes sent via SQL*Net to client 404 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

TABLE ACCESS BY USER ROWID：索引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 offselect rowid from t where object_id=8; ROWID------------------AAAZxiAAGAAAB07AAHset autotrace traceonlyset linesize 1000select * from t where object_id=8 and rowid='AAAZxiAAGAAAB07AAH';执行计划-----------------------------------------------------------------------------------| 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 |-----------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 1391 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

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 traceonlyset linesize 1000select * from t order by object_id;执行计划---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 88780 | 17M| 1208 (1)| 00:00:15 || 1 | TABLE ACCESS BY INDEX ROWID| T | 88780 | 17M| 1208 (1)| 00:00:15 || 2 | INDEX FULL SCAN | IDX_OBJECT_ID | 88780 | | 164 (1)| 00:00:02 |---------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 10873 consistent gets 0 physical reads 0 redo size 8116181 bytes sent via SQL*Net to client 54040 bytes received via SQL*Net from client 4877 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 73130 rows processed

INDEX FAST FULL SCAN：索引min和max全扫描

--请注意这个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 traceonlyset linesize 1000select max(object_id) from t;执行计划--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FULL SCAN (MIN/MAX)| IDX_OBJECT_ID | 1 | 13 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 431 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

INDEX FULL SCAN (MINMAX)：索引快速全扫描

---请记住这个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 traceonlyset linesize 1000select count(*) from t;执行计划-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 49 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 88780 | 49 (0)| 00:00:01 |-------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 170 consistent gets 0 physical reads 0 redo size 425 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

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 => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; set autotrace traceonlyset linesize 1000select * from t where object_id=8;执行计划-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 94 | 4 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 94 | 4 (0)| 00:00:01 ||* 2 | INDEX SKIP SCAN | IDX_TYPE_ID | 1 | | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=8) filter("OBJECT_ID"=8)统计信息---------------------------------------------------------- 1 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1401 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

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 explainset linesize 1000select object_id from t where object_id=2 and object_type='TABLE';---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 216 | 2 (0)| 00:00:01 ||* 1 | TABLE ACCESS BY INDEX ROWID| T | 9 | 216 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 12 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------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';执行计划--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 216 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| IDX_ID_TYPE | 9 | 216 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------

索引的不足之处：

1.索引的各种开销，访问开销，更新开销，建立开销

2.索引使用失效：逻辑失效，物理失效

索引访问开销-索引访问集中，导致热块的竞争

/* 结论：一般来说，由于用户都是访问最新产生的数据的，所以容易产生索引的热点块竞争， 这也算是索引的开销的弊端了，不过这时可以考虑用方向键索引来将索引的位置转移到不同的地方，只是反向键索引要担心范围查询无法使用，这在案例 的部分会有描述。 */drop table t purge;create table t (id number,deal_date date,area_code number,nbr number,contents varchar2(4000));insert into t(id,deal_date,area_code,nbr,contents) select rownum, to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,700)),'J'), ceil(dbms_random.value(590,599)), ceil(dbms_random.value(18900000001,18999999999)), rpad('*',400,'*') from dual connect by rownum <= 100000;commit;create index idx_t_id on t(id);--由于一般来说，最新的值都是最新产生的，所以访问容易产生热快竞争。因为select * from t where id=100000; select * from t where id=99999; select * from t where id=99998; select * from t where id=99997;---数据是相邻的，很可能在同一个索引块上，所以很容易产生热点索引块竞争。 --如果方向键索引，刚才的语句等于变成如此查询： select * from t where id=000001; select * from t where id=99999; select * from t where id=89999; select * from t where id=79999;--他们都被分到很远的地方，不会在同一个索引块上，有效的避免了索引块竞争。drop index idx_t_id ;create index idx_t_id on t(id) reverse;

索引访问开销-回表性能取决于聚合因子：CLUSTERING_FACTOR

/* 结论：索引查询要尽可能的避免回表，如果不可避免，需要关注聚合因子是否过大。*/---两者性能差异显著,ORGANIZED表的聚合因子比较小，回表的代价较低，如下，产生2900个BUFFERselect /*+ index( colocated colocated_pk ) */ * from colocated where x between 20000 and 40000;SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 20001 |00:00:00.05 | 2900 || 1 | TABLE ACCESS BY INDEX ROWID| COLOCATED | 1 | 20002 | 20001 |00:00:00.05 | 2900 ||* 2 | INDEX RANGE SCAN | COLOCATED_PK | 1 | 20002 | 20001 |00:00:00.03 | 1375 |---------------------------------------------------------------------------------------------------------两者性能差异显著,DISORGANIZED表的聚合因子比较大，回表的代价很高，如下，产生21360个BUFFERselect /*+ index( disorganized disorganized_pk ) */* from disorganized where x between 20000 and 40000;SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 20001 |00:00:00.09 | 21360 || 1 | TABLE ACCESS BY INDEX ROWID| DISORGANIZED | 1 | 20002 | 20001 |00:00:00.09 | 21360 ||* 2 | INDEX RANGE SCAN | DISORGANIZED_PK | 1 | 20002 | 20001 |00:00:00.03 | 1375 |---------------------------------------------------------------------------------------------------------select a.table_name, a.index_name, a.blevel, a.leaf_blocks, b.num_rows, b.blocks, a.clustering_factor, trunc(a.clustering_factor / b.num_rows,2) cluster_rate from user_indexes a, user_tables b where a.table_name = b.table_name and index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' ) and a.clustering_factor is not null order by cluster_rate desc;TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS BLOCKS CLUSTERING_FACTOR CLUSTER_RATE------------------ -------------------------- ----------- ---------- ---------- ----------------- ------------DISORGANIZED DISORGANIZED_PK 1 208 100000 1219 99927 .99COLOCATED COLOCATED_PK 1 208 100000 1252 1190 .01

索引更新开销_分区表更新(无索引比较)：

/* 结论：无索引的情况下，分区表和普通表的插入相比，分区表的开销更大，会插的更慢, 因为数据需要判断落在哪个分区，需要额外的开销。*/--构造分区表，插入数据。drop table range_part_tab purge;create table range_part_tab (id number,deal_date date,area_code number,nbr1 number,nbr2 number,nbr3 number,contents varchar2(4000)) partition by range (deal_date) ( partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')), partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')), partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')), partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')), partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')), partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')), partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')), partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')), partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')), partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')), partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')), partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')), partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')), partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')), partition p_201403 values less than (TO_DATE('2014-04-01', 'YYYY-MM-DD')), partition p_201404 values less than (TO_DATE('2014-05-01', 'YYYY-MM-DD')), partition p_max values less than (maxvalue) ) ;--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有100万条，如下：insert into range_part_tab (id,deal_date,area_code,nbr1,nbr2,nbr3,contents) select rownum, to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(591,599)), ceil(dbms_random.value(18900000001,18999999999)), ceil(dbms_random.value(18900000001,18999999999)), ceil(dbms_random.value(18900000001,18999999999)), rpad('*',400,'*') from dual connect by rownum <= 2000000;commit;--以下是插入2014年部分日期随机数和表示福建地区号含义（591到599）的随机数记录，共有20万条，如下：insert into range_part_tab (id,deal_date,area_code,nbr1,nbr2,nbr3,contents) select rownum, to_date( to_char(sysdate,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'), ceil(dbms_random.value(591,599)), ceil(dbms_random.value(18900000001,18999999999)), ceil(dbms_random.value(18900000001,18999999999)), ceil(dbms_random.value(18900000001,18999999999)), rpad('*',400,'*') from dual connect by rownum <= 400000;commit;/*create index idx_parttab_id on range_part_tab(id) local;create index idx_parttab_nbr1 on range_part_tab(nbr1) local;create index idx_parttab_nbr2 on range_part_tab(nbr2) local;create index idx_parttab_nbr3 on range_part_tab(nbr3) local;create index idx_parttab_area on range_part_tab(area_code) local;*/drop table normal_tab purge;create table normal_tab (id number,deal_date date,area_code number,nbr1 number,nbr2 number,nbr3 number,contents varchar2(4000));insert into normal_tab select * from range_part_tab;commit;/*create index idx_tab_id on normal_tab(id) ;create index idx_tab_nbr1 on normal_tab(nbr1) ;create index idx_tab_nbr2 on normal_tab(nbr2) ;create index idx_tab_nbr3 on normal_tab(nbr3) ;create index idx_tab_area on normal_tab(area_code) ; */select count(*) from normal_tab where deal_date>=TO_DATE('2014-02-01', 'YYYY-MM-DD') and deal_date=TO_DATE('2014-02-01', 'YYYY-MM-DD') and deal_date set timing onSQL> insert into range_part_tab 2 select rownum, 3 to_date( to_char(sysdate+60,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'), 4 ceil(dbms_random.value(591,599)), 5 ceil(dbms_random.value(18900000001,18999999999)), 6 ceil(dbms_random.value(18900000001,18999999999)), 7 ceil(dbms_random.value(18900000001,18999999999)), 8 rpad('*',400,'*') 9 from dual 10 connect by rownum <= 400000;已创建400000行。已用时间: 00: 00: 24.69SQL> insert into normal_tab 2 select rownum, 3 to_date( to_char(sysdate+60,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'), 4 ceil(dbms_random.value(591,599)), 5 ceil(dbms_random.value(18900000001,18999999999)), 6 ceil(dbms_random.value(18900000001,18999999999)), 7 ceil(dbms_random.value(18900000001,18999999999)), 8 rpad('*',400,'*') 9 from dual 10 connect by rownum <= 400000;已创建400000行。已用时间: 00: 00: 14.69

索引更新开销_普通表更新(索引是关键)

/*结论： 1. 在无索引的情况下，表的记录越大，插入的速度只会受到很小的影响，基本不会越慢。 2. 在无索引的情况下，分区表的插入要比普通表更慢，因为插入的数据需要做判断落在哪个分区。有这方面的开销。 3. 在有索引的情况下，表记录越大，索引越多，插入速度越慢。 4. 在有索引的情况下，如果表非常大，索引也很多，在条件允许下，可以将索引先失效再生效，速度可以更快。 5. 在有索引的情况下，分区表如果只是有局部索引，一般来说，分区表的插入速度比普通表更快，因为每个分区的分区索引都比较小，更新的开销自然也小。*/-------------------------------------------------------------------------------------------------------------------------------------------------表大还是小对插入的性能影响并不大drop table t_big purge;drop table t purge;create table t as select * from dba_objects;set autotrace offcreate table t_big as select * from t ;insert into t_big select * from t_big;insert into t_big select * from t_big;insert into t_big select * from t_big;insert into t_big select * from t_big;insert into t_big select * from t_big;insert into t_big select * from t_big;commit;drop table t_small purge;create table t_small as select * from t where rownum<=1000;set timing on insert into t_small select * from t_big;已创建4684096行。已用时间: 00: 00: 28.46commit;insert into t_big select * from t_big;已创建4684096行。已用时间: 00: 00: 28.22commit;--可以看出基本没有任何差别，虽然t_small是小表，t_big是大表。所以插入一般不会随着记录的增加越插越慢。什么时候会越插越慢，就是当表有索引的时候。因为索引需要维护，越大越多维护越困难。-----------------------------------------------------------------------------------------------------------------------------------------------drop table test1 purge;drop table test2 purge;drop table test3 purge;drop table t purge;create table t as select * from dba_objects;create table test1 as select * from t;create table test2 as select * from t;create table test3 as select * from t;create index idx_owner on test1(owner);create index idx_object_name on test1(object_name);create index idx_data_obj_id on test1(data_object_id);create index idx_created on test1(created);create index idx_last_ddl_time on test1(last_ddl_time);create index idx_status on test1(status);create index idx_t2_sta on test2(status);create index idx_t2_objid on test2(object_id);set timing on --语句1(test1表有6个索引）insert into test1 select * from t;commit;--语句2(test2表有2个索引）insert into test2 select * from t;commit;--语句3(test3表有无索引）insert into test3 select * from t;commit;-------------------------------------------------------------------------------------------------------------------------------一次与出账相关的小故事drop table t purge;create table t as select * from dba_objects;insert into t select * from t;insert into t select * from t;commit;--请从这里开始注意累加的时间（从建索引到插入记录完毕）set timing on create index idx_t_owner on t(owner);create index idx_t_obj_name on t(object_name);create index idx_t_data_obj_id on t(data_object_id);create index idx_t_created on t(created);create index idx_t_last_ddl on t(last_ddl_time);--语句1(t表有6个索引）insert into t select * from t;commit; --以下进行试验2drop table t purge;create table t as select * from dba_objects;insert into t select * from t;insert into t select * from t;commit;---也从这里开始这里开始注意累加的时间（从插入记录完毕到建索引完毕）set timing on --语句1(t表有6个索引,此时先不建）insert into t select * from t;create index idx_t_owner on t(owner);create index idx_t_obj_name on t(object_name);create index idx_t_data_obj_id on t(data_object_id);create index idx_t_created on t(created);create index idx_t_last_ddl on t(last_ddl_time);

索引建立开销_建索引过程会产生全表锁：

/* 结论：普通的对表建索引将会导致针对该表的更新操作无法进行，需要等待索引建完。更新操作将会被建索引动作阻塞。 这种建索引的方式不会阻止针对该表的更新操作，与建普通索引相反的是， ONLINE建索引的动作是反过来被更新操作阻塞。*/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;insert into t select * from t;insert into t select * from t;insert into t select * from t;insert into t select * from t;commit;select sid from v$mystat where rownum=1; --12set timing oncreate index idx_object_id on t(object_id);索引已创建。已用时间: 00: 00: 15.00session 2sqlplus ljb/ljbset linesize 1000select sid from v$mystat where rownum=1; --134--以下执行居然被阻塞，要直至建索引结束后，才能执行update t set object_id=99999 where object_id=8;session 3set linesize 1000select * from v$lock where sid in (12,134);SQL> select * from v$lock where sid in (134,12);ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------2EB79320 2EB7934C 12 AE 100 0 4 0 409 02EB79394 2EB793C0 134 AE 100 0 4 0 254 02EB79408 2EB79434 12 TO 65921 1 3 0 402 02EB79574 2EB795A0 12 DL 106831 0 3 0 12 02EB795E8 2EB79614 12 DL 106831 0 3 0 12 00EDD7A9C 0EDD7ACC 134 TM 106831 0 0 3 10 00EDD7A9C 0EDD7ACC 12 TM 106831 0 4 0 12 10EDD7A9C 0EDD7ACC 12 TM 18 0 3 0 12 02C0D2844 2C0D28B0 12 TS 8 25202162 6 0 4 02C1A2A8C 2C1A2ACC 12 TX 393223 31633 6 0 12 0select /*+no_merge(a) no_merge(b) */(select username from v$session where sid=a.sid) blocker,a.sid, 'is blocking',(select username from v$session where sid=b.sid) blockee,b.sidfrom v$lock a,v$lock bwhere a.block=1 and b.request>0and a.id1=b.id1and a.id2=b.id2;BLOCKER SID 'ISBLOCKING BLOCKEE SID------------------------------ ---------- ----------- ------------------------------ ----------LJB 12 is blocking LJB 134

索引建立开销_建索引过程中会全表排序：

/* 结论：建索引的过程会产生排序，排序的开销一般比较大，所以要尽量避免在生产缓慢的时候建索引。*/set linesize 266drop table t purge;create table t as select * from dba_objects;select t1.name, t1.STATISTIC#, t2.VALUE from v$statname t1, v$mystat t2 where t1.STATISTIC# = t2.STATISTIC# and t1.name like '%sort%';NAME STATISTIC# VALUE---------------------------------------------------------------- ---------- ----------sorts (memory) 565 462sorts (disk) 566 0sorts (rows) 567 2174create index idx_object_id on t(object_id);select t1.name, t1.STATISTIC#, t2.VALUE from v$statname t1, v$mystat t2 where t1.STATISTIC# = t2.STATISTIC# and t1.name like '%sort%';NAME STATISTIC# VALUE---------------------------------------------------------------- ---------- ----------sorts (memory) 565 463sorts (disk) 566 0sorts (rows) 567 75292

索引逻辑失效：

索引逻辑失效_尽量要避免列的类型转换

/* 结论：什么类型就放什么值，否则会发生类型转换，导致性能问题！（是存放字符的字段就设varchar2类型,是存放数值的字段就设置number类型,是存放日期的字段就设置date类型） 另外不止是类型转换，如果对列进行了运算，比如substr(列），trunc(列)等等，也会导致用不上索引，具体见案例分析中 */--举例说明：drop table t_col_type purge;create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;commit;create index idx_id on t_col_type(id);set linesize 1000set autotrace traceonlyselect * from t_col_type where id=6;执行计划--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 36 | 9 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T_COL_TYPE | 1 | 36 | 9 (0)| 00:00:01 |-------------------------------------------------------------------------------- 1 - filter(TO_NUMBER("ID")=6)统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 32 consistent gets 0 physical reads 0 redo size 540 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed--实际上只有如下写法才可以用到索引，这个很不应该，是什么类型的取值就设置什么样的字段。 select * from t_col_type where id='6';执行计划------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T_COL_TYPE | 1 | 36 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------ 2 - access("ID"='6')统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

索引物理失效_long列调整会致索引失效：

/* 结论：LONG修改为CLOB，居然会导致其他列的索引失效，这个问题背后产生的原因无需去纠结，有兴趣自行研究，*/ drop table t purge;create table t (object_id number,object_name long);create index idx_object_id on t(object_id);insert into t values (1,'ab');commit;select t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID';STATUS INDEX_NAME-------- ---------------VALID IDX_OBJECT_IDalter table T modify object_name clob;set autotrace offselect t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID';STATUS INDEX_NAME-------- --------------UNUSABLE IDX_OBJECT_IDalter index idx_object_id rebuild;set autotrace offselect t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID';STATUS INDEX_NAME-------- ---------------VALID IDX_OBJECT_ID

索引物理失效_move 操作会致索引失效：

/* 结论：很多人想用ALTER TABLE MOVE的方式来降低高水平，结果经常忽略了这个操作会导致索引失效， 请大家通过下列的试验的回顾，以后多留意这点。 另外alter table t shrink space; 是否能十全十美*/ drop table t purge;create table t as select * from dba_objects where object_id is not null;alter table t modify object_id not null;set autotrace offinsert into t select * from t;insert into t select * from t;commit;create index idx_object_id on t(object_id);select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';INDEX_NAME STATUS------------------------------ ------IDX_OBJECT_ID VALIDset linesize 1000set autotrace onselect count(*) from t; COUNT(*)---------- 292740执行计划-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 185 (2)| 00:00:03 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 398K| 185 (2)| 00:00:03 |-------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 659 consistent gets 0 physical reads 0 redo size 425 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedset autotrace offdelete from t where rownum<=292000;commit;set autotrace on select count(*) from t; COUNT(*)---------- 740执行计划-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 185 (2)| 00:00:03 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 1 | 185 (2)| 00:00:03 |-------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 659 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedalter table t move;select count(*) from t;执行计划-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| T | 740 | 6 (0)| 00:00:01 |-------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL*Net to client 415 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 offselect index_name,status from user_indexes where index_name='IDX_OBJECT_ID';INDEX_NAME STATUS------------------------------ --------IDX_OBJECT_ID UNUSABLEalter index idx_object_id rebuild;set autotrace onselect count(*) from t;执行计划-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 740 | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 424 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ----------------------------------------------------------------------------------------------------------------------------------------------

索引物理失效_分区表致索引失效的操作：

/*1.truncate分区会导致全局索引失效，不会导致局部索引失效。如果truncate 增加update global indexes，全局索引不会失效。2.drop分区会导致全局索引失效，局部索引因为drop分区，所以也不存在该分区的局部索引了。如果drop分区增加update global indexes，全局索引不会失效。3.split分区会导致全局索引失效，也会导致局部索引失效。如果split分区增加update global indexes，全局索引不会失效。4.add 分区不会导致全局索引失效，也不会导致局部索引失效。5.exchange会导致全局索引失效，不会导致局部索引失效。如果exchange分区增加update global indexes，全局索引不会失效。重要结论：1. 所有的全局索引，只要用到update global indexes ,都不会失效，其中add分区甚至不需要增加update global indexes都可以生效。2. 局部索引的操作都不会失效，除了split分区。切记split分区的时候，要将局部索引进行rebuild;*/

索引的取舍控制：

索引取舍控制_避免表交叉重复建立索引：

/*结论：一般来说，联合索引和单列索引如果有交叉，需要谨慎考虑比如联合索引nbr,area_code 和单列的nbr索引就有多余，因为nbr,area_code的索引可以用在单列nbr索引上。*/--组合索引的前缀与单列索引一致drop table t purge;create table t as select * from dba_objects;create index idx_object_id on t(object_id,object_type);set autotrace traceonlyset linesize 1000--以下就能用到索引，因为object_id列是前缀select * from t where object_id=19;执行计划---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 207 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 3 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1392 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--因此这两个索引只需要保留一个就够了。

索引取舍控制_删除系统从未用到的索引：

/* 结论：我们可以通过alter index xxx monitoring usage的命令，对具体的索引进行监控，通过查询v$object_usage视图的USED 字段的取值是YES还是NO，就可以知道该索引是否被使用过，具体如下：*/----观察查询出来的结果，删除不常使用的索引，控制索引的数量。drop table t purge;create table t as select * from dba_objects;create index idx_t_id on t (object_id);alter index idx_t_id monitoring usage;--直接简单查询这个(因为v$object_usage只包括当前用户的索引使用记录，如果需要查出所有用户的索引使用记录，使用下面的sql)set linesize 166col INDEX_NAME for a10col TABLE_NAME for a10col MONITORING for a10col USED for a10col START_MONITORING for a25col END_MONITORING for a25select * from v$object_usage;INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING---------- ---------- ---------- ---------- ------------------------- ---------------IDX_T_ID T YES NO 12/14/2013 07:44:36--接下来继续执行一个用都索引的查询set autotrace traceonlyselect * from t where object_id=10;--然后再观察set autotrace offselect * from v$object_usage;INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING---------- ---------- ---------- ---------- ------------------------- ---------------IDX_T_ID T YES YES 12/14/2013 07:44:36注：---停止对索引的监控，观察v$object_usage状态变化alter index idx_t_id nomonitoring usage;INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING---------- ---------- ---------- ---------- ------------------------- --------------------IDX_T_ID T NO YES 12/14/2013 07:44:36 12/14/2013 07:46:45--如果针对当前用户下所有索引都监控，可以如下select 'alter index '||owner||'.'||index_name||' monitoring usage;'from user_indexes;

索引取舍控制_组合列过多的索引很可疑

/* 结论：这里的例子说明了一般来说，组合索引过多效率也不见的就很高。 除非你有特殊的手法，本例中的手法就比较特殊，*/---col1,col2,col3的索引情况，如果没有为COL2赋予查询条件时，COL3只能起到检验作用(依然是in的优化）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 offalter session set statistics_level=all ;set linesize 1000select * from t where object_type='VIEW' and OWNER='LJB';select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));---------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 24 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 9 | 4 |00:00:00.01 | 24 ||* 2 | INDEX RANGE SCAN | IDX_UNION | 1 | 22 | 4 |00:00:00.01 | 21 |---------------------------------------------------------------------------------------------------select /*+INDEX(T,idx_union)*/ * from t T where object_type='VIEW' and OBJECT_ID IN (20,21,22) AND OWNER='LJB';----------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 13 || 1 | INLIST ITERATOR | | 1 | | 4 |00:00:00.01 | 13 || 2 | TABLE ACCESS BY INDEX ROWID| T | 3 | 1 | 4 |00:00:00.01 | 13 ||* 3 | INDEX RANGE SCAN | IDX_UNION | 3 | 1 | 4 |00:00:00.01 | 10 |----------------------------------------------------------------------------------------------------类似select /*+INDEX(T,idx_union)*/ * from t T where (object_type='VIEW' and OBJECT_ID =20 AND OWNER='LJB') or (object_type='VIEW' and OBJECT_ID =21 AND OWNER='LJB') or (object_type='VIEW' and OBJECT_ID =22 AND OWNER='LJB') ---一般来说，组合索引中，第2列脱离第1列无意义，第3列脱离第2列无意义，第4列脱离第3列无意义。所以如果像这个案例中，假如无法有效的增加object_id条件进去，这个object_type,object_id,owner的三列组合索引，还不如直接建成object_type单列索引

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。