ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How the CBO Transforms ORs into Compound Queries

How the CBO Transforms ORs into Compound Queries

原创 Linux操作系统 作者:fulzu 时间:2009-09-24 14:20:10 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle的like
请登录后发表评论 登录
全部评论

注册时间:2008-03-28

  • 博文量
    68
  • 访问量
    72742