Oracle Scratchpad

August 17, 2010

FBI Bug

Filed under: CBO,Indexing,Troubleshooting — Jonathan Lewis @ 5:42 pm BST Aug 17,2010

Here’s an odd little bug (I think) in the optimizer that showed itself in a thread on the OTN database forum some time ago. The optimizer can choose an index which is NOT the cheapest index for satisfying a query against a single table. Here’s the demo – which I’ve run against 11.1.0.6 using an 8KB block size, ASSM and system allocated extent sizes:


rem
rem     Script:         fbi_stats.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2010
rem

create table t1(
	DATE1              DATE,
	STRING1            VARCHAR2(40),
	NUM1               NUMBER,
	NUM2               NUMBER,
	NUM3               NUMBER,
	NUM4               NUMBER,
	NUM5               NUMBER,
	STRING2            VARCHAR2(3),
	NUM6               NUMBER,
	STRING3            VARCHAR2(240),
	STRING4            VARCHAR2(240),
	STRING5            VARCHAR2(240),
	STRING6            VARCHAR2(240),
	STRING7            VARCHAR2(240),
	STRING8            VARCHAR2(240),
	STRING9            VARCHAR2(10)
);

insert into t1(
	date1, num1, num3, num6, string9
)
select
	sysdate + dbms_random.value(-180, +180),
	trunc(dbms_random.value(0,10)),
	trunc(dbms_random.value(0,10)),
	trunc(dbms_random.value(0,10)),
	dbms_random.string('U',6)
from dual
connect by
	rownum  comment to avoid WordPress format issue
;

commit;

CREATE INDEX t1_i1 ON t1 (TRUNC(DATE1), NUM3);
CREATE INDEX t1_i2 ON t1 (NUM3, NUM1, TRUNC(DATE1));

begin
	dbms_stats.gather_table_stats(
		ownname          => user,
		tabname          => 'T1',
		estimate_percent => 100,
		block_sample     => true,
		method_opt       => 'for all columns size 1',
		cascade          => true
	);
end;
/

explain plan for
SELECT
	NVL(SUM(num6),0) num6_SUM
FROM t1
WHERE
	1 = 1
and	num3 = :b1
AND	TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
and	string9 = :b4
;

select * from table(dbms_xplan.display)
;

explain plan for
SELECT
	/*+ index(t1 t1_i1) */
	NVL(SUM(num6),0) num6_SUM
FROM t1
WHERE
	1 = 1
AND	num3 = :b1
AND	TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
and	string9 = :b4
;

select * from table(dbms_xplan.display)
;

The mixture of upper and lower case combined with the presence of redundant columns is because I copied most of the code from the original thread and kept the table and index structures – I don’t usually write messy code.

You’ll notice that I have a query that could use either of the indexes to get to the table, but index t1_i2 starts with a column that holds only 10 distinct values and has an extra column “in the way” so the nature of the query means Oracle will have to scan at least 10% of this index to satisfy the query. Here are the two execution plans (note that I’ve had to change the normal comparison operators in the predicate section to “.le.” and “.ge.” respectively for “less than or equal to” and “greater than or equal to” to work around a formatting problem with WordPress).

First the unhinted version of the query – which uses the t1_i2 index. Note the cost, of which the index range scan cost (line 4) is a significant fraction:

-----------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    21 |    33 |
|   1 |  SORT AGGREGATE               |       |     1 |    21 |       |
|*  2 |   FILTER                      |       |       |       |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |    33 |
|*  4 |     INDEX RANGE SCAN          | T1_I2 |    18 |       |    15 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-TO_NUMBER(:B2).le. SYSDATE@!-TO_NUMBER(:B3))
   3 - filter("STRING9"=:B4)
   4 - access("NUM3"=TO_NUMBER(:B1) AND
              TRUNC(INTERNAL_FUNCTION("DATE1")).ge. SYSDATE@!-TO_NUMBER(:B2) AND
              TRUNC(INTERNAL_FUNCTION("DATE1")).le. SYSDATE@!-TO_NUMBER(:B3))
       filter(TRUNC(INTERNAL_FUNCTION("DATE1")).ge. SYSDATE@!-TO_NUMBER(:B2)
              AND TRUNC(INTERNAL_FUNCTION("DATE1")).le. SYSDATE@!-TO_NUMBER(:B3))

