本文共 11628 字，大约阅读时间需要 38 分钟。
在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。组合索引的使用存在着一定的局限，只有在谓词中出现全部索引列时才能使用效率最高的index unique scan, 否则谓词中必须包含前导列，否则会走Index full scan或者FTS。
SQL> create index idx_test on yangtest (object_type,object_name);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'YANGTEST',cascade=>true);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 20.78
SQL> select object_type,count(*) from yangtest group by object_type order by 2;
OBJECT_TYPE COUNT(*)
------------------- ----------
EDITION 1
RULE 1
MATERIALIZED VIEW 1
SCHEDULE 2
WINDOW GROUP 4
DIRECTORY 5
UNDEFINED 6
LOB PARTITION 7
RESOURCE PLAN 7
CONTEXT 7
WINDOW 9
CLUSTER 10
JOB 11
EVALUATION CONTEXT 11
INDEXTYPE 11
JOB CLASS 13
CONSUMER GROUP 14
RULE SET 17
PROGRAM 18
QUEUE 33
OPERATOR 57
XML SCHEMA 91
TABLE PARTITION 108
INDEX PARTITION 128
PROCEDURE 131
LIBRARY 179
TYPE BODY 224
SEQUENCE 227
FUNCTION 296
JAVA DATA 324
TRIGGER 482
LOB 760
JAVA RESOURCE 833
PACKAGE BODY 1206
PACKAGE 1267
TABLE 2543
TYPE 2616
INDEX 3194
VIEW 4749
JAVA CLASS 22103
SYNONYM 26670
已选择41行。
已用时间: 00: 00: 00.09
1、当使用基于规则的优化器(RBO)时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引；
SQL> set autot trace
SQL> select /*+ rule */ * from yangtest where object_type='JOB';
已选择11行。
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 2067289980
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST |
|* 2 | INDEX RANGE SCAN | IDX_TEST |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='JOB')
Note
-----
- rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
2310 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
-- 而让CBO自己选择时，却选择了TFS，从信息统计里面可以看出consistent gets 是前者的100倍。CBO 也不一定很聪明。
SQL> select * from yangtest where object_type='JOB';
已选择11行。
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1668 | 164K| 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 1668 | 164K| 275 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='JOB')
统计信息
----------------------------------------------------------
264 recursive calls
0 db block gets
1050 consistent gets
0 physical reads
0 redo size
2006 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
由于使用了组合索引的前导列并且访问了表中的少量记录，Oracle明智地选择了索引扫描。那么，如果我们访问表中的大量数据时，Oracle会选择什么样的访问路径呢？看下面的测试：
SQL> select * from yangtest where object_type='SYNONYM';
已选择26670行。
已用时间: 00: 00: 01.42
执行计划
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1668 | 164K| 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 1668 | 164K| 275 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='SYNONYM')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2769 consistent gets
0 physical reads
0 redo size
1228701 bytes sent via SQL*Net to client
19963 bytes received via SQL*Net from client
1779 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26670 rows processed
测试一下是使用RULE 的优化器。
SQL> select /*+ rule */ * from yangtest where object_type='SYNONYM';
已选择26670行。
已用时间: 00: 00: 01.56
执行计划
----------------------------------------------------------
Plan hash value: 2067289980
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST |
|* 2 | INDEX RANGE SCAN | IDX_TEST |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='SYNONYM')
Note
-----
- rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
23543 consistent gets --明显比cbo的执行计划的多10倍。
0 physical reads
0 redo size
3235078 bytes sent via SQL*Net to client
19963 bytes received via SQL*Net from client
1779 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26670 rows processed
从以上结果可以看出，在访问大量数据的情况下，使用索引确实会导致更高的执行成本，这从statistics部分的逻辑读取数(consistent gets)就可以看出，使用索引导致的逻辑读取数是不使用索引导致的逻辑读的10倍还多。因此，Oracle明智地选择了全表扫描而不是索引扫描。
下面，让我们来看看where子句中没有索引前导列的情况：
SQL> select * from yangtest where object_name ='EMP';
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 4208055961
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 202 | 45 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST | 2 | 202 | 45 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TEST | 2 | | 43 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='EMP')
filter("OBJECT_NAME"='EMP')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
35 consistent gets
1 physical reads
0 redo size
1337 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from yangtest where object_name ='YANGTEST';
未选定行
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 4208055961
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 202 | 45 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST | 2 | 202 | 45 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TEST | 2 | | 43 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='YANGTEST')
filter("OBJECT_NAME"='YANGTEST')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
1124 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
没有使用前导列，Oracle正确地选择了索引跳跃扫描。我们再来看看如果不使用索引跳跃扫描，该语句的成本：
SQL> select /*+ NO_INDEX(YANGTEST,IDX_TEST)*/ * from yangtest where object_name ='DEPT';
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 202 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 2 | 202 | 275 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DEPT')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1011 consistent gets --是使用索引跳跃扫描的50倍左右
0 physical reads
0 redo size
1335 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from yangtest where object_name like 'T%';
已选择136行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 925 | 93425 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| YANGTEST | 925 | 93425 | 275 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME" LIKE 'T%')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1020 consistent gets
0 physical reads
0 redo size
8900 bytes sent via SQL*Net to client
515 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
136 rows processed
这次只选择了136条数据，跟表YANGTEST中总的数据量29489条相比，显然只是很小的一部分，但是Oracle还是选择了全表扫描，有1020 个逻辑读。这种情况下，如果我们强制使用索引.结果如下
SQL> select /*+ INDEX(YANGTEST,IDX_TEST)*/ * from yangtest where object_name like 'T%';
已选择136行。
已用时间: 00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 972231820
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 925 | 93425 | 1084 (1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| YANGTEST | 925 | 93425 | 1084 (1)| 00:00:14 |
|* 2 | INDEX FULL SCAN | IDX_TEST | 925 | | 424 (1)| 00:00:06 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME" LIKE 'T%')
filter("OBJECT_NAME" LIKE 'T%')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
537 consistent gets
0 physical reads
0 redo size
14700 bytes sent via SQL*Net to client
515 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
136 rows processed
通过添加提示(hint)，我们强制Oracle使用了索引扫描(index full scan)，执行了335个逻辑读，比使用全表扫描的时候少了一些。
由此可见，Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示(hint)，我们可以帮助Oracle优化器作出更好的选择。
转载地址：https://blog.csdn.net/weixin_34518190/article/details/116359097 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！
发表评论
最新留言
关于作者
