This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Jul 13, 2012 6:40 AM by Jonathan Lewis RSS

Why Optimizer ignore Index Fast Full Scan when much lower cost?

763581 Newbie
Currently Being Moderated
Summary (details follow below) - To improve performance of a query involving several tables I created an index on one table that included all of the columns referenced in the query. With the new index in place the optimizer is still choosing a Full Table Scan over an Index Fast Full Scan. However, by removing tables from the query one by one I reach the point where the optimizer suddenly does use the Index Fast Full Scan on this table. And "Yes", it is a much lower cost than the Full Table Scan it used before. In getting a test case I was able to get the query down to 4 tables with the optimizer still ignoring the index, and at 3 tables it will use the index.

So why is the Optimizer not choosing the Index Fast Full Scan, when it is obvious that it is so much cheaper than a Full Table Scan? And why does removing a table change the way the Optimizer works - I don't think it is an issue with the number of join permutations (see below). The query is as simple as I can make it, while still being true to the original application SQL, and it still shows this flipping in access path choice. I can run the queries one after the other and it always uses a Full Table Scan for the original query, and an Index Fast Full Scan for the modified query with one less table.

Looking at 10053 trace output for both queries I can see that for the original 4 table query the SINGLE TABLE ACCESS PATH section only costs a Full Table Scan. But for the modified query with one less table, the same table now has a cost for an Index Fast Full Scan too. And the end of the 10053 join costing does not finish with a message about exceeding the maximum number of permutations. So why is the Optimizer not costing the IFFS for the first query, when it does so for the second, near identical query?

Potentially this is an issue to do with OUTER JOINs, but why? The joins between the tables do not change when the one extra table is removed.

This is on 10.2.0.5 on Linux (Oracle Enterprise Linux). I've not set any special parameters that I am aware of. I am seeing the same behaviour on 10.2.0.4 32 bit on Windows (XP).

Thanks
John
Database Performance Blog

DETAILS
I've replicated the whole scenario via SQL scripts to create and populate the tables against which I can then run the queries. I've deliberately padded the table's so that the average row length from the generated data is similar to that of the real data. That way the statistics should be similar regarding number of blocks and so forth.

System - uname -a
Linux mysystem.localdomain 2.6.32-300.25.1.el5uek #1 SMP Tue May 15 19:55:52 EDT 2012 i686 i686 i386 GNU/Linux
Database - v$version
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
Original Query (full table details below):
SELECT 
    episode.episode_id , episode.cross_ref_id , episode.date_required , 
    product.number_required , 
    request.site_id 
FROM episode 
LEFT JOIN REQUEST on episode.cross_ref_id = request.cross_ref_id 
     JOIN product ON episode.episode_id = product.episode_id 
LEFT JOIN product_sub_type ON product.prod_sub_type_id = product_sub_type.prod_sub_type_id 
WHERE (
        episode.department_id = 2
    and product.status = 'I'
      ) 
ORDER BY episode.date_required
;
Execution Plan from display_cursor after execution:
SQL_ID  5ckbvabcmqzw7, child number 0
-------------------------------------
SELECT     episode.episode_id , episode.cross_ref_id , episode.date_required ,
product.number_required ,     request.site_id FROM episode LEFT JOIN REQUEST on
episode.cross_ref_id = request.cross_ref_id      JOIN product ON episode.episode_id =
product.episode_id LEFT JOIN product_sub_type ON product.prod_sub_type_id =
product_sub_type.prod_sub_type_id WHERE (         episode.department_id = 2 and
product.status = 'I'       ) ORDER BY episode.date_required

Plan hash value: 3976293091

-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |       |       |       | 35357 (100)|          |
|   1 |  SORT ORDER BY        |                     | 33333 |  1920K|  2232K| 35357   (1)| 00:07:05 |
|   2 |   NESTED LOOPS OUTER  |                     | 33333 |  1920K|       | 34879   (1)| 00:06:59 |
|*  3 |    HASH JOIN OUTER    |                     | 33333 |  1822K|  1728K| 34878   (1)| 00:06:59 |
|*  4 |     HASH JOIN         |                     | 33333 |  1334K|       |   894   (1)| 00:00:11 |
|*  5 |      TABLE ACCESS FULL| PRODUCT             | 33333 |   423K|       |   103   (1)| 00:00:02 |
|*  6 |      TABLE ACCESS FULL| EPISODE             |   299K|  8198K|       |   788   (1)| 00:00:10 |
|   7 |     TABLE ACCESS FULL | REQUEST             |  3989K|    57M|       | 28772   (1)| 00:05:46 |
|*  8 |    INDEX UNIQUE SCAN  | PK_PRODUCT_SUB_TYPE |     1 |     3 |       |  0   (0)|          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID")
   4 - access("EPISODE"."EPISODE_ID"="PRODUCT"."EPISODE_ID")
   5 - filter("PRODUCT"."STATUS"='I')
   6 - filter("EPISODE"."DEPARTMENT_ID"=2)
   8 - access("PRODUCT"."PROD_SUB_TYPE_ID"="PRODUCT_SUB_TYPE"."PROD_SUB_TYPE_ID")
