组合索引和单列索引效率对比

SQL> create table test(object_id NUMBER,object_name varchar2(10));Table created.SQL> begin  2  for i in 1 .. 1000000  3  loop  4  insert into test values (1,'TEST');  5  end loop;   6  end;  7  /PL/SQL procedure successfully completed.SQL> begin  2  for i in  1 .. 300  3  loop  4  insert into test values (2,'TEST2');  5  end loop;  6  end;  7  /PL/SQL procedure successfully completed.SQL> begin  2  for i in  1 .. 49700  3  loop  4  insert into test values (2,i||'TEST2');  5  end loop;  6  end;  7  /SQL> select count(*), object_id from test group by object_id;  COUNT(*)  OBJECT_ID---------- ----------   1000000    1     50000    2SQL>  select count(*) from test where object_name='TEST2' and object_id=2;  COUNT(*)----------       300SQL> select count(*) from test where object_name<>'TEST2' and object_id=2;  COUNT(*)----------     49700第一种情况：TABLE ACCESS BY INDEX ROWID 前面有 *,并且INDEX RANGE SCAN返回5w条,5W个rowid,过滤object_name之后只剩下 300条 那么就建组合索引在object_id列创建索引SQL> create index test_idx1 on test(OBJECT_ID);Index created.BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TEST',                                tabname          => 'TEST',                                estimate_percent => 100,                                method_opt       => 'for all columns size skewonly',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;SQL> select * from test where object_id=2 and object_name='TEST2';300 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2624864549-----------------------------------------------------------------------------------------| Id  | Operation    | Name| Rows| Bytes | Cost (%CPU)| Time|-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    ||     1 |     8 |   198   (1)| 00:00:03 ||*  1 |  TABLE ACCESS BY INDEX ROWID| TEST|     1 |     8 |   198   (1)| 00:00:03 ||*  2 |   INDEX RANGE SCAN    | TEST_IDX1 | 50000 ||   100   (0)| 00:00:02 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_NAME"='TEST2')   2 - access("OBJECT_ID"=2)Statistics----------------------------------------------------------  1  recursive calls  0  db block gets270  consistent gets  0  physical reads  0  redo size       4441  bytes sent via SQL*Net to client628  bytes received via SQL*Net from client 21  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)300  rows processedSQL> select count(*) from test;  COUNT(*)----------   1050000SQL>  select count(*) from test where object_id=2;  COUNT(*)----------     50000SQL> select count(*) from test where object_id=2 and object_name='TEST2';  COUNT(*)----------       300TEST表总共1050000行,其中INDEX RANGE SCAN后返回50000行,过滤object_name之后只剩下 300条 那么就建组合索引建立组合索引；SQL> select * from test where object_id=2 and object_name='TEST2';300 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 415678261------------------------------------------------------------------------------| Id  | Operation | Name      | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT |     |   1 |   8 |   3   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| TEST_IDX2 |   1 |   8 |   3   (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("OBJECT_ID"=2 AND "OBJECT_NAME"='TEST2')Statistics----------------------------------------------------------  1  recursive calls  0  db block gets 24  consistent gets  0  physical reads  0  redo size       4441  bytes sent via SQL*Net to client628  bytes received via SQL*Net from client 21  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)300  rows processed如果组合起来的话不需要过滤,不需要回表,直接返回300行第2种情况:INDEX RANGE SCAN 返回5W 回表过滤后返回数据还很多,不需要创建组合索引的情况。 begin    for i in 1 .. 1000000    loop    insert into test values (1,'TEST');    end loop;     end; begin    for i in  1 .. 49000    loop    insert into test values (2,'TEST2');   end loop;    end;    begin    for i in  1 .. 100    loop    insert into test values (2,'TEST3');   end loop;    end;   SQL>  select count(*),object_id,object_name from test   where object_id=2    group by object_id,object_name  2    3  ;  COUNT(*)  OBJECT_ID OBJECT_NAM---------- ---------- ----------     49000    2 TEST2       100    2 TEST3SQL> select * from test where object_id=2 and object_name='TEST2';49000 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2624864549-----------------------------------------------------------------------------------------| Id  | Operation    | Name| Rows| Bytes | Cost (%CPU)| Time|-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    ||  2293 | 18344 |   188   (1)| 00:00:03 ||*  1 |  TABLE ACCESS BY INDEX ROWID| TEST|  2293 | 18344 |   188   (1)| 00:00:03 ||*  2 |   INDEX RANGE SCAN    | TEST_IDX1 | 49100 ||    98   (0)| 00:00:02 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_NAME"='TEST2')   2 - access("OBJECT_ID"=2)Statistics----------------------------------------------------------  1  recursive calls  0  db block gets       6715  consistent gets 98  physical reads  0  redo size     660310  bytes sent via SQL*Net to client      36345  bytes received via SQL*Net from client       3268  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)      49000  rows processedINDEX RANGE SCAN 返回49100行,过滤"OBJECT_NAME"='TEST2'后返回49000行创建组合索引:SQL> create index test_Idx2 on test(object_id,object_name);Index created.SQL> select * from test where object_id=2 and object_name='TEST2';49000 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 415678261------------------------------------------------------------------------------| Id  | Operation | Name      | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT |     |2293 | 18344 |   9   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| TEST_IDX2 |2293 | 18344 |   9   (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("OBJECT_ID"=2 AND "OBJECT_NAME"='TEST2')Statistics----------------------------------------------------------  1  recursive calls  0  db block gets       3398  consistent gets139  physical reads  0  redo size     660310  bytes sent via SQL*Net to client      36345  bytes received via SQL*Net from client       3268  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)      49000  rows processed可以看到就算INDEX RANGE SCAN返回49100行,通过过滤"OBJECT_NAME"='TEST2',虽然只过滤了100行,但还是走组合索引效率高。-------------------------------------------------------------------------------------------------------------------那什么情况会出现组合索引比单列索引效率低呢？select  XLBHZ XLBHZ,ZDBHZ ZDBHZ, jyrqz,               decode(JCZBZ, '0', sum(JYBSZ), 0) TOKEN_JKLZZ,               decode(JCZBZ, '1', sum(JYBSZ), 0) TOKEN_CKLZZ,               0 CASH_JKLZZ,               0 CASH_CKLZZ,               0 TFT_JKLZZ,               0 TFT_CKLZZ          from brt_hzjcztok          where 1=1 AND XLBHZ IN ('0001','0002') AND  JYRQZ>='20130727' AND JYRQZ<='20130728'         group by jyrqz, JCZBZ,XLBHZ,ZDBHZExecution Plan----------------------------------------------------------Plan hash value: 1433290359---------------------------------------------------------------------------------------------------| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                    |  3757 | 93925 |  2604   (1)| 00:00:32 ||   1 |  SORT GROUP BY NOSORT        |                    |  3757 | 93925 |  2604   (1)| 00:00:32 ||   2 |   TABLE ACCESS BY INDEX ROWID| BRT_HZJCZTOK       |  3757 | 93925 |  2604   (1)| 00:00:32 ||*  3 |    INDEX RANGE SCAN          | IDX_HZJCZTOK_UNION |  3757 |       |    20   (0)| 00:00:01 |---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("JYRQZ">='20130727' AND "JYRQZ"<='20130728')       filter("XLBHZ"='0001' OR "XLBHZ"='0002')原先索引:IDX_HZJCZTOK_UNION(JYRQZ, JCZBZ, XLBHZ, ZDBHZ)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets       2670  consistent gets          0  physical reads          0  redo size      10149  bytes sent via SQL*Net to client        646  bytes received via SQL*Net from client         16  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        224  rows processed        Execution Plan----------------------------------------------------------Plan hash value: 2641846428-----------------------------------------------------------------------------------| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |              |  3757 | 93925 |   203   (5)| 00:00:03 ||   1 |  HASH GROUP BY     |              |  3757 | 93925 |   203   (5)| 00:00:03 ||*  2 |   TABLE ACCESS FULL| BRT_HZJCZTOK |  3757 | 93925 |   201   (4)| 00:00:03 |-----------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("JYRQZ">='20130727' AND "JYRQZ"<='20130728' AND              ("XLBHZ"='0001' OR "XLBHZ"='0002'))Statistics----------------------------------------------------------          1  recursive calls          0  db block gets        947  consistent gets          0  physical reads          0  redo size      10333  bytes sent via SQL*Net to client        646  bytes received via SQL*Net from client         16  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        224  rows processed        同个sql，一个强制索引，一个没有强制走全表，你觉得这2个执行计划哪个好？--select count(*) from brt_hzjcztok where JYRQZ>='20130727' AND JYRQZ<='20130728'3885全部数据呢---select count(*) from brt_hzjcztok 128023SQL> select XLBHZ,count(*) from brt_hzjcztok group by XLBHZ order by count(*) desc;XLBH   COUNT(*)---- ----------0002      641310001      638490100         43create index brt_hzjcztok_idx1 on brt_hzjcztok(JYRQZ,XLBHZ);或者直接创建create index brt_hzjcztok_idx1 on brt_hzjcztok(JYRQZ);原来网友自己创建的索引:IDX_HZJCZTOK_UNION(JYRQZ, JCZBZ, XLBHZ, ZDBHZ)-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------create index brt_hzjcztok_idx1 on brt_hzjcztok(JYRQZ,XLBHZ);                Execution Plan----------------------------------------------------------Plan hash value: 497111649--------------------------------------------------------------------------------------------------| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                   |  3757 | 93925 |    85   (3)| 00:00:02 ||   1 |  HASH GROUP BY               |                   |  3757 | 93925 |    85   (3)| 00:00:02 ||   2 |   TABLE ACCESS BY INDEX ROWID| BRT_HZJCZTOK      |  3757 | 93925 |    83   (0)| 00:00:02 ||*  3 |    INDEX RANGE SCAN          | BRT_HZJCZTOK_IDX1 |  3757 |       |    16   (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("JYRQZ">='20130727' AND "JYRQZ"<='20130728')       filter("XLBHZ"='0001' OR "XLBHZ"='0002')Statistics----------------------------------------------------------          0  recursive calls          0  db block gets         79  consistent gets          0  physical reads          0  redo size      10363  bytes sent via SQL*Net to client        646  bytes received via SQL*Net from client         16  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        224  rows processedSQL> /DROP INDEX brt_hzjcztok_idx1create index brt_hzjcztok_idx1 on brt_hzjcztok(JYRQZ);224 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 497111649--------------------------------------------------------------------------------------------------| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                   |  3757 | 93925 |    48   (5)| 00:00:01 ||   1 |  HASH GROUP BY               |                   |  3757 | 93925 |    48   (5)| 00:00:01 ||*  2 |   TABLE ACCESS BY INDEX ROWID| BRT_HZJCZTOK      |  3757 | 93925 |    46   (0)| 00:00:01 ||*  3 |    INDEX RANGE SCAN          | BRT_HZJCZTOK_IDX1 |  3757 |       |    12   (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("XLBHZ"='0001' OR "XLBHZ"='0002')   3 - access("JYRQZ">='20130727' AND "JYRQZ"<='20130728')Statistics----------------------------------------------------------          1  recursive calls          0  db block gets         44  consistent gets          0  physical reads          0  redo size      10333  bytes sent via SQL*Net to client        646  bytes received via SQL*Net from client         16  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        224  rows processed这里是--select count(*) from brt_hzjcztok where JYRQZ>='20130727' AND JYRQZ<='20130728'3885 走INDEX RANGE SCAN后返回3885条记录后,在过滤43条记录。