小议USE_CONCAT提示 (转）

原文：http://yangtingkun.itpub.net/post/468/500859

 

前两天写了篇文章描述在一个查询中使用两个以上索引的几种情况，有人在我的BLOG里面回复提到了USE_CONCAT提示，这里就简单说明一下。

查询访问同一表的两个以上索引（一）：http://yangtingkun.itpub.net/post/468/499769

查询访问同一表的两个以上索引（二）：http://yangtingkun.itpub.net/post/468/499798

查询访问同一表的两个以上索引（三）：http://yangtingkun.itpub.net/post/468/499808

其实利用USE_CONCAT提示生成的CONCATENATION执行计划和上面讨论的情况有所不同。上面三种情况所有的查询条件都是AND的关系，而USE_CONCAT提示适用于查询条件的OR关系，这种执行计划其实和BITMAP OR操作十分类似。

简单的说，CONCATENATION和UNION/UNION ALL操作比较类似，根据OR查询条件，将一个查询分解为两个或更多的部分，然后在去掉两个部分重复的记录。由于CONCATENATION执行计划需要去掉重复的记录，因此和UNION ALL不同。和UNION也不同的是，这个执行计划并不会去掉所有的重复记录，而只是CONCATENATION包含两个子查询之间重复的记录被过滤掉。

SQL> CREATE TABLE T_CONCAT
2 (ID NUMBER,
3 NAME VARCHAR2(30),
4 TYPE VARCHAR2(30));

表已创建。

SQL> INSERT INTO T_CONCAT
2 SELECT ROWNUM,
3 OBJECT_NAME,
4 OBJECT_TYPE
5 FROM ALL_OBJECTS;

已创建69408行。

SQL> CREATE INDEX IND_CONCAT_NAME
2 ON T_CONCAT (NAME);

索引已创建。

SQL> CREATE INDEX IND_CONCAT_TYPE
2 ON T_CONCAT(TYPE);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_CONCAT')

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*)
2 FROM T_CONCAT
3 WHERE NAME = 'T_CONCAT'
4 OR TYPE = 'DATABASE LINK';

COUNT(*)
----------
1

执行计划
----------------------------------------------------------
Plan hash value: 1182419877

--------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows | Bytes |Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 35| 6 (0)| 00:00:01 |
| 1| SORT AGGREGATE | | 1| 35| | |
| 2| BITMAP CONVERSION COUNT | | 2778|97230| 6 (0)| 00:00:01 |
| 3| BITMAP OR | | | | | |
| 4| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*5| INDEX RANGE SCAN | IND_CONCAT_TYPE | | | 5 (0)| 00:00:01 |
| 6| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*7| INDEX RANGE SCAN | IND_CONCAT_NAME | | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("TYPE"='DATABASE LINK')
7 - access("NAME"='T_CONCAT')

SQL> SELECT /*+ USE_CONCAT */ COUNT(*)
2 FROM T_CONCAT
3 WHERE NAME = 'T_CONCAT'
4 OR TYPE = 'DATABASE LINK';

COUNT(*)
----------
1

执行计划
----------------------------------------------------------
Plan hash value: 1333442903

--------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes |Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 35| 28 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1| 35| | |
| 2 | CONCATENATION | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T_CONCAT | 2| 70| 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_CONCAT_NAME | 2| | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T_CONCAT | 2776|97160| 26 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_CONCAT_TYPE | 2776| | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("NAME"='T_CONCAT')
5 - filter(LNNVL("NAME"='T_CONCAT'))
6 - access("TYPE"='DATABASE LINK')

这就是一个使用USE_CONCAT提示的例子，如果不使用提示，Oracle会选择BITMAP OR执行计划。

其实在10g中，这个执行计划和提示已经是为了后向兼容而保留了，可以看到，默认情况下，就是使用CONCATENATION执行计划的COST更低，Oracle也不会选择这种执行计划：

SQL> SELECT *
2 FROM T_CONCAT
3 WHERE NAME = 'T_CONCAT'
4 OR TYPE = 'DATABASE LINK';

ID NAME TYPE
---------- ------------------------------ ------------------------------
67500 T_CONCAT TABLE

执行计划
----------------------------------------------------------
Plan hash value: 3064336088

--------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | |2778| 105K| 75 (2)| 00:00:02 |
| 1| TABLE ACCESS BY INDEX ROWID | T_CONCAT |2778| 105K| 75 (2)| 00:00:02 |
| 2| BITMAP CONVERSION TO ROWIDS | | | | | |
| 3| BITMAP OR | | | | | |
| 4| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*5| INDEX RANGE SCAN | IND_CONCAT_TYPE | | | 5 (0)| 00:00:01 |
| 6| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*7| INDEX RANGE SCAN | IND_CONCAT_NAME | | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("TYPE"='DATABASE LINK')
7 - access("NAME"='T_CONCAT')

SQL> SELECT /*+ USE_CONCAT */ *
2 FROM T_CONCAT
3 WHERE NAME = 'T_CONCAT'
4 OR TYPE = 'MATERIALIZED VIEW';

ID NAME TYPE
---------- ------------------------------ ------------------------------
67500 T_CONCAT TABLE

执行计划
----------------------------------------------------------
Plan hash value: 1849942237

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2778 | 105K| 28 (0)|00:00:01|
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_CONCAT | 2 | 78 | 2 (0)|00:00:01|
|* 3 | INDEX RANGE SCAN | IND_CONCAT_NAME | 2 | | 1 (0)|00:00:01|
|* 4 | TABLE ACCESS BY INDEX ROWID| T_CONCAT | 2776 | 105K| 26 (0)|00:00:01|
|* 5 | INDEX RANGE SCAN | IND_CONCAT_TYPE | 2776 | | 5 (0)|00:00:01|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("NAME"='T_CONCAT')
4 - filter(LNNVL("NAME"='T_CONCAT'))
5 - access("TYPE"='MATERIALIZED VIEW')

可以看到，即使CONCATENATION的代价更低，Oracle仍然选择了BITMAP OR执行计划，说明在10g以上，除非使用USE_CONCAT提示，否则Oracle不会自动选择这种执行计划了。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8102208/viewspace-667397/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8102208/viewspace-667397/