增加复合索引优化SQL的简单过程

待优化SQL:
select t2.emperor,t1.* from t1,t2 where t1.countryid=t2.id and t1.CountryId=1010 and t1.DistrictId=1 and playerId<>100

T1表上的索引:
SQL> select index_name,column_name from user_ind_columns where table_name='T1';

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
PK_T1                   PLAYERID
T1_CID_IDX              COUNTRYID
T1_IDX                  DISTRICTID
T1_NO_IDX               ARMYNO

T2表上的索引:
SQL> select index_name,column_name from user_ind_columns where table_name='T2';

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
PK_T2                 ID
INDEX_T2              ID
INDEX_T2              DISTRICTID

这些索引都是开发人员建的。

先查看一下执行计划：
SQL> explain plan for select t2.emperor,t1.* from t1,t2 where t1.countryid=t2.id and t1.CountryId=1010 and t1.DistrictId=1 and playerId<>100;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id  | Operation                    |  Name              | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |  6266 |   514K|   211 |
|   1 |  NESTED LOOPS                |                    |  6266 |   514K|   211 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2                 |     1 |    26 |     1 |
|*  3 |    INDEX RANGE SCAN          | INDEX_T2           |     1 |       |     2 |
|*  4 |   TABLE ACCESS FULL          | T1                 |  7641 |   432K|   210 |
-----------------------------------------------------------------------------------

SQL> set autot traceonly
SQL> select t2.emperor,t1.* from t1,t2 where t1.countryid=t2.id and t1.CountryId=1010 and t1.DistrictId=1 and playerId<>100;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=211 Card=6266 Bytes=526344)
   1    0   NESTED LOOPS (Cost=211 Card=6266 Bytes=526344)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=1 Card=1 Bytes=26)
   3    2       INDEX (RANGE SCAN) OF 'INDEX_T2' (NON-UNIQUE) (Cost=2 Card=1)
   4    1     TABLE ACCESS (FULL) OF 'T1' (Cost=210 Card=7641 Bytes=443178)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2289  consistent gets
          0  physical reads
          0  redo size
     135776  bytes sent via SQL*Net to client
       1735  bytes received via SQL*Net from client
        114  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1687  rows processed

发现T1没走索引，走了全表扫描（T1,T2都经过了分析），尝试加INDEX HINT，结果逻辑读和COST都比不加还要高，CBO计算得没错，所以它选择了FULL TABLE。

之后在表T1中增加复合索引T1_CDID_IDX：
SQL> select index_name,column_name from user_ind_columns where table_name='T1';

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
PK_T1                   PLAYERID
T1_CID_IDX              COUNTRYID
T1_IDX                  DISTRICTID
T1_NO_IDX               ARMYNO
T1_CDID_IDX             DISTRICTID
T1_CDID_IDX             COUNTRYID

SQL> set autot traceonly
SQL> select t2.emperor,t1.* from t1,t2 where t1.countryid=t2.id and t1.CountryId=1010 and t1.DistrictId=1 and playerId<>100;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=32 Card=6266 Bytes=526344)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=31 Card=7641 Bytes=443178)
   2    1     NESTED LOOPS (Cost=32 Card=6266 Bytes=526344)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=1 Card=1 Bytes=26)
   4    3         INDEX (RANGE SCAN) OF 'INDEX_T2' (NON-UNIQUE) (Cost=2 Card=1)
   5    2       INDEX (RANGE SCAN) OF 'T1_CDID_IDX' (NON-UNIQUE) (Cost=1 Card=7641)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1075  consistent gets
          7  physical reads
          0  redo size
     135777  bytes sent via SQL*Net to client
       1735  bytes received via SQL*Net from client
        114  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1687  rows processed

由此看到，增加了复合索引后T1表也走了索引，COST由221降到了32，逻辑读也降低了一半多，SQL得到了一定程度的优化。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17997/viewspace-256888/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17997/viewspace-256888/