索引优化系列十五--组合查询和in有关的优化

drop table t purge;

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的索引情况，如果没有为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 | 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')