待优化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得到了一定程度的优化。
