/*
 结论：这里的例子说明了一般来说，组合索引过多效率也不见的就很高。除非你有特殊的手法，本例中的手法就比较特殊，请大家细看，还真是可以优化。
*/

---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='TMUSER';
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单列索引