Now the version hinted to use t1_i1. Again note the cost – and that the difference is due only to the lower cost of the index range scan.

-----------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    21 |    20 |
|   1 |  SORT AGGREGATE               |       |     1 |    21 |       |
|*  2 |   FILTER                      |       |       |       |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |    20 |
|*  4 |     INDEX RANGE SCAN          | T1_I1 |    18 |       |     2 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-TO_NUMBER(:B2).le. SYSDATE@!-TO_NUMBER(:B3))
   3 - filter("STRING9"=:B4)
   4 - access(TRUNC(INTERNAL_FUNCTION("DATE1")).ge. SYSDATE@!-TO_NUMBER(:B2)
              AND "NUM3"=TO_NUMBER(:B1) AND TRUNC(INTERNAL_FUNCTION("DATE1")).le.
              SYSDATE@!-TO_NUMBER(:B3))
       filter("NUM3"=TO_NUMBER(:B1))

Left to its own devices, the optimizer chose the more expensive index – even though in both cases the calculations show that the number of visits to the table to collect the result set is the same.

I haven’t really worked out exactly what causes the optimizer to do the wrong thing but it seems, from a couple of other tests, that if you have two indexes that could be used to satisfy a query with the same table access cost but one of the indexes starts with a range scan on a “virtual column” then the other index will be chosen, even if it happens to be the more expensive index to use.

