Oracle学习笔记从案例中看索引的开销（十四）

Oracle学习笔记从案例中看索引的开销（十四）

索引访问开销_设置索引并行属性引风波

/* 为了提高建索引的效率，采用了并行的方式，并且设到了索引的属性中去了，引发了性能故障。 一般来说，如果我们要做并行的操作，建议用HINT的方式给查询语句加索引，比如/*+parallel n*/*/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;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;set timing oncreate index idx_object_id on t(object_id) parallel 8;索引已创建。已用时间: 00: 00: 09.85select index_name,degree from user_indexes where table_name='T';INDEX_NAME DEGREE------------------------------ -------IDX_OBJECT_ID 8set linesize 1000set autotrace traceonlyselect count(*) from t;执行计划----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5797 (2)| 00:01:10 | | | || 1 | SORT AGGREGATE | | 1 | | | | | || 2 | PX COORDINATOR | | | | | | | || 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) || 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | || 5 | PX BLOCK ITERATOR | | 8100K| 5797 (2)| 00:01:10 | Q1,00 | PCWC | || 6 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 8100K| 5797 (2)| 00:01:10 | Q1,00 | PCWP | |----------------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 24 recursive calls 0 db block gets 25365 consistent gets 20769 physical reads 0 redo size 426 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 offalter index IDX_OBJECT_ID noparallel;select index_name,degree from user_indexes where table_name='T';INDEX_NAME DEGREE------------------------------ -------IDX_OBJECT_ID 1 SQL> select count(*) from t;执行计划-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5797 (2)| 00:01:10 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 8100K| 5797 (2)| 00:01:10 |-------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 20828 consistent gets 0 physical reads 0 redo size 426 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

索引更新开销_分区表与插入性能的提升

/* 结论：如果表没有索引，插入的速度一般都不会慢，只有在有索引的情况下，才要考虑插入速度的优化。 如果表有大量索引，一般来说，分区表的局部索引由于只需要更新局部分区的索引，所以索引的开销会比较小，所以插入性能比 有着相同的记录数，列及索引的普通表更快。 */--构造分区表，插入数据。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: 51.20SQL> 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: 01: 20.04

索引建立开销_未用online建索引酿大错