Modified Query:
SELECT 
    episode.episode_id , episode.cross_ref_id , episode.date_required , 
    product.number_required , 
    request.site_id 
FROM episode 
LEFT JOIN REQUEST on episode.cross_ref_id = request.cross_ref_id 
     JOIN product ON episode.episode_id = product.episode_id 
WHERE (
        episode.department_id = 2
    and product.status = 'I'
      ) 
ORDER BY episode.date_required
;
Execution Plan from display_cursor after execution:
SQL_ID  gbs74rgupupxz, child number 0
-------------------------------------
SELECT     episode.episode_id , episode.cross_ref_id , episode.date_required ,
product.number_required ,     request.site_id FROM episode LEFT JOIN REQUEST on
episode.cross_ref_id = request.cross_ref_id      JOIN product ON episode.episode_id =
product.episode_id WHERE (         episode.department_id = 2     and product.status =
'I'       ) ORDER BY episode.date_required

Plan hash value: 4250628916

----------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |       | 10515 (100)|          |
|   1 |  SORT ORDER BY         |             | 33333 |  1725K|  2112K| 10515   (1)| 00:02:07 |
|*  2 |   HASH JOIN OUTER      |             | 33333 |  1725K|  1632K| 10077   (1)| 00:02:01 |
|*  3 |    HASH JOIN           |             | 33333 |  1236K|       |   894   (1)| 00:00:11 |
|*  4 |     TABLE ACCESS FULL  | PRODUCT     | 33333 |   325K|       |   103   (1)| 00:00:02 |
|*  5 |     TABLE ACCESS FULL  | EPISODE     |   299K|  8198K|       |   788   (1)| 00:00:10 |
|   6 |    INDEX FAST FULL SCAN| IX4_REQUEST |  3989K|    57M|       |  3976   (1)| 00:00:48 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID")
   3 - access("EPISODE"."EPISODE_ID"="PRODUCT"."EPISODE_ID")
   4 - filter("PRODUCT"."STATUS"='I')
   5 - filter("EPISODE"."DEPARTMENT_ID"=2)
Table Creation and Population:
1. Create Tables
2. Load data
3. Create Indexes
4. Gather statistics
--
-- Main table
--
create table episode (
episode_id number (*,0),
department_id number (*,0),
date_required date,
cross_ref_id varchar2 (11),
padding varchar2 (80),
constraint pk_episode primary key (episode_id)
) ;
--
-- Product tables
--
create table product_type (
prod_type_id number (*,0),
code varchar2 (10),
binary_field number (*,0),
padding varchar2 (80),
constraint pk_product_type primary key (prod_type_id)
) ;
--
create table product_sub_type (
prod_sub_type_id number (*,0),
sub_type_name varchar2 (20),
units varchar2 (20),
padding varchar2 (80),
constraint pk_product_sub_type primary key (prod_sub_type_id)
) ;
--
create table product (
product_id number (*,0),
prod_type_id number (*,0),
prod_sub_type_id number (*,0),
episode_id number (*,0),
status varchar2 (1),
number_required number (*,0),
padding varchar2 (80),
constraint pk_product primary key (product_id),
constraint nn_product_episode check (episode_id is not null) 
) ;
alter table product add constraint fk_product 
foreign key (episode_id) references episode (episode_id) ;
alter table product add constraint fk_product_type 
foreign key (prod_type_id) references product_type (prod_type_id) ;
alter table product add constraint fk_prod_sub_type
foreign key (prod_sub_type_id) references product_sub_type (prod_sub_type_id) ;
--
-- Requests
--
create table request (
request_id number (*,0),
department_id number (*,0),
site_id number (*,0),
cross_ref_id varchar2 (11),
padding varchar2 (80),
padding2 varchar2 (80),
constraint pk_request primary key (request_id),
constraint nn_request_department check (department_id is not null),
constraint nn_request_site_id check (site_id is not null)
) ;
--
-- Activity & Users
--
create table activity (
activity_id number (*,0),
user_id number (*,0),
episode_id number (*,0),
request_id number (*,0), -- always NULL!
padding varchar2 (80),
constraint pk_activity primary key (activity_id)
) ;
alter table activity add constraint fk_activity_episode
foreign key (episode_id) references episode (episode_id) ;
alter table activity add constraint fk_activity_request
foreign key (request_id) references request (request_id) ;
--
create table app_users (
user_id number (*,0),
user_name varchar2 (20),
start_date date,
padding varchar2 (80),
constraint pk_users primary key (user_id)
) ;

prompt Loading episode ...
--
insert into episode
with generator as 
(select rownum r
          from (select rownum r from dual connect by rownum <= 1000) a,
               (select rownum r from dual connect by rownum <= 1000) b,
               (select rownum r from dual connect by rownum <= 1000) c
         where rownum <= 1000000
       ) 
select r, 2,
    sysdate + mod (r, 14),
    to_char (r, '0000000000'),
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || to_char (r, '000000')
  from generator g
where g.r <= 300000
/
commit ;
--
prompt Loading product_type ...
--
insert into product_type
with generator as 
(select rownum r
          from (select rownum r from dual connect by rownum <= 1000) a,
               (select rownum r from dual connect by rownum <= 1000) b,
               (select rownum r from dual connect by rownum <= 1000) c
         where rownum <= 1000000
       ) 
