至少应包含组合索引的第一列，以实现ORACLE+SQL性能优化（全面）

至少要包含组合索引的第一列 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10)); Table created. SQL> create index multindex on multiindexusage(inda,indb); Index created. SQL> set autotrace traceonly SQL> select * from multiindexusage where inda = 1; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE' 2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE) SQL> select * from multiindexusage where indb = 1; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE' 很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引