/* 结论：普通的对表建索引将会导致针对该表的更新操作无法进行，需要等待索引建完。更新操作将会被建索引动作阻塞。 而ONLINE建索引的方式却是不会阻止针对该表的更新操作，与建普通索引相反的是，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) online;索引已创建。session 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);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 278 02EB79394 2EB793C0 134 AE 100 0 4 0 303 02EB79408 2EB79434 12 DL 106831 0 3 0 25 02EB79574 2EB795A0 12 DL 106831 0 3 0 25 02EB795E8 2EB79614 12 OD 106831 0 4 0 25 02EB7965C 2EB79688 12 TX 131079 31688 0 4 11 00EDD7A9C 0EDD7ACC 134 TM 106831 0 3 0 23 00EDD7A9C 0EDD7ACC 12 TM 106831 0 2 0 25 00EDD7A9C 0EDD7ACC 12 TM 106834 0 4 0 25 02C17C3B8 2C17C3F8 134 TX 131079 31688 6 0 23 12C1A2448 2C1A2488 12 TX 589853 31754 6 0 25 0已选择11行。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.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 134 is blocking LJB 12

索引去哪儿_like与 %之间

/* 结论：索引遇到like '%LJB' 或者是'%LJB%'的查询，是用不到索引的（除非是全索引访问，这是索引能回答问题的一个例外）。 不过like 'LJB%'是可以用到索引的。原理其实很简单，从索引有序性就可以推理到原因了。 让'%LJB'用的索引的另类方法，这里涉及到了函数索引的知识， 另外即便是'%LJB%'，也不见的就一定用不到索引，可以考虑全文检索， */思路：1. 全文检索2. 寻找函数构造的机会，并建函数索引drop table t purge;create table t as select * from dba_objects where object_id is not null;set autotrace offupdate t set object_id=rownum;update t set object_name='AAALJB' where object_id=8;update t set object_name='LJBAAA' where object_id=10;commit;create index idx_object_name on t(object_name);SET AUTOTRACE ONSET LINESIZE 1000select object_name,object_id from t where object_name like 'LJB%';OBJECT_NAME OBJECT_ID------------------------------ ---LJBAAA 10LJB_TMP_SESSION 72521LJB_TMP_SESSION 72910LJB_TMP_TRANSACTION 72522LJB_TMP_TRANSACTION 72911已选择5行。执行计划-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 395 | 6 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 5 | 395 | 6 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_OBJECT_NAME | 5 | | 3 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 602 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) 5 rows processed SQL> select object_name,object_id from t where object_name like '%LJB%';OBJECT_NAME OBJECT_ID---------------------------------- ---AAALJB 8LJBAAA 10LJB_TMP_SESSION 72521LJB_TMP_TRANSACTION 72522LJB_TMP_SESSION 72910LJB_TMP_TRANSACTION 72911已选择6行。执行计划--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 12 | 948 | 292 (1)| 00:00:04 ||* 1 | TABLE ACCESS FULL| T | 12 | 948 | 292 (1)| 00:00:04 |--------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 1049 consistent gets 0 physical reads 0 redo size 653 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) 6 rows processed select object_name,object_id from t where object_name like '%LJB'; OBJECT_NAME OBJECT_ID---------------------------- --AAALJB 8 执行计划--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 12 | 948 | 292 (1)| 00:00:04 ||* 1 | TABLE ACCESS FULL| T | 12 | 948 | 292 (1)| 00:00:04 |--------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 1049 consistent gets 0 physical reads 0 redo size 496 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 reverse('%LJB') from dual;REVER-----BJL% create index idx_reverse_objname on t(reverse(object_name));set autotrace on select object_name,object_id from t where reverse(object_name) like reverse('%LJB'); OBJECT_NAME OBJECT_ID---------------------------- --AAALJB 8执行计划---------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3596 | 509K| 290 (0)| 00:00:04 || 1 | TABLE ACCESS BY INDEX ROWID| T | 3596 | 509K| 290 (0)| 00:00:04 ||* 2 | INDEX RANGE SCAN | IDX_REVERSE_OBJNAME | 647 | | 6 (0)| 00:00:01 |---------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 496 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

move 致索引失效:

/* 结论： 这次案例，是涉及有主外键的两表关联查询的性能，索引失效导致NL连接性能下降。*/drop table t_p cascade constraints purge;drop table t_c cascade constraints purge;CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;COMMIT;CREATE INDEX IND_T_C_FID ON T_C (FID);SELECT TABLE_NAME,INDEX_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND_T_C_FID';TABLE_NAME INDEX_NAME STATUS------------------------------ ------------------------------ -------T_C IND_T_C_FID VALID--不小心失效了，比如操作了ALTER TABLE T_C MOVE;SELECT TABLE_NAME,INDEX_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND_T_C_FID';TABLE_NAME INDEX_NAME STATUS------------------------------ ------------------------------ --------T_C IND_T_C_FID UNUSABLE--结果查询性能是这样的：SET LINESIZE 1000SET AUTOTRACE TRACEONLYSELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;执行计划------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 25 | 1500 | 111 (1)| 00:00:02 || 1 | NESTED LOOPS | | 25 | 1500 | 111 (1)| 00:00:02 || 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | T_C | 25 | 750 | 111 (1)| 00:00:02 |------------------------------------------------------------------------------------------ 3 - access("A"."ID"=880) 4 - filter("B"."FID"=880)统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 394 consistent gets 0 physical reads 0 redo size 3602 bytes sent via SQL*Net to client 459 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 72 rows processed ---将失效索引重建后ALTER INDEX IND_T_C_FID REBUILD;查询性能是这样的：SELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;执行计划--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 72 | 4320 | 87 (0)| 00:00:02 || 1 | NESTED LOOPS | | 72 | 4320 | 87 (0)| 00:00:02 || 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID| T_C | 72 | 2160 | 87 (0)| 00:00:02 ||* 5 | INDEX RANGE SCAN | IND_T_C_FID | 72 | | 1 (0)| 00:00:01 |-------------------------------------------------------------------------------------------- 3 - access("A"."ID"=880) 5 - access("B"."FID"=880)统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 81 consistent gets 0 physical reads 0 redo size 3602 bytes sent via SQL*Net to client 459 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 72 rows processed

