ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Function-based Index and Or-Expansion

Function-based Index and Or-Expansion

原创 Linux操作系统 作者:wei-xh 时间:2011-03-09 10:59:09 0 删除 编辑

原文连接:http://dioncho.wordpress.com/page/17/

1. Create objects.

drop table t1 purge;

create table t1(c1 int, c2 int, c3 int);

insert into t1
select level, level, level
from dual
connect by level <= 100000;

create index t1_n1 on t1(c1+1);  -- function-based index
create index t1_n2 on t1(c2+1);  -- function-based index
create index t1_n3 on t1(c1);  -- normal index
create index t1_n4 on t1(c2);  -- normal index

exec dbms_stats.gather_table_stats(user, 't1');

2.With index combination enabled, Oracle builds the most efficient execution plan imagineable.

alter session set "_b_tree_bitmap_plans" = true;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

-------------------------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |       |     2 |    48 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1    |     2 |    48 |     2   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS    |       |       |       |            |
|   3 |    BITMAP OR                     |       |       |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |
|*  5 |      INDEX RANGE SCAN            | T1_N1 |       |       |     1   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |
|*  7 |      INDEX RANGE SCAN            | T1_N2 |       |       |     1   (0)|
-------------------------------------------------------------------------------

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

   5 - access("C1"+1=1)
   7 - access("C2"+1=1)                                                       

3. What if the index combination got disabled?

alter session set "_b_tree_bitmap_plans" = false; -- In standard edition, this would be fixed behavior.

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    48 |    99   (6)|
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    48 |    99   (6)|
---------------------------------------------------------------

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

   1 - filter("C1"+1=1 OR "C2"+1=1)                           

4. But with normal indexes, Oracle’s choice is Or-Expansion, which is quite natural and efficient.

explain plan for
select *
from t1
where c1 = 1 or c2 = 1
;

---------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    30 |     4   (0)|
|   1 |  CONCATENATION               |       |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | T1_N4 |     1 |       |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  5 |    INDEX RANGE SCAN          | T1_N3 |     1 |       |     1   (0)|
---------------------------------------------------------------------------

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

   3 - access("C2"=1)
   4 - filter(LNNVL("C2"=1))
   5 - access("C1"=1)

5. Okay, maybe Oracle has some logic holes with function-based indexes. Would appropriate hints force the Or-Expansion with function-based indexes?

explain plan for
select
  /*+
    INDEX_RS_ASC(@"SEL$1_2"
"T1"@"SEL$1_2" "T1_N1")
    INDEX_RS_ASC(@"SEL$1_1"
"T1"@"SEL$1" "T1_N2")
    USE_CONCAT(@"SEL$1" 8) */
  *
from t1
where c1+1 = 1 or c2+1 = 1
;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    48 |   195   (4)|
|   1 |  CONCATENATION     |      |       |       |            |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    24 |    98   (5)|
|*  3 |   TABLE ACCESS FULL| T1   |     1 |    24 |    98   (5)|
----------------------------------------------------------------

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

   2 - filter("C2"+1=1)
   3 - filter("C1"+1=1 AND LNNVL("C2"+1=1))

No.

The question is why this is happening. This is well documented here.
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_in.htm#1006464

Yes, unfortunately, Oracle is not able to use function-based indexes with or expansion. Hence in this case, full table scan is the only option remaining.

6. There are another trick we can try when the SQL text itself is not modifiable. Stored outline. But in this case, even stored outline cannot change the execution plan to use function-based indexes. Mission impossible.

7. The last trick. Oracle 10g supports the feature called advanced query rewriting, which enables us to change the SQL text on the fly. But it has many restrictions.

  • Enterpnrise edition feature.
  • Select stateme only.
  • Bind variable not supported.

Fortunately, my simple and stupid test case can be resolved with this feature.

begin
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
     name           => 'rewrite1',
     source_stmt =>
'select *
from t1
where c1+1 = 1 or c2+1 = 1',
    destination_stmt =>
'select *
from t1
where c1 = 0 or c2 = 0',
     validate       => false,
     rewrite_mode   => 'text_match');
end;
/

alter session set query_rewrite_integrity = trusted;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

---------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    30 |     4   (0)|
|   1 |  CONCATENATION               |       |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | T1_N4 |     1 |       |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  5 |    INDEX RANGE SCAN          | T1_N3 |     1 |       |     1   (0)|
---------------------------------------------------------------------------

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

   3 - access("C2"=0)
   4 - filter(LNNVL("C2"=0))
   5 - access("C1"=0)

But, this feature would not be easily adopted in the real life. Too many restrictions.

It was a sad story, which made me think about how to control the execution plan of the non-modifiable SQL.

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

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2315152