索引優(yōu)化系列十五--組合查詢和in有關(guān)的優(yōu)化
drop table t purge;
成都創(chuàng)新互聯(lián)長(zhǎng)期為成百上千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為全州企業(yè)提供專業(yè)的成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作，全州網(wǎng)站改版等技術(shù)服務(wù)。擁有10年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。
create table t as select * from dba_objects;
update t set object_id=rownum ;
create index idx_id_type on t(object_id,object_type);
UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;
UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;
COMMIT;
set linesize 1000
set pagesize 1
alter session set statistics_level=all ;
select /*+index(t,idx1_object_id)*/ * from t where object_TYPE='TABLE' AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2939 |00:00:00.02 | 1117 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 3411 | 2939 |00:00:00.02 | 1117 |
|* 2 | INDEX RANGE SCAN | IDX_ID_TYPE | 1 | 299 | 2939 |00:00:00.02 | 736 |
--------------------------------------------------------------------------------------------------------
2 - access("OBJECT_ID">=20 AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<=21)
filter("OBJECT_TYPE"='TABLE')
已選擇25行。
select /*+index(t,idx_id_type)*/ * from t t where object_TYPE='TABLE' AND OBJECT_ID IN (20,21);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2939 |00:00:00.01 | 598 |
| 1 | INLIST ITERATOR | | 1 | | 2939 |00:00:00.01 | 598 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 2 | 3411 | 2939 |00:00:00.01 | 598 |
|* 3 | INDEX RANGE SCAN | IDX_ID_TYPE | 2 | 1 | 2939 |00:00:00.01 | 217 |
---------------------------------------------------------------------------------------------------------
3 - access((("OBJECT_ID"=20 OR "OBJECT_ID"=21)) AND "OBJECT_TYPE"='TABLE')
已選擇25行。
---col1,col2,col3的索引情況，如果沒(méi)有為COL2賦予查詢條件時(shí)，COL3只能起到檢驗(yàn)作用(依然是in的優(yōu)化）
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 | Reads |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 24 | 19 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 9 | 4 |00:00:00.01 | 24 | 19 |
|* 2 | INDEX RANGE SCAN | IDX_UNION | 1 | 22 | 4 |00:00:00.01 | 21 | 19 |
------------------------------------------------------------------------------------------------------------
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')
網(wǎng)站欄目：索引優(yōu)化系列十五--組合查詢和in有關(guān)的優(yōu)化
文章分享：http://m.jiaotiyi.com/article/jipcgi.html