move 致索引失效引锁等待:

/* 结论：由于move 外键所在的表，导致外键的表的索引失效，导致主外键的表更新起来举步维艰，频频被锁，如下： */--外键索引性能研究之准备drop table t_p cascade constraints purge;drop table t_c cascade constraints purge;CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;COMMIT;create index idx_IND_T_C_FID on T_C(FID);--以下操作导致外键索引失效ALTER TABLE T_C MOVE;外键索引删除后，立即有锁相关问题--首先开启会话1select sid from v$mystat where rownum=1;DELETE T_C WHERE ID = 2;--接下来开启会话2，也就是开启一个新的连接select sid from v$mystat where rownum=1; --然后执行如下进行观察DELETE T_P WHERE ID = 2000;--居然发现卡住半天不动了！ --假如外键有索引，就不会产生死锁情况，--首先开启会话1ALTER INDEX idx_IND_T_C_FID REBUIDL;select sid from v$mystat where rownum=1;DELETE T_C WHERE FID = 2;--接下来开启会话2，也就是开启一个新的连接select sid from v$mystat where rownum=1;DELETE T_P WHERE ID = 2000;

shrink索引不失效也被弃用

/* 结论：alter table t shrink的方式降低表的高水平位，也不会导致索引失效，却无法消除索引的大量空块。 最终导致虽然索引不失效，查询依然不用索引，具体见案例如下： */--这里用alter table t shrink的方式降低高水平位，结果避免了索引的失效，不过索引不失效了，是否索引就一定会被用到吗，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); set linesize 1000set autotrace onselect count(*) from t;set autotrace offdelete from t where rownum<=292000;commit;set autotrace on select count(*) from t;alter table t enable row movement;alter table t shrink space;select count(*) from t;执行计划----------------------------------------------------------Plan hash value: 2966233522-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| T | 740 | 5 (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 VALIDset autotrace on --原来发现走了，还更慢。select /*+index(t)*/ count(*) from t;执行计划--------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 675 (1)| 00:00:09 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FULL SCAN| IDX_OBJECT_ID | 740 | 675 (1)| 00:00:09 |--------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 649 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 shrink 不会导致索引失效，但是索引块的高水平无法释放。还是会产生大量的逻辑读。

范围查询为何就用不到索引：

/* 结论：方向键索引可以消除索引热块访问竞争，是一个很不错的技术，该技术只能用在等值查询，而不能用在范围查询。 以下是生产中的一个案例，有人讲索引建成了反向键索引，却忘记了该系统有大量范围查询，还在纠结为何范围查询用不到索引。 */--这里说的是反向键索引的故事drop table t purge;create table t (id number,deal_date date,area_code number,nbr number,contents varchar2(4000));set autotrace offinsert 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) reverse;set linesize 1000set autotrace offselect index_name,index_type from user_indexes where table_name='T';INDEX_NAME INDEX_TYPE------------------------------ -----------IDX_T_ID NORMAL/REVset autotrace traceonly --以下语句缘何用不到索引。select * from t where id=28;执行计划----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 69 | 138K| 401 (0)| 00:00:05 || 1 | TABLE ACCESS BY INDEX ROWID| T | 69 | 138K| 401 (0)| 00:00:05 ||* 2 | INDEX RANGE SCAN | IDX_T_ID | 486 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------不过奇怪的是，缘何下列语句却用不到索引，索引去哪儿？select * from t where id>=28 and id<=50;执行计划----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 304 | 608K| 1709 (1)| 00:00:21 ||* 1 | TABLE ACCESS FULL| T | 304 | 608K| 1709 (1)| 00:00:21 |--------------------------------------------------------------------------统计信息------------------------------------------------------ 0 recursive calls 0 db block gets 6303 consistent gets 0 physical reads 0 redo size 2263 bytes sent via SQL*Net to client 426 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 23 rows processed--原因在于这个索引不是普通索引，是为了避免热块竞争而建立的反向键索引，根本部支持范围查询，只支持等值查询。