select r, 
       to_char (r, '000000000'),
       mod (r, 2),
       'ABCDEFGHIJKLMNOPQRST' || to_char (r, '000000')
  from generator g
where g.r <= 12
/
commit ;
--
prompt Loading product_sub_type ...
--
insert into product_sub_type
with generator as 
(select rownum r
          from (select rownum r from dual connect by rownum <= 1000) a,
               (select rownum r from dual connect by rownum <= 1000) b,
               (select rownum r from dual connect by rownum <= 1000) c
         where rownum <= 1000000
       ) 
select r, 
       to_char (r, '000000'),
       to_char (mod (r, 3), '000000'),
       'ABCDE' || to_char (r, '000000')
  from generator g
where g.r <= 15
/
commit ;
--
prompt Loading product ...
--
-- product_id prod_type_id prod_sub_type_id episode_id padding 
insert into product
with generator as 
(select rownum r
          from (select rownum r from dual connect by rownum <= 1000) a,
               (select rownum r from dual connect by rownum <= 1000) b,
               (select rownum r from dual connect by rownum <= 1000) c
         where rownum <= 1000000
       ) 
select r, mod (r, 12) + 1, mod (r, 15) + 1, mod (r, 300000) + 1,
       decode (mod (r, 3), 0, 'I', 1, 'C', 2, 'X', 'U'),
       dbms_random.value (1, 100), NULL
  from generator g
where g.r <= 100000
/
commit ;
--
prompt Loading request ...
--
-- request_id department_id site_id cross_ref_id varchar2 (11) padding 
insert into request
with generator as 
(select rownum r
          from (select rownum r from dual connect by rownum <= 1000) a,
               (select rownum r from dual connect by rownum <= 1000) b,
               (select rownum r from dual connect by rownum <= 1000) c
         where rownum <= 10000000
       ) 
select r, mod (r, 4) + 1, 1, to_char (r, '0000000000'),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz01234567890123456789' || to_char (r, '000000'),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345678' || to_char (r, '000000')
  from generator g
where g.r <= 4000000
/
commit ;
--
prompt Loading activity ...
--
-- activity activity_id user_id episode_id request_id (NULL) padding 
insert into activity
with generator as 
(select rownum r
          from (select rownum r from dual connect by rownum <= 1000) a,
               (select rownum r from dual connect by rownum <= 1000) b,
               (select rownum r from dual connect by rownum <= 1000) c
         where rownum <= 10000000
       ) 
select r, mod (r, 50) + 1, mod (r, 300000) + 1, NULL, NULL
  from generator g
where g.r <= 100000
/
commit ;
--
prompt Loading app_users ...
--
-- app_users user_id user_name start_date padding 
insert into app_users
with generator as 
(select rownum r
          from (select rownum r from dual connect by rownum <= 1000) a,
               (select rownum r from dual connect by rownum <= 1000) b,
               (select rownum r from dual connect by rownum <= 1000) c
         where rownum <= 10000000
       ) 
select r, 
       'User_' || to_char (r, '000000'),
       sysdate - mod (r, 30),
       'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || to_char (r, '000000')
  from generator g
where g.r <= 1000
/
commit ;
--

prompt Episode (1)
create index ix1_episode_cross_ref on episode (cross_ref_id) ;
--
prompt Product (2)
create index ix1_product_episode on product (episode_id) ;
create index ix2_product_type on product (prod_type_id) ;
--
prompt Request (4)
create index ix1_request_site on request (site_id) ;
create index ix2_request_dept on request (department_id) ;
create index ix3_request_cross_ref on request (cross_ref_id) ;
-- The extra index on the referenced columns!!
create index ix4_request on request (cross_ref_id, site_id) ;
--
prompt Activity (2)
create index ix1_activity_episode on activity (episode_id) ;
create index ix2_activity_request on activity (request_id) ;
--
prompt Users (1)
create unique index ix1_users_name on app_users (user_name) ;
--
prompt Gather statistics on schema ...
--
exec dbms_stats.gather_schema_stats ('JB')
10053 Sections - Original query
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table: REQUEST  Alias: REQUEST
    Card: Original: 3994236  Rounded: 3994236  Computed: 3994236.00  Non Adjusted: 3994236.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  28806.24  Resp: 28806.24  Degree: 0
      Cost_io: 28738.00  Cost_cpu: 1594402830
      Resp_io: 28738.00  Resp_cpu: 1594402830
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (FullScan)
    Index: PK_REQUEST
    resc_io: 7865.00  resc_cpu: 855378926
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 7901.61  Resp: 7901.61  Degree: 0
  Access Path: index (FullScan)
    Index: PK_REQUEST
    resc_io: 7865.00  resc_cpu: 855378926
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 7901.61  Resp: 7901.61  Degree: 0
  ****** finished trying bitmap/domain indexes ******
******** End index join costing ********
  Best:: AccessPath: TableScan
         Cost: 28806.24  Degree: 1  Resp: 28806.24  Card: 3994236.00  Bytes: 0
