How the CBO Transforms ORs into Compound Queries

If a query contains a WHERE clause with multiple conditions combined with OR operators, then the optimizer transforms it into an equivalent compound
query that uses the UNION ALL set operator, if this makes the query execute more efficiently:
1 If each condition individually makes an index access path available, then the
optimizer can make the transformation. The optimizer chooses an execution
plan for the resulting statement that accesses the table multiple times using the
different indexes and then puts the results together.
如果单独的条件能够单独的使用索引,这时候oracle会做transformation
2 If any condition requires a full table scan because it does not make an index
available, then the optimizer does not transform. the statement. The optimizer
chooses a full table scan to execute the statement, and Oracle tests each row in
the table to determine whether it satisfies any of the conditions.
如果任意一个条件导致全表扫描,oracle都不会transformation,而最终采用全表扫描.
3 For statements that use the CBO, the optimizer might use statistics to determine
whether to make the transformation, by estimating and then comparing
execution costs of the original statement and the resulting statement.
对于cbo,oracle会根据cost来决定采用哪种执行计划
4The CBO does not use the OR transformation for IN-lists or ORs on the same
column; instead, it uses the INLIST iterator operator.
对于cbo,使用in或者同一个列上使用or时,不是进行transformation,而是采用inlist

SQL> create table test as select * from dba_objects; Table created SQL> create index object_id on test(object_id); Index created SQL> create index object_name on test(object_name); Index created SQL> exec dbms_stats.gather_table_stats(user,'TEST'); PL/SQL procedure successfully completed SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'FUGUOLIANG', TABNAME => 'TEST',ESTIMATE_PERCENT =>100 , BLOCK_SAMPLE => TRUE, METHOD_OPT => 'FOR COLUMNS OWNER SIZE 254', DEGREE => 16, GRANULARITY => 'ALL', CASCADE => TRUE); PL/SQL procedure successfully completed SQL> alter session set "_b_tree_bitmap_plans"=false; 会话已更改。 SQL> SELECT * FROM test WHERE object_id=122 OR object_name='SYS'; 执行计划 ---------------------------------------------------------- Plan hash value: 1747223187 -------------------------------------------------------------------------------------------- | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU) | Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |             |     3 |   279 |     4   (0)| 00:00:01 | |   1 |  CONCATENATION               |             |       |       ||          | |   2 |   TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    93 |     2   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | OBJECT_ID   |     1 |       |     1   (0)| 00:00:01 | |*  4 |   TABLE ACCESS BY INDEX ROWID| TEST        |     2 |   186 |     2   (0)| 00:00:01 | |*  5 |    INDEX RANGE SCAN          | OBJECT_NAME |     2 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("OBJECT_ID"=122)    4 - filter(LNNVL("OBJECT_ID"=122))    5 - access("OBJECT_NAME"='SYS')

在"_b_tree_bitmap_plans"为true的时候，将不会使用CONCATENATION

SQL> set autot trace exp SQL> SELECT * FROM test WHERE  object_name='SYS' OR object_id=122; 执行计划 ---------------------------------------------------------- Plan hash value: 3344651087 -------------------------------------------------------------------------------- ---------------- | Id  | Operation                        | Name        | Rows  | Bytes | Cost (% CPU)| Time     | -------------------------------------------------------------------------------- ---------------- |   0 | SELECT STATEMENT                 |             |     3 |   279 |     3  (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID     | TEST        |     3 |   279 |     3  (0)| 00:00:01 | |   2 |   BITMAP CONVERSION TO ROWIDS    |             |       |       |     |          | |   3 |    BITMAP OR                     |             |       |       |     |          | |   4 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |     |          | |*  5 |      INDEX RANGE SCAN            | OBJECT_NAME |       |       |     1  (0)| 00:00:01 | |   6 |     BITMAP CONVERSION FROM ROWIDS|             |       |       |     |          | |*  7 |      INDEX RANGE SCAN            | OBJECT_ID   |       |       |     1  (0)| 00:00:01 | -------------------------------------------------------------------------------- ---------------- Predicate Information (identified by operation id): ---------------------------------------------------    5 - access("OBJECT_NAME"='SYS')    7 - access("OBJECT_ID"=122)

它实际走的是index_combine，在*的情况下是不能走index_join（因为不能通过索引获得全部数据）的，我们可以修改如下：

SQL> SELECT/*+ index_join(test object_id owner)*/ owner,object_id FROM test WHERE  WNER='FUGUOLIANG' AND object_id=122; 执行计划 ---------------------------------------------------------- Plan hash value: 3568469156 -------------------------------------------------------------------------------- ------- | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Tim e     | -------------------------------------------------------------------------------- ------- |   0 | SELECT STATEMENT   |                  |     1 |    11 |     3  (34)| 00: 00:01 | |*  1 |  VIEW              | index$_join$_001 |     1 |    11 |     3  (34)| 00: 00:01 | |*  2 |   HASH JOIN        |                  |       |       |            |       | |*  3 |    INDEX RANGE SCAN| OBJECT_ID        |     1 |    11 |     1   (0)| 00: 00:01 | |*  4 |    INDEX RANGE SCAN| OWNER            |     1 |    11 |     1   (0)| 00: 00:01 | -------------------------------------------------------------------------------- ------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("OBJECT_ID"=122 AND "OWNER"='FUGUOLIANG')    2 - access(ROWID=ROWID)    3 - access("OBJECT_ID"=122)    4 - access("OWNER"='FUGUOLIANG')

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

转载于:http://blog.itpub.net/10805681/viewspace-615477/