回收站还原表后的苦难经历

/* 结论：关于误drop表后从回收站中取回表，需要记住一些细节，比如，这时其实该表的索引已经丢了。 一定要在恢复该表的同时记住将索引及约束等属性补完善。*/drop table t purge;create table t as select * from dba_objects;create index idx_object_id on t(object_id);set autotrace offselect index_name,status from user_indexes where table_name='T';INDEX_NAME STATUS------------------------------ --------IDX_OBJECT_ID VALIDdrop table t;flashback table t to before drop;--取回来了，其实索引丢了select status from user_indexes where index_name='IDX_OBJECT_ID';未选定行--后来系统运行的很慢，很慢.....

回收站恢复与约束的那点事：

/* 结论：关于误drop表，然后从回收站中取回表后，除了索引会丢，约束一样也会丢失。 */drop table t_p cascade constraints purge;drop table t_c cascade constraints purge;CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);set autotrace offINSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;COMMIT;CREATE INDEX IND_T_C_FID ON T_C (FID);--以下删除数据会失败delete from t_p where id=8;第 1 行出现错误:ORA-02292: 违反完整约束条件 (LJB.FK_T_C) - 已找到子记录---换一个顺序可以（先删除t_c的记录，再删除t_p)delete from t_c where fid=8; delete from t_p where id=8; commit;--当然，也可以采用约束下失效再生效的方法--以下删除操作会失败drop table t_p;ORA-02449: unique/primary keys in table referenced by foreign keys---不过强制可以删除成功（drop table t_p cascade constraint;）--换一个顺序（先删t_c,再删t_p就可以了)SQL> drop table t_c;表已删除。SQL> drop table t_p;表已删除。--当然，也可以采用约束下失效再生效的方法----注意，现实中的一个案例，外键所在的表被drop了，从回收站取回来的时候，记得，不仅是索引没了，约束也丢了。DROP TABLE T_C ;FLASHBACK TABLE T_C TO BEFORE DROP;---发现不止是外键的索引丢失了，约束也丢失了。SELECT TABLE_NAME, CONSTRAINT_NAME, STATUS, CONSTRAINT_TYPE, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T_C'; 未选定行prompt

失效对象select t.object_type, t.object_name, 'alter ' ||decode(object_type, 'PACKAGE BODY', 'PACKAGE', 'TYPE BODY','TYPE',object_type) || ' ' ||owner || '.' || object_name || ' ' ||decode(object_type, 'PACKAGE BODY', 'compile body', 'compile') || ';' from user_objects t where STATUS='INVALID' order by 1, 2;

最典型的时间查询通病：