***************************************
10053 - Modified Query
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table: REQUEST  Alias: REQUEST
    Card: Original: 3994236  Rounded: 3994236  Computed: 3994236.00  Non Adjusted: 3994236.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  28806.24  Resp: 28806.24  Degree: 0
      Cost_io: 28738.00  Cost_cpu: 1594402830
      Resp_io: 28738.00  Resp_cpu: 1594402830
  Access Path: index (index (FFS))
    Index: IX4_REQUEST
    resc_io: 3927.00  resc_cpu: 583211030
    ix_sel: 0.0000e+00  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  3951.96  Resp: 3951.96  Degree: 1
      Cost_io: 3927.00  Cost_cpu: 583211030
      Resp_io: 3927.00  Resp_cpu: 583211030
  Access Path: index (FullScan)
    Index: IX4_REQUEST
    resc_io: 14495.00  resc_cpu: 903225273
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 14533.66  Resp: 14533.66  Degree: 1
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (FullScan)
    Index: IX4_REQUEST
    resc_io: 14495.00  resc_cpu: 903225273
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 14533.66  Resp: 14533.66  Degree: 0
  Access Path: index (FullScan)
    Index: IX4_REQUEST
    resc_io: 14495.00  resc_cpu: 903225273
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 14533.66  Resp: 14533.66  Degree: 0
  ****** finished trying bitmap/domain indexes ******
******** End index join costing ********
  Best:: AccessPath: IndexFFS  Index: IX4_REQUEST
         Cost: 3951.96  Degree: 1  Resp: 3951.96  Card: 3994236.00  Bytes: 0
