Oracle学习笔记组合索引（十二）

Oracle学习笔记组合索引（十二）

1.适用在单独查询返回很多，组合查询返回很少。

2.组合查询的组合顺序，要全面考虑单列查询情况

3.仅等值无范围查询时，组合索引顺序不影响性能

4.组合索引最佳顺序一般是将列等值查询的列置前。

5.注意组合索引与组合条件中关于IN 的优化

组合索引经要素!/* 1.适用在单独查询返回记录很多，组合查询后忽然返回记录很少的情况: 比如where 学历=硕士以上 返回不少的记录 比如where 职业=收银员 同样返回不少的记录 于是无论哪个条件查询做索引，都不合适。 可是，如果学历为硕士以上，同时职业又是收银员的，返回的就少之又少了。 于是联合索引就可以这么开始建了。*/ /* 2.组合查询的组合顺序，要考虑单独的前缀查询情况（否则单独前缀查询的索引不能生效或者只能用到跳跃索引） 比如你在建id,object_type的联合索引时，要看考虑是单独where id=xxx查询的多，还是单独where object_type查询的多。*/--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 offalter 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.组合索引最佳顺序一般是将列等值查询的列置前。（测试组合索引在条件是不等的情况下的情况，条件经常是不等的，要放在后面，让等值的在前面）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.注意组合索引与组合条件中关于IN 的优化--案例1UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;COMMIT;set linesize 1000set pagesize 1alter 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--依然是关于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 offalter session set statistics_level=all ;set linesize 1000select * from t where object_type='VIEW' and OWNER='LJB';select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));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'));

分区表各类聚合优化：

-- 范围分区示例drop table range_part_tab purge;--注意，此分区为范围分区--例子1create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000)) partition by range (deal_date) ( partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')), partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')), partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')), partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')), partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')), partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')), partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')), partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')), partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')), partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')), partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')), partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')), partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')), partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')), partition p_max values less than (maxvalue) ) ;alter table RANGE_PART_TAB modify nbr not null;--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：insert into range_part_tab (id,deal_date,area_code,nbr,contents) select rownum, to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(591,599)), ceil(dbms_random.value(18900000001,18999999999)), rpad('*',400,'*') from dual connect by rownum <= 100000;commit;--以下是插入2014年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：insert into range_part_tab (id,deal_date,area_code,nbr,contents) select rownum, to_date( to_char(sysdate,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(591,599)), ceil(dbms_random.value(18900000001,18999999999)), rpad('*',400,'*') from dual connect by rownum <= 100000;commit;create index idx_part_id on range_part_tab (id) ;create index idx_part_nbr on range_part_tab (nbr) local;--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; set autotrace on set linesize 1000select max(nbr) max_nbr from range_part_tab partition(p_201305);执行计划------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | || 1 | SORT AGGREGATE | | 1 | 8 | | | | || 2 | PARTITION RANGE SINGLE | | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 || 3 | INDEX FULL SCAN (MIN/MAX)| IDX_PART_NBR | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 |------------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent getsselect max(nbr) max_nbr from range_part_tab where deal_date >= TO_DATE('2013-05-01', 'YYYY-MM-DD') and deal_date < TO_DATE('2013-06-01', 'YYYY-MM-DD');执行计划----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | || 1 | SORT AGGREGATE | | 1 | 17 | | | | || 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 || 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |----------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent getsselect count(*) max_nbr from range_part_tab partition(p_201305);执行计划------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 8 (0)| 00:00:01 | | || 1 | SORT AGGREGATE | | 1 | | | | || 2 | PARTITION RANGE SINGLE| | 8716 | 8 (0)| 00:00:01 | 5 | 5 || 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 8 (0)| 00:00:01 | 5 | 5 |------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 29 consistent gets select count(*) max_nbr from range_part_tab where deal_date >= TO_DATE('2013-05-01', 'YYYY-MM-DD') and deal_date < TO_DATE('2013-06-01', 'YYYY-MM-DD');执行计划----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | || 1 | SORT AGGREGATE | | 1 | 9 | | | | || 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 || 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |----------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets select sum(nbr) max_nbr from range_part_tab partition(p_201305);执行计划--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 8 | 8 (0)| 00:00:01 | | || 1 | SORT AGGREGATE | | 1 | 8 | | | | || 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 || 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |--------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 29 consistent gets select sum(nbr) max_nbr from range_part_tab where deal_date >= TO_DATE('2013-05-01', 'YYYY-MM-DD') and deal_date < TO_DATE('2013-06-01', 'YYYY-MM-DD');执行计划----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | || 1 | SORT AGGREGATE | | 1 | 17 | | | | || 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 || 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |----------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets select distinct(nbr) from range_part_tab partition(p_201305);执行计划--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 8660 | 69280 | 9 (12)| 00:00:01 | | || 1 | HASH UNIQUE | | 8660 | 69280 | 9 (12)| 00:00:01 | | || 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 || 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |--------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 29 consistent gets 0 physical reads 0 redo size 152890 bytes sent via SQL*Net to client 6741 bytes received via SQL*Net from client 577 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8635 rows processed select distinct(nbr) from range_part_tab where deal_date >= TO_DATE('2013-05-01', 'YYYY-MM-DD') and deal_date < TO_DATE('2013-06-01', 'YYYY-MM-DD');执行计划----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 22 | 374 | 171 (1)| 00:00:03 | | || 1 | HASH UNIQUE | | 22 | 374 | 171 (1)| 00:00:03 | | || 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 || 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |----------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets 0 physical reads 0 redo size 152886 bytes sent via SQL*Net to client 6741 bytes received via SQL*Net from client 577 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8635 rows processed ---<=和<,<=扫描了两个分区select count(*) from range_part_tab where deal_date >= TO_DATE('2013-05-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') and deal_date <= TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss'); COUNT(*)---------- 8635执行计划------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 340 (1)| 00:00:05 | | || 1 | SORT AGGREGATE | | 1 | 9 | | | | || 2 | PARTITION RANGE ITERATOR| | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 ||* 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |------------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 1136 consistent gets select count(*) from range_part_tab where deal_date >= TO_DATE('2013-05-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') and deal_date < TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss'); COUNT(*)---------- 8635 执行计划----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | || 1 | SORT AGGREGATE | | 1 | 9 | | | | || 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 || 3 | TABLE ACCESS FULL | RANGE_PART_TAB | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |----------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets

啥时候分区索引性能反而低：

drop table part_tab purge;create table part_tab (id int,col2 int,col3 int) partition by range (id) ( partition p1 values less than (10000), partition p2 values less than (20000), partition p3 values less than (30000), partition p4 values less than (40000), partition p5 values less than (50000), partition p6 values less than (60000), partition p7 values less than (70000), partition p8 values less than (80000), partition p9 values less than (90000), partition p10 values less than (100000), partition p11 values less than (maxvalue) ) ;insert into part_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;commit;create index idx_par_tab_col2 on part_tab(col2) local;create index idx_par_tab_col3 on part_tab(col3) ;drop table norm_tab purge;create table norm_tab (id int,col2 int,col3 int);insert into norm_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;commit;create index idx_nor_tab_col2 on norm_tab(col2) ;create index idx_nor_tab_col3 on norm_tab(col3) ;set autotrace traceonlyset linesize 1000set timing on select * from part_tab where col2=8 ;执行计划-----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 39 | 13 (0)| 00:00:01 | | || 1 | PARTITION RANGE ALL | | 1 | 39 | 13 (0)| 00:00:01 | 1 | 11 || 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PART_TAB | 1 | 39 | 13 (0)| 00:00:01 | 1 | 11 ||* 3 | INDEX RANGE SCAN | IDX_PAR_TAB_COL2 | 1 | | 12 (0)| 00:00:01 | 1 | 11 |-----------------------------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 539 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed select * from norm_tab where col2=8 ;执行计划------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| NORM_TAB | 1 | 39 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_NOR_TAB_COL2 | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 543 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed select * from part_tab where col2=8 and id=2;select * from norm_tab where col2=8 and id=2;--查看索引高度等信息select index_name, blevel, leaf_blocks, num_rows, distinct_keys, clustering_factor from user_ind_statistics where table_name in( 'NORM_TAB'); select index_name, blevel, leaf_blocks, num_rows, distinct_keys, clustering_factor FROM USER_IND_PARTITIONS where index_name like 'IDX_PAR_TAB%'; ---索引的高度会影响性能

同时取最大最小值的案例：

MAX/MIN 的索引优化 drop table t purge; create table t as select * from dba_objects; update t set object_id=rownum; commit; alter table t add constraint pk_object_id primary key (OBJECT_ID); set autotrace on set linesize 1000 select max(object_id) from t; select min(object_id) from t; --等价改写，为数不多的SQL改写复杂了性能更优的情况set linesize 1000set autotrace onselect max(object_id),min(object_id) from t;执行计划--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 46 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN| PK_OBJECT_ID | 74796 | 949K| 46 (0)| 00:00:01 |--------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 160 consistent gets 0 physical reads 0 redo size 502 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed select max, min from (select max(object_id) max from t ) a, (select min(object_id) min from t ) b; 执行计划---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 || 1 | NESTED LOOPS | | 1 | 26 | 4 (0)| 00:00:01 || 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 || 3 | SORT AGGREGATE | | 1 | 13 | | || 4 | INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 1 | 13 | 2 (0)| 00:00:01 || 5 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 || 6 | SORT AGGREGATE | | 1 | 13 | | || 7 | INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 1 | 13 | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 480 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

组合索引考虑单例索引：

组合索引的前缀与单列索引一致drop table t purge;create table t as select * from dba_objects;create index idx_object_id on t(object_id,object_type);set autotrace traceonlyset linesize 1000--以下就能用到索引，因为object_id列是前缀select * from t where object_id=19;执行计划---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 207 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 3 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1392 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed组合索引的前缀与单列索引不一致drop index idx_object_id;create index idx_object_id on t(object_type, object_id);--以下就不能用到索引，因为object_id列是后缀select * from t where object_id=19; 执行计划--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 12 | 2484 | 292 (1)| 00:00:04 ||* 1 | TABLE ACCESS FULL| T | 12 | 2484 | 292 (1)| 00:00:04 |--------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 1049 consistent gets 0 physical reads 0 redo size 1389 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

组合查询和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 1000set pagesize 1alter 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 offalter session set statistics_level=all ;set linesize 1000select * 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')

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。