组合索引经要素!
--3.仅等值无范围查询时，组合索引顺序不影响性能（比如where col1=xxx and col2=xxx，无论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 * 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 * 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.组合索引最佳顺序一般是将列等值查询的列置前。（测试组合索引在条件是不等的情况下的情况，条件经常是不等的，要放在后面，让等值的在前面）
select * 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 * 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.注意组合索引与组合条件中关于IN 的优化(将会在案例部分描述，展现结果就不在这里贴出了）
--案例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 * from t where object_TYPE='TABLE' AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;
--6.案例2
--依然是关于IN的优化 (col1,col2,col3的索引情况，如果没有为COL2赋予查询条件时，COL3只能起到检验作用)
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'));
--这里就略去了展现结果，在案例中有描述。
select * 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'));