***************************************
  • 1. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    sb92075 Guru
    Currently Being Moderated
    post SQL & results from statements below:

    SELECT COUNT(*) FROM EPISODE;
    SELECT COUNT(*) FROM PRODUCT;
    SELECT COUNT(*) FROM EPISODE WHERE episode.department_id = 2;
    SELECT COUNT(*) FROM PRODUCT WHERE and product.status = 'I';
  • 2. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Dom Brooks Guru
    Currently Being Moderated
    Hi John,

    Thanks for the full test case and the scripts - just excellent.

    My initial reaction was that this was likely to be an issue with ANSI SQL and maybe Query Transformation.

    There are a fair few bugs and features with ANSI SQL.

    1. If you rewrite using traditional Oracle syntax, do you get the same issue?

    2. If you use a /*+ no_query_transformation */ hint, does this help?


    I don't have 10.2 available right now but from a quick look at an 11.2 trace, I see an interim transformation to a LATERAL VIEW like this:
    LATERAL( (SELECT "REQUEST"."CROSS_REF_ID" "CROSS_REF_ID_0"
              ,      "REQUEST"."SITE_ID" "SITE_ID_1"
              ,      "REQUEST"."DEPARTMENT_ID"  "DEPARTMENT_ID_2" 
              FROM   "RIMS"."REQUEST" "REQUEST" 
              WHERE  "EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID"))(+) "from$_subquery$_008"
    the extra columns of which may well be interfering with your good intentions with the index only on (cross_ref_id, site_id)


    The following posts may be useful:
    http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/
    https://blogs.oracle.com/optimizer/entry/outerjoins_in_oracle
    http://jonathanlewis.wordpress.com/2011/01/31/ansi-outer/


    But this may be a red herring because the LATERAL VIEW is not used in the "Final query after transformations".
    But this final query is meant to be just a rough representation - not the exact transformation -
    and interestingly if I execute that final version, I get the index usage you might expect (I also get the redundant table
    elimination of product_sub_type):
    SELECT "EPISODE"."EPISODE_ID" "EPISODE_ID"
          ,"EPISODE"."CROSS_REF_ID" "CROSS_REF_ID"
          ,"EPISODE"."DATE_REQUIRED" "DATE_REQUIRED"
          ,"PRODUCT"."NUMBER_REQUIRED" "NUMBER_REQUIRED"
          ,"REQUEST"."SITE_ID" "SITE_ID" 
    FROM   "RIMS"."EPISODE" "EPISODE"
          ,"RIMS"."REQUEST" "REQUEST"
          ,"RIMS"."PRODUCT" "PRODUCT" 
    WHERE "EPISODE"."DEPARTMENT_ID"=2 
    AND   "PRODUCT"."STATUS"='I' 
    AND   "EPISODE"."EPISODE_ID"="PRODUCT"."EPISODE_ID" 
    AND   "EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID"(+) 
    ORDER BY "EPISODE"."DATE_REQUIRED";
    
    ----------------------------------------------------------------------------------------------
    | Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |             | 33333 |  1725K|       |  6407   (6)| 00:00:33 |
    |   1 |  SORT ORDER BY         |             | 33333 |  1725K|  2112K|  6407   (6)| 00:00:33 |
    |*  2 |   HASH JOIN OUTER      |             | 33333 |  1725K|       |  5912   (6)| 00:00:30 |
    |*  3 |    HASH JOIN           |             | 33333 |  1236K|       |  1067   (5)| 00:00:06 |
    |*  4 |     TABLE ACCESS FULL  | PRODUCT     | 33333 |   325K|       |   125   (7)| 00:00:01 |
    |*  5 |     TABLE ACCESS FULL  | EPISODE     |   300K|  8203K|       |   931   (3)| 00:00:05 |
    |   6 |    INDEX FAST FULL SCAN| IX4_REQUEST |  4000K|    57M|       |  4736   (4)| 00:00:24 |
    ----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id): 
    --------------------------------------------------- 
                                                        
       2 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID"(+))
       3 - access("EPISODE"."EPISODE_ID"="PRODUCT"."EPISODE_ID")       
       4 - filter("PRODUCT"."STATUS"='I')                         
       5 - filter("EPISODE"."DEPARTMENT_ID"=2)                    
    In summary, probably bug / "feature" - something's getting lost along the way.

    These sorts of features are the main reason I don't tend to use ANSI SQL unless it offers a particular advantage in expressing the query/logic.



    Hope this helps.

    Cheers,
    Dominic

    Apologies if this is double posted, I'm getting HTTP 500 errors intermittently from the forum.
  • 3. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    user503699 Expert
    Currently Being Moderated
    John Brady - UK wrote:
    So why is the Optimizer not choosing the Index Fast Full Scan, when it is obvious that it is so much cheaper than a Full Table Scan?
    John,

    First, thanks a lot for posting an interesting problem and complete set-up scripts.
    I don't have your exact version but an non-patched one (i.e. 10.2.0.1). But here is what I got
    My configuration details:
    SQL> select * from v$version ;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE     10.2.0.1.0     Production
    TNS for Linux: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    SQL> !uname -a
    Linux my-laptop 2.6.24-24-generic #1 SMP Tue Jul 7 19:46:39 UTC 2009 i686 GNU/Linux
    Original Query:
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID     c5hytjq270maz, child number 0
    -------------------------------------
    SELECT        episode.episode_id , episode.cross_ref_id , episode.date_required ,
    product.number_required ,     request.site_id FROM episode LEFT JOIN REQUEST on
    episode.cross_ref_id = request.cross_ref_id      JOIN product ON episode.episode_id =
    product.episode_id LEFT JOIN product_sub_type ON product.prod_sub_type_id =
    product_sub_type.prod_sub_type_id WHERE (       episode.department_id = 2     and
    product.status = 'I'        ) ORDER BY episode.date_required
    
    Plan hash value: 1286671272
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |                |       |       |       |  6983 (100)|           |
    |   1 |  SORT ORDER BY          |                | 33823 |  1948K|  4520K|  6983     (1)| 00:02:14 |
    |   2 |   NESTED LOOPS OUTER     |                | 33823 |  1948K|       |  6534     (1)| 00:02:05 |
    |*  3 |    HASH JOIN OUTER     |                | 33823 |  1849K|  1752K|  6532     (1)| 00:02:05 |
    |*  4 |     HASH JOIN          |                | 33823 |  1354K|       |   524     (2)| 00:00:11 |
    |*  5 |      TABLE ACCESS FULL     | PRODUCT           | 33333 |   423K|       |    62     (2)| 00:00:02 |
    |*  6 |      TABLE ACCESS FULL     | EPISODE           |   299K|  8199K|       |   460     (1)| 00:00:09 |
    |   7 |     INDEX FAST FULL SCAN| IX4_REQUEST           |  3999K|    57M|       |  2396     (1)| 00:00:46 |
    |*  8 |    INDEX UNIQUE SCAN     | PK_PRODUCT_SUB_TYPE |     1 |     3 |       |     0     (0)|           |
    -------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID")
       4 - access("PRODUCT"."EPISODE_ID"="EPISODE"."EPISODE_ID")
       5 - filter("PRODUCT"."STATUS"='I')
       6 - filter("EPISODE"."DEPARTMENT_ID"=2)
       8 - access("PRODUCT"."PROD_SUB_TYPE_ID"="PRODUCT_SUB_TYPE"."PROD_SUB_TYPE_ID")
    
    34 rows selected.
    Modified Query:
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID     2m7573c1pgc61, child number 0
    -------------------------------------
    SELECT        episode.episode_id , episode.cross_ref_id , episode.date_required ,
    product.number_required ,     request.site_id FROM episode LEFT JOIN REQUEST on
    episode.cross_ref_id = request.cross_ref_id      JOIN product ON episode.episode_id =
    product.episode_id WHERE (        episode.department_id = 2      and product.status =
    'I'       ) ORDER BY episode.date_required
    
    Plan hash value: 4250628916
    
    ----------------------------------------------------------------------------------------------
    | Id  | Operation            | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |          |          |          |          |     6942 (100)|          |
    |   1 |  SORT ORDER BY            |          | 33823 |     1750K|     4280K|     6942   (1)| 00:02:13 |
    |*  2 |   HASH JOIN OUTER      |          | 33823 |     1750K|     1656K|     6528   (1)| 00:02:05 |
    |*  3 |    HASH JOIN            |          | 33823 |     1255K|          |      524   (2)| 00:00:11 |
    |*  4 |     TABLE ACCESS FULL  | PRODUCT     | 33333 |      325K|          |       62   (2)| 00:00:02 |
    |*  5 |     TABLE ACCESS FULL  | EPISODE     |      299K|     8199K|          |      460   (1)| 00:00:09 |
    |   6 |    INDEX FAST FULL SCAN| IX4_REQUEST |     3999K|       57M|          |     2396   (1)| 00:00:46 |
    ----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID")
       3 - access("PRODUCT"."EPISODE_ID"="EPISODE"."EPISODE_ID")
       4 - filter("PRODUCT"."STATUS"='I')
       5 - filter("EPISODE"."DEPARTMENT_ID"=2)
    
    
    30 rows selected.
    It looks like it is a bug in 10.2.04 as well as 10.2.0.5.
    But I must admit this is (most probably) first time that I am noticing that a non-patched version (in which I have encountered many other issues that were fixed in subsequent patches) is working as expected but the patched version is not. It is possible that the optimizer team managed to introduce a new bug while fixing some other issue(s).
  • 4. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    rp0428 Guru
    Currently Being Moderated
    >
    So why is the Optimizer not choosing the Index Fast Full Scan, when it is obvious that it is so much cheaper than a Full Table Scan?
    >
    What is your basis for this statement? There is nothing in your post for the first query that shows any information for an Index Fast Full Scan so how can it be 'obvious'? For the second query the trace shows an IFFS is cheaper and Oracle used it.
    >
    And why does removing a table change the way the Optimizer works
    >
    Other than a bug I wonder if the fact that the 'cross_ref_id' columns both being nullable for 'episode' and 'request' have anything to do with it. The 'product_sub_type' table still has to be joined to the 'product' table but the possible nulls could drop rows and make them unavailable for that join.

    Obviously the rows being equi-joined would ultimately drop them anyway I'm just wondering if that somehow changed what Oracle looked at.
  • 5. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi John,

    thanks for a very interesting question and a detailed test case script.

    I think that your problem is that you declare columns non-nullable via a check constraint. As you understand, the optimizer can only use an INDEX FAST FULL SCAN instead of a FULL TABLE SCAN if the indexed column(s) is(are) NOT NULL, or if there is a predicate that assures that the query shouldn't return NULLs (e.g. col1=col1 would do just fine).

    Your query doesn't have any filter predicates on REQUEST table, so INDEX FAST FULL SCAN can only be used if there is an appropriate join predicate; e.g. if you rewrite your query replacing ANSI syntax with the traditional Oracle join, then you do get the desired plan.

    Try ALTER TABLE REQUEST MODIFY site_id NOT NULL and see if the plan changes. Worked for me (11.2.0.1).

    Best regards,
    Nikolay
  • 6. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    763581 Newbie
    Currently Being Moderated
    rp048 - I said that the IFSS is cheaper than the Full Table Scan because its cost is lower, as reported by Oracle itself. From both execution plans as posted:

    Full Table Scan cost 28,722
    | Id  | Operation         | Name         | Rows  | Bytes |Tmp| Cost (%CPU)| Time     |
    |   7 | TABLE ACCESS FULL | REQUEST      |  3989K|    57M|   | 28772   (1)| 00:05:46 |
    Index Fast Full Scan cost 3,976
    |   6 |INDEX FAST FULL SCAN| IX4_REQUEST |  3989K|    57M|   |  3976   (1)| 00:00:48 |
    So the IFFS at a cost of 3,976 is much cheaper than the Full Table Scan cost at 28,722. That is why I said it was so obvious that it was cheaper. And the query only wants 2 columns from REQUEST - CROSS_REF_ID and SITE_ID - and both are in the index. Hence the second query's execution plan can get everything it needs from the index alone.

    John
  • 7. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    763581 Newbie
    Currently Being Moderated
    Nikolay,

    Thanks for the suggestion about NULL values, but I don't think that is the issue here. The only difference between the two queries is that the second one does not join to "product_sub_type". That is the only difference. So any NULL constraints or lack of are exactly the same when both queries are being executed. So if the index is valid for the second query then it should be equally valid for the first query. And the missing table of "product_sub_type" does not join to the "request" table, which is the one where the index should be used. So I don't see how this join could be affecting the choice of an index on "request".

    I don't know what is going on, hence my question. I'd be interested to find out, as the use of the index is clearly a lower cost (under 4,000 compared to 28,000), and it would be nice if I could get the real SQL from the application to use it instead of a full table scan. Hinting is not really an option, but I did also try the "index_ffs" hint with no success. But then I know that one hint on its own is never enough, and I did not want to over complicate any solution. I would prefer it if I could get Oracle to "naturally" choose to use the index, as it does for the second query.

    Thanks,
    John
  • 8. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Paul Horth Expert
    Currently Being Moderated
    Cost is meaningless when you are comparing two different queries. It is only an internal measure used by Oracle when comparing its own plans
    for a single query.

    See Fallacies of the Cost Based Optimizer at http://www.centrexcc.com/papers.html
  • 9. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Paul,

    Cost of a complex plan is usually of little use. But cost of a specific plan operation is a very meaningful (and very useful) quantity.

    Best regards,
    Nikolay
  • 10. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi John,

    I understand your skepticism, but did you actually try it? Please redefining all columns of REQUESTS for which you used NOT NULL check constraint as NOT NULL via
    ALTER TABLE REQUEST MODIFY <column_name> NOT NULL
    and see if the plan changes.

    Best regards,
    Nikolay
  • 11. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    763581 Newbie
    Currently Being Moderated
    Nikolay,

    I'm afraid that I'm not going to be able to do any changes to "NOT NULL" to the columns in the database. Almost all of the columns in all tables allow NULL values apart from the primary key columns. That's just the way it is - not my database design, usual excuses apply, etc. So while you may be right that changing columns to "NOT NULL" will affect the Optimizer choices, and will result in it using the index on REQUEST, it is just not an option open to me in this case I'm afraid.

    I am also curious as to why the index is good enough in the modified query when the columns are the same, so the same NULL issues apply in both cases. Why does the Optimizer cost the Index Fast Full Scan in the second query, as seen in the 10053 trace file, but does not calculate the cost of the IFFS in the first query?

    As you say, it may be something to do with NULL conditions, but something makes me think that it is something else. Mainly because the columns are the same in both cases, so any NULL conditions are the same in both. I don't have any hard evidence either way.

    My current thought is that it is because the join to the extra table in the first query is an OUTER LEFT join, and that maybe the presence of 2 OUTER joins is stopping the Optimizer costing certain access paths. In the modified query there is only the one OUTER join, so maybe the Optimizer knows that certain special conditions are met and it can cost up the Index Fast Full Scan. But with 2 or more OUTER joins, the Optimizer may believe that those "special conditions" are not met and so does not cost the Index Scan.

    I've quickly tried a few variations and any time there is only one OUTER join in the query it does cost and use the Index Fast Full Scan. Also in the original query from the application, which involved more OUTER joins to other tables, if I change the join to REQUEST from a LEFT JOIN to a JOIN, then it does an Index Fast Full Scan on it. So I do think it is something to do with the use of OUTER joins between more than one table in the query.

    Thanks,
    John
  • 12. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Dom Brooks Guru
    Currently Being Moderated
    I mentioned above that this is a probably bug related to ANSI SQL and query transformation.

    As suggested/questioned in my original reply:
    1. if you use a no_query_transformation then you should find that you get the index usage (albeit not in the plan you expect)
    2. if you use traditional Oracle syntax then you shouldn't have the same problem.
  • 13. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    763581 Newbie
    Currently Being Moderated
    Dom,

    You are right, which is where I was now heading - the OUTER joins are the issue. You have identified that it is the rewrite by Oracle to its native syntax that is not perfect for ANSI syntax joins.

    The original query had a total execution cost of 35,357 due to the full table scan on REQUEST of 28,772.

    As you suggested Dom, I rewrote the original query to use Oracle native syntax and got the index fast full scan. The cost has now dropped to 10,561 (15,000 less), because the REQUEST table access has now dropped to 3,976 (again, about 15,000 less).

    Here is the rewritten original query
    SELECT 
        episode.episode_id , episode.cross_ref_id , episode.date_required , 
        product.number_required , 
        request.site_id 
    FROM episode , REQUEST , product , product_sub_type 
    WHERE (
            episode.department_id = 2
        and product.status = 'I'
          ) 
    and episode.cross_ref_id = request.cross_ref_id (+)
    and episode.episode_id = product.episode_id 
    and product.prod_sub_type_id = product_sub_type.prod_sub_type_id (+)
    ORDER BY episode.date_required
    ;
    Its execution plan is:
    SQL_ID  4x8j6fdbth06w, child number 0
    -------------------------------------
    SELECT     episode.episode_id , episode.cross_ref_id , episode.date_required ,
    product.number_required ,     request.site_id FROM episode , REQUEST , product ,
    product_sub_type WHERE (         episode.department_id = 2     and product.status = 'I'
    ) and episode.cross_ref_id = request.cross_ref_id (+) and episode.episode_id =
    product.episode_id and product.prod_sub_type_id = product_sub_type.prod_sub_type_id (+) ORDER
    BY episode.date_required
    
    Plan hash value: 1286671272
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation               | Name                | Rows  | Bytes |TempSpc|Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |                     |       |       |       |10561 (100)|          |
    |   1 |  SORT ORDER BY          |                     | 33333 |  1920K|  2232K|10561   (1)| 00:02:07 |
    |   2 |   NESTED LOOPS OUTER    |                     | 33333 |  1920K|       |10083   (1)| 00:02:01 |
    |*  3 |    HASH JOIN OUTER      |                     | 33333 |  1822K|  1728K|10082   (1)| 00:02:01 |
    |*  4 |     HASH JOIN           |                     | 33333 |  1334K|       |  894   (1)| 00:00:11 |
    |*  5 |      TABLE ACCESS FULL  | PRODUCT             | 33333 |   423K|       |  103   (1)| 00:00:02 |
    |*  6 |      TABLE ACCESS FULL  | EPISODE             |   299K|  8198K|       |  788   (1)| 00:00:10 |
    |   7 |     INDEX FAST FULL SCAN| IX4_REQUEST         |  3989K|    57M|       | 3976   (1)| 00:00:48 |
    |*  8 |    INDEX UNIQUE SCAN    | PK_PRODUCT_SUB_TYPE |     1 |     3 |       |    0   (0)|          |
    -------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       3 - access("EPISODE"."CROSS_REF_ID"="REQUEST"."CROSS_REF_ID")
       4 - access("EPISODE"."EPISODE_ID"="PRODUCT"."EPISODE_ID")
       5 - filter("PRODUCT"."STATUS"='I')
       6 - filter("EPISODE"."DEPARTMENT_ID"=2)
       8 - access("PRODUCT"."PROD_SUB_TYPE_ID"="PRODUCT_SUB_TYPE"."PROD_SUB_TYPE_ID")
    So the good news is that I now know what is going on.

    The bad news is that I am still stuck on where to go next. The developers prefer the ANSI join syntax as it is clearer and more readable than the Oracle native one. And I tend to agree with them on that. Yet this query is definitely under-performing and can be significantly improved. It is not urgent at all, but I just know that at some point in the future this will become an issue.

    Likewise, improving its performance will reduce the total I/O load on the system, as a full table scan on every execution on a 4 million row table will take some time. So by making it go faster I can make any other I/O intensive queries go faster too because of the reduced workload.

    I'll keep trying different query rewrites to see what I can achieve. I'll try pushing some of the other tables into inline views, to keep their joins separate from the one to this large REQUEST table. However, you cannot mix ANSI and Oracle native join syntax together in the same query (I got an error when I tried that), so any rewrite will have to keep using the ANSI syntax.

    Thanks again,
    John Brady
    Database Performance Blog
  • 14. Re: Why Optimizer ignore Index Fast Full Scan when much lower cost?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi John,
    John Brady - UK wrote:
    Nikolay,

    I'm afraid that I'm not going to be able to do any changes to "NOT NULL" to the columns in the database. Almost all of the columns in all tables allow NULL values apart from the primary key columns.
    I'm not talking about declaring all columns NOT NULL, I was referring to the two columns which are already NOT NULL (at least in your test case):
    constraint nn_request_department check (department_id is not null),
    constraint nn_request_site_id check (site_id is not null)
    That's just the way it is - not my database design, usual excuses apply, etc. So while you may be right that changing columns to "NOT NULL" will affect the Optimizer choices, and will result in it using the index on REQUEST, it is just not an option open to me in this case I'm afraid.
    >
    I am also curious as to why the index is good enough in the modified query when the columns are the same, so the same NULL issues apply in both cases. Why does the Optimizer cost the Index Fast Full Scan in the second query, as seen in the 10053 trace file, but does not calculate the cost of the IFFS in the first query?
    In order for IFFS to be chosen as an access path, it's not enough that it have a low cost. Another condition must be met: this access path should return same results as a table full scan. Generally, that's not the case because an index doesn't contain rowids for rows where the index key is NULL. So when you are doing something like this:
    SELECT X FROM TAB1
    where TAB1 contains a bunch of other columns, and X is a nullable indexed column, the optimizer also wouldn't bother considering INDEX FAST FULL SCAN. Because no matter what performance, it would return (potentially) wrong results.

    Note if you change this query to (seemingly) identical one:
    SELECT X FROM TAB1 WHERE X=X
    then IFFS becomes possible, because NULL=NULL is not evaluated to TRUE (it's NULL). For the same reason, if TAB1 is joined to another table via an inner equijoin, Oracle knows that NULLs are eliminated by the join condition, so once again, IFFS becomes possible.

    In case of an outer join it becomes a bit trickier, but I think you get the general idea.

    Now about the mystery of the modified query -- I don't know what exactly is going on inside the optimizer to make it work, but I am pretty sure that it has to do with query transforms and join predicates. In one case the optimizer analyzes cardinalities and costs of accessing the table as an isolated table without any predicates. In this case, obviously, IFFS is not an option. In the other case there are join predicates that make IFFS possible.

    If I get a chance to spend a day looking at 10053 traces I might be able to figure out the details, but I'm not sure if I'd be able to do that any time soon.


    >
    As you say, it may be something to do with NULL conditions, but something makes me think that it is something else. Mainly because the columns are the same in both cases, so any NULL conditions are the same in both. I don't have any hard evidence either way.

    My current thought is that it is because the join to the extra table in the first query is an OUTER LEFT join, and that maybe the presence of 2 OUTER joins is stopping the Optimizer costing certain access paths. In the modified query there is only the one OUTER join, so maybe the Optimizer knows that certain special conditions are met and it can cost up the Index Fast Full Scan. But with 2 or more OUTER joins, the Optimizer may believe that those "special conditions" are not met and so does not cost the Index Scan.

    I've quickly tried a few variations and any time there is only one OUTER join in the query it does cost and use the Index Fast Full Scan. Also in the original query from the application, which involved more OUTER joins to other tables, if I change the join to REQUEST from a LEFT JOIN to a JOIN, then it does an Index Fast Full Scan on it. So I do think it is something to do with the use of OUTER joins between more than one table in the query.
    I think that OUTER JOINs limit the ability of the optimizer to do query transformation, such as transitive closure, thus making it harder to see that it's safe to ignore NULLs and replace a FTS with an IFFS. If the index key is declared NOT NULL, then this isn't an issue anymore, and one doesn't have to rely on query transforms which may or may not succeed, to get an IFFS instead of a FTS.

    Best regards,
    Nikolay
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points