ORACLE 组合索引 使用分析

在ORACLE 中可以创建组合索引，使用表中多个字段的组合作为索引的键值。

当我们进行查询时可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，这样的限制条件都会使用索引，但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。

就此我做了下试验：

SQL> create table zh
  2  (it int,
  3   name varchar(30),
  4   id number(30));

表已创建。

SQL> R
  1  INSERT INTO ZH
  2* SELECT * FROM ZH

已创建147456行。

（创建了147456行）

SQL> COMMIT;

提交完成。

SQL> CREATE INDEX ZHIN
  2  ON ZH(IT,NAME);

索引已创建。

SQL> COMMIT;

提交完成。

SQL> INSERT INTO ZH
  2  VALUES(3,'GAOJING',444);

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM ZH
  2  WHERE IT=3;

        IT NAME                                   ID
---------- ------------------------------ ----------
         3 GAOJING                               444

SQL> SET AUTOTRACE ON;
SQL> R
  1  SELECT * FROM ZH
  2* WHERE IT=3

        IT NAME                                   ID
---------- ------------------------------ ----------
         3 GAOJING                               444

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=4 Card=1 Bytes=43)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ZH' (TABLE) (Cost=4 Card
   2    1     INDEX (RANGE SCAN) OF 'ZHIN' (INDEX) (Cost=3 Card=1)

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

SQL> L
  1  SELECT * FROM ZH
  2* WHERE IT=3
SQL> L2
  2* WHERE IT=3
SQL> C/IT=3/NAME='GAOJING'
  2* WHERE NAME='GAOJING'
SQL> R
  1  SELECT * FROM ZH
  2* WHERE NAME='GAOJING'

        IT NAME                                   ID
---------- ------------------------------ ----------
         3 GAOJING                               444

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=201 Card=10 Bytes=
   1    0   TABLE ACCESS (FULL) OF 'ZH' (TABLE) (Cost=201 Card=10 Byte

SQL> select * from zh
  2  where it=3 and name='GAOJING';

        IT NAME                                   ID
---------- ------------------------------ ----------
         3 GAOJING                               444

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=4 Card=1 Bytes=43)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ZH' (TABLE) (Cost=4 Card
   2    1     INDEX (RANGE SCAN) OF 'ZHIN' (INDEX) (Cost=3 Card=1)

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

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

试验证明结果确实如此