/* 结论：避免对列进行运算，否则将用不到索引，除非使用函数索引。 where trunc(created)>=TO_DATE('2013-12-14', 'YYYY-MM-DD') and trunc(created)<=TO_DATE('2013-12-15', 'YYYY-MM-DD')*/drop table t purge;create table t as select * from dba_objects;create index idx_object_id on t(created);set autotrace traceonlyset linesize 1000--以下写法大量的出现在开发人员的代码中，是一个非常常见的通病,由于对列进行了运算，所以用不到索引，如下：select * from t where trunc(created)>=TO_DATE('2013-12-14', 'YYYY-MM-DD')and trunc(created)<=TO_DATE('2013-12-15', 'YYYY-MM-DD');执行计划--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 12 | 2484 | 296 (2)| 00:00:04 ||* 1 | TABLE ACCESS FULL| T | 12 | 2484 | 296 (2)| 00:00:04 |-------------------------------------------------------------------------- 1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))>=TO_DATE(' 2013-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("CREATED"))<=TO_DATE(' 2013-12-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 1049 consistent gets 0 physical reads 0 redo size 1390 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 where created>=TO_DATE('2013-12-14', 'YYYY-MM-DD')and created=TO_DATE(' 2013-12-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CREATED"

请注意这写法是案例：

/* 结论：避免对列进行运算，否则将用不到索引，除非使用函数索引。 之间已经看过了一个时间的例子 where trunc(created)>=TO_DATE('2013-12-14', 'YYYY-MM-DD') and trunc(created)<=TO_DATE('2013-12-15', 'YYYY-MM-DD') 导致用不到索引， */drop table t purge;create table t as select * from dba_objects;create index idx_object_id on t(object_id);VARIABLE id NUMBER;EXECUTE :id := 8;set linesize 1000set autotrace traceonlyselect * from t where object_id/2=:id;执行计划--------------------------------------------------------------------------------| 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 where object_id=:id*2;执行计划---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 685 | 138K| 6 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 685 | 138K| 6 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 274 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1407 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

组合升降序排序索引有玄机：

/* 结论：索引能够消除排序，但是如果排序是部分升序部分降序，就必须建对应部分升降序的索引，否则无法用这个来消除排序。 比如order by col1 desc col2 asc，我们可以建(col1 desc,col2 asc)的索引。 值得一提的是，如果你的语句变成 order by col1 asc col2 desc， 之前的(col1 desc,col2 asc)的索引依然可以起到避免排序的作用DESCING。*/drop table t purge;create table t as select * from dba_objects where object_id is not null ;set autotrace offinsert into t select * from t;insert into t select * from t;commit;create index idx_t on t (owner,object_id);alter table t modify owner not null;alter table t modify object_id not null;set linesize 1000set autotrace traceonly--听说order by 列有索引可以消除排序，测试发现，Oracle选择不用索引，排序依然存在，索引去哪儿？select * from t a order by owner desc ,object_type asc;执行计划-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 398K| 78M| | 19133 (1)| 00:03:50 || 1 | SORT ORDER BY | | 398K| 78M| 94M| 19133 (1)| 00:03:50 || 2 | TABLE ACCESS FULL| T | 398K| 78M| | 1177 (1)| 00:00:15 |-----------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4209 consistent gets 0 physical reads 0 redo size 13981752 bytes sent via SQL*Net to client 215080 bytes received via SQL*Net from client 19517 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 292740 rows processed --换个思路，建如下索引 drop index idx_t;create index idx_t on t(owner desc,object_type asc);--哦，索引再这，效率果然提高了,COST比未用索引导致排序的代价19133低，是14687。select * from t a order by owner desc ,object_type asc;执行计划-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 398K| 78M| 14687 (1)| 00:02:57 || 1 | TABLE ACCESS BY INDEX ROWID| T | 398K| 78M| 14687 (1)| 00:02:57 || 2 | INDEX FULL SCAN | IDX_T | 398K| | 1085 (1)| 00:00:14 |-------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 52710 consistent gets 0 physical reads 0 redo size 13821025 bytes sent via SQL*Net to client 215080 bytes received via SQL*Net from client 19517 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 292740 rows processed

早知道有虚拟索引就好了

/* 结论：在数据库优化中，索引的重要性不言而喻。但是，在性能调整过程中，一个索引是否能被查询用到，在索引创建之前是无法确定的，而创建索引是一个代价比较高的操作，尤其是数据量较大的时候。这时你就应该考虑使用虚拟索引来做个试验*/drop table t purge;create table t as select * from dba_objects;--创建虚拟索引，首先要将_use_nosegment_indexes的隐含参数设置为truealter session set "_use_nosegment_indexes"=true;--虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个nosegment关键字create index ix_t_id on t(object_id) nosegment;explain plan for select * from t where object_id=1;set linesize 1000select * from table(dbms_xplan.display());set autotrace traceonlyselect * from t where object_id=1;set autotrace off--以下看的是真实执行计划，显然是用不到索引。alter session set statistics_level=all;select * from t where object_id=1;select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));--从数据字段中是无法找到这个索引的。select index_name,status from user_indexes where table_name='T';注：虚拟索引的几个特点1. 无法执行alter index2. 不能创建和虚拟索引同名的实际索引3. 可以创建和虚拟索引包含相同列但不同名的实际索引4. 在10g使用回收站特性的时候，虚拟索引必须显式drop，或者在drop table后purge table后，才能创建同名的索引5. 虚拟索引分析并且有效，但是数据字典里查不到结果，估计是oracle内部临时保存了分析结果

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