Search

OakieTags

more tags

Who's online

There are currently 1 user and 30 guests online.

Online users

Recent comments

Affiliations

Home

FBI trouble

Submitted by Timur Akhmadeev on Wed, 2011-12-14 15:45

In our application we extensively use a function-based index on an important table. Couple of days ago I’ve seen an interesting issue associated with this FBI, view and a GROUP BY query. I have to say I don’t have an explanation what exactly it is and how I should call it properly, hence just “trouble” in the subject line.

This is a synthetic setup to demonstrate the case:

drop table t cascade constraints purge
;
create table t (
    id    number not null,
    x     varchar2(100),
    y     number,
    pad   varchar2(50)
);
insert into t
with g as (select /*+ materialize */ null from all_source where rownum <= 1000)
select mod(rownum, 1000) id,
       lpad('x', rownum/1e5, 'x') x,
       1 y,
       lpad('x', 50, 'x') pad
  from g g1, g g2
 where rownum <= 1e6
;

create index t_indx on t(id, coalesce(upper(x), to_char(y)))
;
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1', cascade=>true)
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all hidden columns size 1', no_invalidate=>false)

create or replace view v1
as
select t.*, coalesce(upper(x), to_char(y)) func from t
;
create or replace view v2
as
select v1.id, nvl(coalesce(upper(x), to_char(y)), 1) y, func from v1
;

So there’s a table and a function-based index defined. There’re also two views selecting data from table: one that adds FBI column, the other tries to restrict selected data to that stored in the index. So far so good:

SQL> explain plan for select * from v2 where id = :1;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'+outline +projection'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 4058602070

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |  1000 | 10000 |     5   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_INDX |  1000 | 10000 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$5C160134" "T"@"SEL$3" "T_INDX")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$335DD26A")
      OUTLINE_LEAF(@"SEL$5C160134")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - access("T"."ID"=TO_NUMBER(:1))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T"."ID"[NUMBER,22], COALESCE(UPPER("X"),TO_CHAR("Y"))[VARCHAR2,1
       00]

39 rows selected.

A query gets pure index access and does not touch the table at all, as wanted. Now kind of dumb query is fired against this view:

SQL> explain plan for
  2  select count(t2.y)
  3    from (select 1 id from dual) t1
  4        ,(select id, y from v2 where id = :1) t2
  5  where t1.id = t2.id(+)
  6  group by t1.id
  7  ;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'+outline +projection'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2568130530

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    27 |     7   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT         |        |     1 |    27 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER          |        |     1 |    27 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | DUAL   |     1 |     2 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T      |     1 |    25 |     5   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | T_INDX |     1 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$70D5F4CE" "T"@"SEL$5")
      LEADING(@"SEL$70D5F4CE" "DUAL"@"SEL$2" "T"@"SEL$5")
      INDEX_RS_ASC(@"SEL$70D5F4CE" "T"@"SEL$5" "T_INDX")
      FULL(@"SEL$70D5F4CE" "DUAL"@"SEL$2")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$4")
      MERGE(@"SEL$5")
      OUTLINE(@"SEL$7286615E")
      OUTLINE(@"SEL$3")
      MERGE(@"SEL$7286615E")
      OUTLINE(@"SEL$C8360722")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$C8360722")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$70D5F4CE")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   5 - access("T"."ID"(+)=TO_NUMBER(:1))
       filter("T"."ID"(+)=CASE  WHEN (ROWID IS NOT NULL) THEN 1 ELSE 1 END )

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) 1[2], COUNT(CASE  WHEN ROWID IS NOT NULL THEN
       NVL(COALESCE(UPPER("T"."X"),TO_CHAR("T"."Y")),'1') ELSE NULL END )[22]
   2 - (#keys=0) ROWID[ROWID,10], "T"."X"[VARCHAR2,100], "T"."Y"[NUMBER,22]
   3 - ROWID[ROWID,10]
   4 - ROWID[ROWID,10], "T"."X"[VARCHAR2,100], "T"."Y"[NUMBER,22]
   5 - ROWID[ROWID,10]

56 rows selected.

See how Oracle reacted. It’s now accessing the table, and it’s not wanted at all. Optimizer trace shows that the query undergoes several view merging and is transformed to

select count(case
                 when "T".ROWID is not null then
                  nvl(coalesce(upper("T"."X"), to_char("T"."Y")), 1)
                 else
                  null
             end) "COUNT(T2.Y)"
  from "SYS"."DUAL" "DUAL", TIM."T" "T"
 where case
           when "DUAL".ROWID is not null then
            1
           else
            1
       end = "T"."ID"(+)
   and "T"."ID"(+) = :b1
 group by 1

I assume this transformation resulted in additional CASE expression in the select list and this is what made Oracle to access table. Well, now I know why it happens and how to get rid of it, but I really curious to know how to call and control it. There’s really no information in 10053 trace about this feature, except that the predicate first appears under “Outer Join Elimination” transformation – but turning OJE off does not change anything. If anyone knows more on this topic – welcome to comments.

Update Dec 19th I’ve uploaded 10053 trace generated for a modified query (see comments): link to download. Please note that the file is plain *.txt with .doc extension.

Filed under: CBO, Oracle Tagged: CBQT, FBI, indexes