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 traceonly
set linesize 1000
exec 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 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 |
---------------------------------------------------------------------------------------------|
统计信息
---------------------------------------------------
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 off
select rowid from t where object_id=8;
ROWID
------------------
AAAZxiAAGAAAB07AAH
set autotrace traceonly
set linesize 1000
select * 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 traceonly
set linesize 1000
select * 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 traceonly
set linesize 1000
select 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 traceonly
set linesize 1000
select 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 traceonly
set linesize 1000
select * 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 explain set linesize 1000 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 | 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个BUFFER
select /*+ 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个BUFFER
select /*+ 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 .99
COLOCATED 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-03-01', 'YYYY-MM-DD');
select count(*) from range_part_tab where deal_date>=TO_DATE('2014-02-01', 'YYYY-MM-DD') and deal_date<TO_DATE('2014-03-01', 'YYYY-MM-DD');
set timing on
insert into range_part_tab
select rownum,
to_date( to_char(sysdate+60,'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;
insert into normal_tab
select rownum,
to_date( to_char(sysdate+60,'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;
SQL> set timing on
SQL> 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.69
SQL> 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 off create 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.46 commit; insert into t_big select * from t_big; 已创建4684096行。 已用时间: 00: 00: 28.22 commit; --可以看出基本没有任何差别，虽然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; --以下进行试验2 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 --语句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;
--12
set timing on
create index idx_object_id on t(object_id);
索引已创建。
已用时间: 00: 00: 15.00
session 2
sqlplus ljb/ljb
set linesize 1000
select sid from v$mystat where rownum=1;
--134
--以下执行居然被阻塞，要直至建索引结束后，才能执行
update t set object_id=99999 where object_id=8;
session 3
set linesize 1000
select * 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 0
2EB79394 2EB793C0 134 AE 100 0 4 0 254 0
2EB79408 2EB79434 12 TO 65921 1 3 0 402 0
2EB79574 2EB795A0 12 DL 106831 0 3 0 12 0
2EB795E8 2EB79614 12 DL 106831 0 3 0 12 0
0EDD7A9C 0EDD7ACC 134 TM 106831 0 0 3 10 0
0EDD7A9C 0EDD7ACC 12 TM 106831 0 4 0 12 1
0EDD7A9C 0EDD7ACC 12 TM 18 0 3 0 12 0
2C0D2844 2C0D28B0 12 TS 8 25202162 6 0 4 0
2C1A2A8C 2C1A2ACC 12 TX 393223 31633 6 0 12 0
select /*+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.sid
from v$lock a,v$lock b
where a.block=1 and b.request>0
and a.id1=b.id1
and a.id2=b.id2;
BLOCKER SID 'ISBLOCKING BLOCKEE SID
------------------------------ ---------- ----------- ------------------------------ ----------
LJB 12 is blocking LJB 134
索引建立开销_建索引过程中会全表排序：
/* 结论：建索引的过程会产生排序，排序的开销一般比较大，所以要尽量避免在生产缓慢的时候建索引。 */ set linesize 266 drop 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 462 sorts (disk) 566 0 sorts (rows) 567 2174 create 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 463 sorts (disk) 566 0 sorts (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 1000
set autotrace traceonly
select * 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_ID alter table T modify object_name clob; set autotrace off select t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID'; STATUS INDEX_NAME -------- -------------- UNUSABLE IDX_OBJECT_ID alter index idx_object_id rebuild; set autotrace off select 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 off
insert 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 VALID
set linesize 1000
set autotrace on
select 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 processed
set autotrace off
delete 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 processed
alter 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 off
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME STATUS
------------------------------ --------
IDX_OBJECT_ID UNUSABLE
alter index idx_object_id rebuild;
set autotrace on
select 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 traceonly
set 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 166 col INDEX_NAME for a10 col TABLE_NAME for a10 col MONITORING for a10 col USED for a10 col START_MONITORING for a25 col END_MONITORING for a25 select * 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 traceonly select * from t where object_id=10; --然后再观察 set autotrace off select * 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 off
alter session set statistics_level=all ;
set linesize 1000
select * 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单列索引
