索引優(yōu)化十二--組合索引的優(yōu)化技巧與總結(jié)

組合索引經(jīng)要素!

/* 1.適用在單獨(dú)查詢返回記錄很多，組合查詢后忽然返回記錄很少的情況:

   比如where 學(xué)歷=碩士以上 返回不少的記錄

   比如where 職業(yè)=收銀員 同樣返回不少的記錄

   于是無(wú)論哪個(gè)條件查詢做索引，都不合適。

   可是，如果學(xué)歷為碩士以上，同時(shí)職業(yè)又是收銀員的，返回的就少之又少了。

   于是聯(lián)合索引就可以這么開始建了。

*/   

/* 2.組合查詢的組合順序，要考慮單獨(dú)的前綴查詢情況（否則單獨(dú)前綴查詢的索引不能生效或者只能用到跳躍索引）

   比如你在建id,object_type的聯(lián)合索引時(shí)，要看考慮是單獨(dú)where id=xxx查詢的多，還是單獨(dú)where object_type查詢的多。

   這里細(xì)節(jié)就暫時(shí)略去了，在案例的部分中還有描述

*/

--3.僅等值無(wú)范圍查詢時(shí)，組合索引順序不影響性能（比如where col1=xxx and col2=xxx，無(wú)論COL1+COL2組合還是COL2+COL1組合）

drop table t purge;

create table t as select * from dba_objects;

insert into t select * from t;

insert into t select * from t;

insert into t select * from t;

update t set object_id=rownum ;

commit;

create index idx_id_type on t(object_id,object_type);

create index idx_type_id on t(object_type,object_id);

set autotrace off

alter session set statistics_level=all ;

set linesize 366

select /*+index(t,idx_id_type)*/ * from  t  where object_id=20  and object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |      1 |        |      1 |00:00:00.01 |       5 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |      1 |     57 |      1 |00:00:00.01 |       5 |

|*  2 |   INDEX RANGE SCAN          | IDX_ID_TYPE |      1 |      9 |      1 |00:00:00.01 |       4 |

-----------------------------------------------------------------------------------------------------

select /*+index(t,idx_type_id)*/ * from  t  where object_id=20  and object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Plan hash value: 3420768628

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |      1 |        |      1 |00:00:00.01 |       5 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |      1 |     57 |      1 |00:00:00.01 |       5 |

|*  2 |   INDEX RANGE SCAN          | IDX_TYPE_ID |      1 |      9 |      1 |00:00:00.01 |       4 |

-----------------------------------------------------------------------------------------------------

--4.組合索引最佳順序一般是將列等值查詢的列置前。（測(cè)試組合索引在條件是不等的情況下的情況，條件經(jīng)常是不等的，要放在后面，讓等值的在前面）

select /*+index(t,idx_id_type)*/ *  from   t where object_id>=20 and object_id<2000 and object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |      1 |        |    469 |00:00:00.01 |      86 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |      1 |     14 |    469 |00:00:00.01 |      86 |

|*  2 |   INDEX RANGE SCAN          | IDX_ID_TYPE |      1 |      1 |    469 |00:00:00.01 |      40 |

-----------------------------------------------------------------------------------------------------

select /*+index(t,idx_type_id)*/ *  from  t  where object_id>=20 and object_id<2000   and object_type='TABLE';

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |      1 |        |    469 |00:00:00.01 |      81 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |      1 |    469 |    469 |00:00:00.01 |      81 |

|*  2 |   INDEX RANGE SCAN          | IDX_TYPE_ID |      1 |    469 |    469 |00:00:00.01 |      35 |

-----------------------------------------------------------------------------------------------------

--5.注意組合索引與組合條件中關(guān)于IN 的優(yōu)化(將會(huì)在案例部分描述，展現(xiàn)結(jié)果就不在這里貼出了）

--案例1

UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;

UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;

COMMIT;

set linesize 1000

set pagesize 1

alter session set statistics_level=all ;

select  /*+index(t,idx1_object_id)*/ * from t  where object_TYPE='TABLE'  AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;

--6.案例2

--依然是關(guān)于IN的優(yōu)化 (col1,col2,col3的索引情況，如果沒有為COL2賦予查詢條件時(shí)，COL3只能起到檢驗(yàn)作用)

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'));

--這里就略去了展現(xiàn)結(jié)果，在案例中有描述。

select /*+INDEX(T,idx_union)*/ * from t T where object_type='VIEW' and OBJECT_ID IN (20,21,22) AND OWNER='LJB';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));