The unexpected change of the ACCESS predicate

  • From: Dion Cho <ukja.dion@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 20 Jul 2010 11:20:13 +0900

To make a long story short, let me show a very simple test case.

1. Create tables and indices

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


> create index t1_n1 on t1(c1);


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



2. The condition of { between :b1 and :b2 } is used as the ACCESS predicate.

> var b1 number;

var b2 number;

var b3 number;


> explain plan for

select *

from t1

where c1 between :b1 and :b2

  and c1 > :b3

 ;


--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |     1 |    39 |     0   (0)|
00:00:01 |

|*  1 |  FILTER                      |       |       |       |            |
         |

|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    39 |     0   (0)|
00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     0   (0)|
00:00:01 |

--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   1 - filter(TO_NUMBER(:B2)>TO_NUMBER(:B3) AND

              TO_NUMBER(:B1)<=TO_NUMBER(:B2))

   3 - *access("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2)) <-- Here!*

       filter("C1">TO_NUMBER(:B3))



3. But with additional IN operation(which has no relation with pre-existent
BETWEEN operation), the { c1 > :b3 and c1 <= :b2 } is used as the ACCESS
predicate.

explain plan for

select *

from t1

where c1 between :b1 and :b2

  and c2 in (1, 2, 3)

   and c1 > :b3

 ;


--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |     1 |    39 |     0   (0)|
00:00:01 |

|*  1 |  FILTER                      |       |       |       |            |
         |

|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    39 |     0   (0)|
00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     0   (0)|
00:00:01 |

--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   1 - filter(TO_NUMBER(:B2)>TO_NUMBER(:B3) AND

              TO_NUMBER(:B1)<=TO_NUMBER(:B2))

   2 - filter("C2"=1 OR "C2"=2 OR "C2"=3)

   3 - *access("C1">TO_NUMBER(:B3) AND "C1"<=TO_NUMBER(:B2))  <-- Here*

       filter("C1">=TO_NUMBER(:B1))


This is a very unexpected change whose reason I couldn't identify. I've
dumped 10053 trace for the additional info but got nothing meaningful.

I'm not sure whether this is a designed feature or just an unexpected bug.
The only certain thing is that this has caused unexpected performance
degradation while the developers change the SQL code. :(

Can anyone share some knowledges and experiences and comments on this?

Other related posts:

  • » The unexpected change of the ACCESS predicate - Dion Cho