Update (31st Oct 2010):  I did a little extra work on this issue a few weeks later (see comments – especially #5 – and pingback below), but forgot to link forward to the new article. My original conclusions were wrong; for more details see: https://jonathanlewis.wordpress.com/2010/09/07/cbo-surprise-3/

16 Comments »

  1. Bug 9309281 is another one, which shows an fbi plan broke between 10gR1 and 10gR2. That and Bug 9155467 (and some other wrong results bugs) suggest trying alternatives (testing only, of course):

    set “_disable_function_based_index”=true;
    SET “_replace_virtual_columns” = FALSE;
    Set optimizer_features_enable < 10.1.0.4

    "Fixed in version 12.1" gotta love it.

    Comment by joel garry — August 17, 2010 @ 11:58 pm BST Aug 17,2010 | Reply

    • Joel,

      I’d like to say thanks for the information – but that has to be some of the most unwelcome information you could supply! Virtual columns and associated effects are probably the most useful features of 11g. (Maybe approximate NDV comes equal first.)

      Comment by Jonathan Lewis — August 18, 2010 @ 9:20 pm BST Aug 18,2010 | Reply

      • I’d like to say you’re welcome :-)

        Yes, it is very useful, and of course those “fixed in” notations are misleading, what with backports and screaming customers and all – wrong results must be taken more seriously than “mere” optimizer issues.

        The development environment I work in has had this functionality, as part of local “db-blind” dictionary extensions, for many years, and it is used all over the place, very useful. Of course, not being part of the db engine, it is easy to mess up when trying to manipulate data through sql, as in this implementation these virtual columns look like regular columns, so you update them, and that gets overwritten by the app. I look forward with interest as to how the vendor will deal with integrating the Oracle functionality, if they do, it would certainly help if the db were aware and these things could be indexed.

        Comment by joel garry — August 20, 2010 @ 9:36 pm BST Aug 20,2010 | Reply

  2. I wonder if this could be a bug that is caused by not having the ability to peek at the bind variables – maybe that causes an unexpected code path? I performed a test on 11.2.0.1 where I initialized a set of bind variables and used DBMS_XPLAN.DISPLAY_CURSOR to retrieve the execution plan. The expected (lowest cost) plan was retrieved. I then issued:

    ALTER SESSION SET "_optim_peek_user_binds"=FALSE;
    

    Then repeated the test (I first flushed the shared pool, but it probably was not necessary). The second test showed that the higher cost execution plan was selected, while the lower cost index was not selected.

    Test 1:

    SQL> SELECT
      2     NVL(SUM(num6),0) num6_SUM
      3  FROM t1
      4  WHERE
      5     1 = 1
      6  and        num3 = :b1
      7  AND        TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
      8  and        string9 = :b4
      9  ;
    
      NUM6_SUM
    ----------
             0
    
    SQL>
    SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +PEEKED_BINDS'));
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------
    
    SQL_ID  5fj4h0fndhc95, child number 1
    -------------------------------------
    SELECT  NVL(SUM(num6),0) num6_SUM FROM t1 WHERE  1 = 1 and num3 = :b1
    AND TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3 and string9 = :b4
    
    Plan hash value: 4191978214
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |       |       |       |    24 (100)|          |
    |   1 |  SORT AGGREGATE               |       |     1 |    21 |            |          |
    |*  2 |   FILTER                      |       |       |       |            |          |
    |*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |    24   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | T1_I1 |    22 |       |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Peeked Binds (identified by position):
    --------------------------------------
       1 - :B1 (NUMBER): 1
       2 - :B2 (NUMBER): 1
       3 - :B3 (NUMBER): 1
       4 - :B4 (VARCHAR2(30), CSID=178): 'TEST'
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter(SYSDATE@!-:B2<=SYSDATE@!-:B3)
       3 - filter("STRING9"=:B4)
       4 - access("T1"."SYS_NC00017$">=SYSDATE@!-:B2 AND "NUM3"=:B1 AND
                  "T1"."SYS_NC00017$"<=SYSDATE@!-:B3)
           filter("NUM3"=:B1)
    
    Hinted higher cost index:
    SQL_ID  6u4zxmqmja9cx, child number 1
    -------------------------------------
    SELECT  /*+ index(t1 t1_i2) */  NVL(SUM(num6),0) num6_SUM FROM t1 WHERE
     1 = 1 AND num3 = :b1 AND TRUNC(date1) BETWEEN sysdate-:b2 AND
    sysdate-:b3 and string9 = :b4
    
    Plan hash value: 1130895096
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |       |       |       |    38 (100)|          |
    |   1 |  SORT AGGREGATE               |       |     1 |    21 |            |          |
    |*  2 |   FILTER                      |       |       |       |            |          |
    |*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |    38   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | T1_I2 |    22 |       |    15   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Peeked Binds (identified by position):
    --------------------------------------
       1 - :B1 (NUMBER): 1
       2 - :B2 (NUMBER): 1
       3 - :B3 (NUMBER): 1
       4 - :B4 (VARCHAR2(30), CSID=178): 'TEST'
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter(SYSDATE@!-:B2<=SYSDATE@!-:B3)
       3 - filter("STRING9"=:B4)
       4 - access("NUM3"=:B1 AND "T1"."SYS_NC00017$">=SYSDATE@!-:B2 AND
                  "T1"."SYS_NC00017$"<=SYSDATE@!-:B3)
           filter(("T1"."SYS_NC00017$"<=SYSDATE@!-:B3 AND
                  "T1"."SYS_NC00017$">=SYSDATE@!-:B2))
    

    Test 2 (peeking disabled):

    SQL> SELECT
      2     NVL(SUM(num6),0) num6_SUM
      3  FROM t1
      4  WHERE
      5     1 = 1
      6  and        num3 = :b1
      7  AND        TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
      8  and        string9 = :b4
      9  ;
    
      NUM6_SUM
    ----------
             0
    
    SQL>
    SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +PEEKED_BINDS'));
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------
    
    SQL_ID  5fj4h0fndhc95, child number 0
    -------------------------------------
    SELECT  NVL(SUM(num6),0) num6_SUM FROM t1 WHERE  1 = 1 and num3 = :b1
    AND TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3 and string9 = :b4
    
    Plan hash value: 1130895096
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |       |       |       |    33 (100)|          |
    |   1 |  SORT AGGREGATE               |       |     1 |    21 |            |          |
    |*  2 |   FILTER                      |       |       |       |            |          |
    |*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |    33   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | T1_I2 |    18 |       |    15   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter(SYSDATE@!-:B2<=SYSDATE@!-:B3)
       3 - filter("STRING9"=:B4)
       4 - access("NUM3"=:B1 AND "T1"."SYS_NC00017$">=SYSDATE@!-:B2 AND
                  "T1"."SYS_NC00017$"<=SYSDATE@!-:B3)
           filter(("T1"."SYS_NC00017$">=SYSDATE@!-:B2 AND
                  "T1"."SYS_NC00017$"<=SYSDATE@!-:B3))
    

    There appears to be a slight difference in a 10053 trace when bind peeking is permitted.
    Bind peeking permitted:

    ...
    SINGLE TABLE ACCESS PATH 
      Single Table Cardinality Estimation for T1[T1] 
    
     ***** Virtual column  Adjustment ****** 
     Column name       SYS_NC00017$  
     cost_cpu 800.00
     cost_io  179769313486231570000000000000000000000000000000000000
    0000000000000000000000000000000000000000000000000000000000000000
    0000000000000000000000000000000000000000000000000000000000000000
    0000000000000000000000000000000000000000000000000000000000000000
    000000000000000000000000000000000000000000000000000000000000000.00
     ***** End virtual column  Adjustment ****** 
    ...
    

    Bind peeking not permitted:

    SINGLE TABLE ACCESS PATH 
      Single Table Cardinality Estimation for T1[T1] 
      ColGroup (#1, Index) T1_I2
        Col#: 3 5 17    CorStregth: -1.00
      ColGroup (#2, Index) T1_I1
        Col#: 5 17    CorStregth: 1.00
      ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: 
     ***** Virtual column  Adjustment ****** 
     Column name       SYS_NC00017$  
     cost_cpu 800.00
     cost_io  17976931348623157000000000000000000000000000000000000000
    000000000000000000000000000000000000000000000000000000000000000000
    000000000000000000000000000000000000000000000000000000000000000000
    000000000000000000000000000000000000000000000000000000000000000000
    0000000000000000000000000000000000000000000000000000000.00
     ***** End virtual column  Adjustment ******
    

    I wonder what impact “CorStregth: -1.00” and “CorStregth: 1.00” have on which index is selected?

    Comment by Charles Hooper — August 18, 2010 @ 1:55 am BST Aug 18,2010 | Reply

    • Charles,

      Thanks for doing that work. I’d been resisting the urge to look at the problem closely – and you’ve just made it harder with those extracts from the 10053 (interesting cost figures !). I think your suggestion about non-peeking is a much better idea than mine. (My quick tests were just “very similar” variations on the same sort of theme, so probably introduced some confirmation bias.)

      Big Warning to anyone who has disabled bind-peeking, though: you may get second-best use of function-based indexes and should check any important cases where there’s a choice of indexes for critical queries.

      Comment by Jonathan Lewis — August 18, 2010 @ 9:23 pm BST Aug 18,2010 | Reply

  3. Just been in a session with Tom Kyte all day and the optimizer not being able to peek at bind variables was one of the “issues” that he demonstrated. He also says that we should not rely on explain plan any longer but should determine the actual execution plan (by using DBMS_XPLAN.DISPLAY_CURSOR) after the query has run as Charles did with his example.

    Comment by John Seaman — August 18, 2010 @ 7:06 am BST Aug 18,2010 | Reply

    • John,

      Thanks for the comment. I assume that Tom was reminding people that “explain plan” doesn’t know the type of the bind variables and doesn’t have any values to peek.

      In this case, though, the problem isn’t so much the fact that we get the wrong plan because of those failings, it’s that with (nominally) a self-consistent level of information the optimizer does the wrong thing.

      Comment by Jonathan Lewis — August 18, 2010 @ 9:26 pm BST Aug 18,2010 | Reply

      • Tom was essentailly saying that the plan given by explain plan may be incorrect where bind variables are being used if the bind variables are of a different type to the columns to which they are compared.

        I wonder if you tried the same test without using bind variables if you would get the same result. From the tests Charles ran it looks as if it is the bind variables that cause the higher cost path to be chosen and that seems to be what Johnwuwuwu is saying.

        I would post the links to Tom’s presentation but it doesn’t seem to be up yet (assuming it will be).

        Comment by John Seaman — August 20, 2010 @ 5:43 am BST Aug 20,2010 | Reply

  4. […] The comments for this Jonathan Lewis blog post tipped me off to the new functions. They led to Rob van Wijk’s detailed post on the new display_cursor function, as well as another Jonathan Lewis post on the function. […]

    Pingback by Explaining it better: dbms_xplan « Database Bulletin — August 18, 2010 @ 11:04 am BST Aug 18,2010 | Reply

  5. When using bind variables we know that some costs are unreliable, we have to use default values. In particular, for range predicates such as those that match the functional index, there is a higher likelyhood of error. For this reason the optimizer will prefer an index that has equality predicates, even if the cost is higher.

    If bind peeking is used (this is not possible with explain plan) then we will be more accurate in the selectivity calculation and so do not have to disregard a lower cost index in this way.

    Comment by johnwuwuwu — August 19, 2010 @ 10:03 am BST Aug 19,2010 | Reply

    • johnwuwuwu,

      Thanks for that information – even though it is rather worrying and means I will have to look at this example a lot more closely because your comment raises lots of questions:

      Is this just function-based indexes, or all indexes
      Is it purely heuristic, or is there some arithmetic bias as well
      Does this apply to all cases of comparison with “unknown” such as:

      • colX between plsql_function(const1) and plsql_function(const2)
      • colx between (non-correlated scalar subquery 1) and (non-correlated scalar subquery 2)
      • colx between sys_context() and sys_context()
      • t2.cox between t1.cola and t1.colb

      If it is more generic than function-based indexes, I’m puzzled that I haven’t noticed it before. I guess this will give me something to do on my next long-haul flight.

      Comment by Jonathan Lewis — August 20, 2010 @ 4:35 am BST Aug 20,2010 | Reply

  6. I assume the optimizer does not ‘prefer’ to use the index created on a column which is used for a RANGE OF values in the predicate because it is unsure how the range would be ‘BIG’ .. so if there is an index on equality condition , it will use that index

    I meant to say the index would be preferred which is created on the columns with ‘=’ operators in the predicates than the the columns with the Range operator

    Comment by Aruna Erusadla — August 21, 2010 @ 1:14 pm BST Aug 21,2010 | Reply

    • Aruna,

      Your argument is rational, but ought to be backed up by arithmetic.

      The optimizer can make a reasonable estimate of the fraction of data that would be returned by “columnX = ‘unknown constant'” because it can work on the basis of “number of distinct values for columnX”.

      For “columnD between pair of unknown constants” Oracle uses a guess of 0.25%. The problem is this – when should Oracle be INCONSISTENT in its arithmetic and ignore the number it usually uses for the guess.

      Let’s pretend that columnX has only one known value – should Oracle take the index that starts with columnX – which means it has to scan the whole index – or should it take the index that starts with columnD because in the worst case it will have to scan the whole index but it may scan much less.

      Clearly, in this extreme case, it is arguably more sensible to use the index that starts with the column using the range-based predicate.

      So – one distinct value is a special case where your rational approach should be ignored. How many distinct values should Oracle allow before it decides that it’s no longer looking at a special case ? (This is the type of question the designers and developers have to ask themselves when trying to create a cost-based optimiser.)

      Comment by Jonathan Lewis — August 21, 2010 @ 8:14 pm BST Aug 21,2010 | Reply

  7. I tested the above scenorio in my test db. Both queries cost have same.

    My test database running on 11.1.0.7 (sun solaris : 64 bit).

    Database details:
    *********************
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size                        integer     2048
    optimizer_mode                       string      ALL_ROWS
    db_file_multiblock_read_count        integer     32
    compatible                           string      11.1.0
    
    
    Tablespace details:
    *********************
    
    
    SQL> select TABLESPACE_NAME,ALLOCATION_TYPE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from  dba_tablespaces;
    
    TABLESPACE_NAME                ALLOCATIO EXTENT_MAN SEGMEN
    ------------------------------ --------- ---------- ------
    TABLE001                       USER      DICTIONARY MANUAL
    
    
    
    
    SQL> create table t1(  
      2      DATE1              DATE,  
      3      STRING1            VARCHAR2(40),  
      4      NUM1               NUMBER,  
      5      NUM2               NUMBER,  
      6      NUM3               NUMBER,  
      7      NUM4               NUMBER,  
      8      NUM5               NUMBER,  
      9      STRING2            VARCHAR2(3),  
     10      NUM6               NUMBER,  
     11      STRING3            VARCHAR2(240),  
     12      STRING4            VARCHAR2(240),  
     13      STRING5            VARCHAR2(240),  
     14      STRING6            VARCHAR2(240),  
     15      STRING7            VARCHAR2(240),  
     16      STRING8            VARCHAR2(240),  
     17      STRING9            VARCHAR2(10)  
     18  );  
    
    Table created.
    
    SQL> insert into t1(  
      2      date1, num1, num3, num6, string9  
      3  )  
      4  select  
      5      sysdate + dbms_random.value(-180, +180),  
      6      trunc(dbms_random.value(0,10)),  
      7      trunc(dbms_random.value(0,10)),  
      8      trunc(dbms_random.value(0,10)),  
      9      dbms_random.string('U',6)  
     10  from dual  
     11  connect by  
     12      rownum  commit;
    
    Commit complete.
    
    SQL> 
    SQL> CREATE INDEX t1_i1 ON t1 (TRUNC(DATE1), NUM3);  
    
    Index created.
    
    SQL> 
    SQL> CREATE INDEX t1_i2 ON t1 (NUM3, NUM1, TRUNC(DATE1));  
    
    Index created.
    
    
    
    SQL> begin  
      2      dbms_stats.gather_table_stats(  
      3          ownname      => 'TEST',  
      4          tabname      =>'T1',  
      5          estimate_percent => 100,  
      6          block_sample     => true,  
      7          method_opt   => 'for all columns size 1',  
      8          cascade      => true  
      9      );  
     10  end;  
     11  /  
    
    PL/SQL procedure successfully completed.
    
    SQL> explain plan for  
      2  SELECT  
      3      NVL(SUM(num6),0) num6_SUM  
      4  FROM t1  
      5  WHERE  
      6      1 = 1  
      7  and num3 = :b1  
      8  AND TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3  
      9  and string9 = :b4  
     10  ;  
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display)  ;  
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 683166071
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |       |     1 |    21 |     1   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE               |       |     1 |    21 |            |          |
    |*  2 |   FILTER                      |       |       |       |            |          |
    |*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |     1   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | T1_I2 |    18 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(SYSDATE@!-TO_NUMBER(:B2)=SYSDATE@!-TO_NUMBER(:B2) AND
                  TRUNC(INTERNAL_FUNCTION("DATE1"))=SYSDATE@!-TO_NUMBER(:B2) AND
                  TRUNC(INTERNAL_FUNCTION("DATE1")) explain plan for  
      2  SELECT  
      3      /*+ index(t1 t1_i1) */  
      4      NVL(SUM(num6),0) num6_SUM  
      5  FROM t1  
      6  WHERE  
      7      1 = 1  
      8  AND num3 = :b1  
      9  AND TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3  
     10  and string9 = :b4  
     11  ;  
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display)  ;
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 2267170846
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |       |     1 |    21 |     1   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE               |       |     1 |    21 |            |          |
    |*  2 |   FILTER                      |       |       |       |            |          |
    |*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |     1   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN          | T1_I1 |    18 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(SYSDATE@!-TO_NUMBER(:B2)=SYSDATE@!-TO_NUMBER(:B2) AND
                  "NUM3"=TO_NUMBER(:B1) AND TRUNC(INTERNAL_FUNCTION("DATE1"))<=SYSDATE@!-TO_NUMBE
                  R(:B3))
           filter("NUM3"=TO_NUMBER(:B1))
    
    21 rows selected.
    

    Thanks Lewis.

    Regards
    Rajabaskar Thangaraj

    Comment by Rajabaskar Thangaraj — September 15, 2010 @ 1:20 pm BST Sep 15,2010 | Reply

  8. Plan from initial testcase is OK awhen event 38068 is set by example at 70 or 100

    alter session set events ‘38068 trace name context forever, level 100’;
    So IMO this is related to Bug 4112254

    Regards
    Marie

    Comment by Marie Raillard — October 29, 2010 @ 11:07 am BST Oct 29,2010 | Reply

  9. […] cost option during its search for an execution path. It turns out that this isn’t true. In a comment attached to a note I had written about a possible bug relating to function-based indexes I was told that there are […]

    Pingback by CBO Surprise | Oracle Scratchpad — February 22, 2018 @ 11:00 am GMT Feb 22,2018 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: