在SunnyXu 的SQL语句的执行计划优化SQL的有一段关于组合索引的描述:
组合索引(concatenated index) 由多个列构成的索引,如create index idx_emp on emp(col1, col2, col3, ……),则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念:引导列(leading column),在上面的例子中,col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”,也可以使用”where col1 = ? and col2 = ?”,这样的限制条件都会使用索引,但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时,该限制条件才会使用该组合索引。
实际上,我们可以发现oracle在组合索引使用中,并不是一定要加上前导列才会使用索引。实验如下:
SQL> select count(*) from test ;
COUNT(*)
----------
21760
SQL> set autotrace traceonly
SQL> create index ind_test on test(DESTADDR,SERIALNO) ;
SQL> select SERIALNO,DESTADDR from test where DESTADDR=13914439111 and SERIALNO=
100295866051 ;
已选择512行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=5 Bytes=115)
1 0 INDEX (FAST FULL SCAN) OF 'IND_TEST' (NON-UNIQUE) (Cost=18
Card=5 Bytes=115)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
295 consistent gets
0 physical reads
0 redo size
7177 bytes sent via SQL*Net to client
877 bytes received via SQL*Net from client
36 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
512 rows processed
SQL> select SERIALNO,DESTADDR from test where DESTADDR=13914439111;
已选择512行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=345 Bytes=79
35)
1 0 INDEX (FAST FULL SCAN) OF 'IND_TEST' (NON-UNIQUE) (Cost=18
Card=345 Bytes=7935)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
295 consistent gets
0 physical reads
0 redo size
7177 bytes sent via SQL*Net to client
877 bytes received via SQL*Net from client
36 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
512 rows processed
SQL> select SERIALNO,DESTADDR from test where SERIALNO=100295866051;
已选择512行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=345 Bytes=79
35)
1 0 INDEX (FAST FULL SCAN) OF 'IND_TEST' (NON-UNIQUE) (Cost=18
Card=345 Bytes=7935)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
295 consistent gets
0 physical reads
0 redo size
7177 bytes sent via SQL*Net to client
877 bytes received via SQL*Net from client
36 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
512 rows processed
从前面的实验可以看到。使用索引并一定需要前导列。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10867315/viewspace-194834/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10867315/viewspace-194834/