在Oracle中可以创建组合索引,即同时包含两个或两个以上列的索引。在组合索引的使用方面,Oracle有以下特点:
SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ---------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
表中的数据分布情况如下:
SQL> select object_type,count(*) from t group by object_type;
OBJECT_TYPE COUNT(*)
------------------ ----------
CONSUMER GROUP 20
EVALUATION CONTEXT 10
FUNCTION 360
INDEX 69
LIBRARY 20
LOB 20
OPERATOR 20
PACKAGE 1210
PROCEDURE 130
SYNONYM 16100
TABLE 180
TYPE 2750
VIEW 8600
已选择13行。
SQL> select count(*) from t;
COUNT(*)
----------
29489
SQL> create index indx_t on t(object_type,object_name);
索引已创建。
SQL> ANALYZE TABLE T COMPUTE STATISTICS
2 FOR TABLE
3 FOR ALL INDEXES
4 FOR ALL INDEXED COLUMNS
5 /
表已分析。
现在让我们编写几条SQL语句来测试一下Oracle优化器对访问路径的选择:
SQL> set autotrace traceonly
SQL> SELECT * FROM T WHERE OBJECT_TYPE='LOB';
已选择20行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=20 Bytes=1740)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=22 Card=20 Bytes=1740)
2 1 INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=2 Card=20)
SQL> SELECT * FROM T WHERE OBJECT_TYPE='SYNONYM';
已选择16100行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=16100 Bytes=1400700)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=16100 Bytes=1400700)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1438 consistent gets
13 physical reads
0 redo size
941307 bytes sent via SQL*Net to client
12306 bytes received via SQL*Net from client
1075 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16100 rows processed
SQL> SELECT * FROM T WHERE OBJECT_TYPE='SYNONYM';
已选择16100行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16180 Card=16100 Bytes=1400700)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16180 Card=16100 Bytes=1400700)
2 1 INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=80 Card=16100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17253 consistent gets
16 physical reads
0 redo size
298734 bytes sent via SQL*Net to client
12306 bytes received via SQL*Net from client
1075 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16100 rows processed
下面,让我们来看看where子句中没有索引前导列的情况:
SQL> select * from t where object_name= 'DEPT';
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=14 Bytes=1218)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=29 Card=14 Bytes=1218)
2 1 INDEX (SKIP SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=14 Card=14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
1224 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select * from t where object_name= 'DEPT';
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=14 Bytes=1218)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=14 Bytes=1218)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
375 consistent gets
17 physical reads
0 redo size
1224 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
继续我们的测试,现在我们来看看Oracle不选择使用索引的情况:
SQL> select * from t where object_name LIKE 'DE%';
已选择180行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=37 Bytes=3219)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=37 Bytes=3219)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
386 consistent gets
16 physical reads
0 redo size
12614 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
180 rows processed
SQL> select * from t where object_name LIKE 'DE%';
已选择180行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=182 Card=37 Bytes=3219)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=182 Card=37 Bytes=3219)
2 1 INDEX (FULL SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=144 Card=37)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
335 consistent gets
0 physical reads
0 redo size
4479 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
180 rows processed