Tony’s Oracle Tips

June 8, 2009

CBO Behaviour With Indexes on Tables Partitioned by List

Filed under: SQL Fun — tonyhasler @ 9:45 am

Since I wrote this blog Jonathan Lewis has pointed out a flaw on this blog entry. See my own reply below.
This blog entry discusses an important difference between selecting a named partition of a table partitioned by list versus specifying the list value in a predicate. The examples in this blog entry were tested in 10.2.0.3 and 11.1.0.6.0 with identical results. Let us begin by creating our table:

DROP TABLE list_example;

CREATE TABLE list_example
(
  col1     INTEGER,
  col2     INTEGER,
  col3     INTEGER,
  partcol  DATE
)
PARTITION BY LIST (partcol)
(
  PARTITION p20090703 VALUES (DATE '2009-07-03') ,
  PARTITION p20090704 VALUES (DATE '2009-07-04')
);

CREATE BITMAP INDEX col1_index ON list_example (col1) LOCAL;
CREATE BITMAP INDEX col2_index ON list_example (col2) LOCAL;
CREATE BITMAP INDEX col3_index ON list_example (col3) LOCAL;

Now in real life we would need to load some data into the table and gather statistics before attempting to query it. But this isn’t necessary here. What I want to show you now is a possible query execution plan based for a particular query forced through a hint. Here is the query and the associated execution plan:

SELECT /*+ index_join (l (col2) (col1) (col3))  */
       COUNT (*)
  FROM list_example PARTITION (p20090703) l
 WHERE col1 = 1 AND col2 = 2 AND col3 = 3

And here is the (right truncated) execution plan:

-------------------------------------------------------------
| Id  | Operation                        | Name             |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                  |
|   1 |  SORT AGGREGATE                  |                  |
|*  2 |   VIEW                           | index$_join$_001 |
|*  3 |    HASH JOIN                     |                  |
|*  4 |     HASH JOIN                    |                  |
|   5 |      PARTITION LIST SINGLE       |                  |
|   6 |       BITMAP CONVERSION TO ROWIDS|                  |
|*  7 |        BITMAP INDEX SINGLE VALUE | COL1_INDEX       |
|   8 |      PARTITION LIST SINGLE       |                  |
|   9 |       BITMAP CONVERSION TO ROWIDS|                  |
|* 10 |        BITMAP INDEX SINGLE VALUE | COL2_INDEX       |
|  11 |     PARTITION LIST SINGLE        |                  |
|  12 |      BITMAP CONVERSION TO ROWIDS |                  |
|* 13 |       BITMAP INDEX SINGLE VALUE  | COL3_INDEX       |
-------------------------------------------------------------

As you can see the three local bitmap indexes on a single partition have been joined using the rowid and no table access has occurred. In my experience, however, most people would write this code differently. This is what the execution plan looks like when you use a predicate instead of a partition name:

SELECT  /*+ index_join (l (col2) (col1) (col3))  */
       COUNT (*)
  FROM list_example l
 WHERE partcol = DATE '2009-07-03'
 AND col1 = 1 AND col2 = 2 AND col3 = 3

-----------------------------------------------
| Id  | Operation              | Name         |
|   0 | SELECT STATEMENT       |              |
|   1 |  SORT AGGREGATE        |              |
|   2 |   PARTITION LIST SINGLE|              |
|*  3 |    TABLE ACCESS FULL   | LIST_EXAMPLE |
-----------------------------------------------

Now this is quite different. It seems the hint has confused the optimizer since the plan generated is different from the un-hinted query. I must say that it is strange to see an index hint cause a full scan but that is by the by! The main point is that an index join has not been considered legal despite the fact that partition pruning has occurred. Why?

I spent some time trying to work out where the problem was and eventually realised that it may be that the CBO is unable to recognise the “special” case where the number of values in the list was one. This is, of course, quite common but not required. Suppose our table had been created thus:

DROP TABLE list_example;

CREATE TABLE list_example
(
  col1     INTEGER,
  col2     INTEGER,
  col3     INTEGER,
  partcol  DATE
)
PARTITION BY LIST (partcol)
(
  PARTITION p0703 VALUES (DATE '2009-07-03', DATE '2010-07-03') ,
  PARTITION p0704 VALUES (DATE '2009-07-04', DATE '2010-07-04')
);

CREATE BITMAP INDEX col1_index ON list_example (col1) LOCAL;
CREATE BITMAP INDEX col2_index ON list_example (col2) LOCAL;
CREATE BITMAP INDEX col3_index ON list_example (col3) LOCAL;

Now in this case the explanation for the difference in the plans would be much easier to spot. The two queries have different semantics. When a predicate is used we need to access the table to weed out the rows that are in the partition but do not match our specified value. It seems that the CBO doesn’t realise that when there is only one value in the list that these rows can’t exist and, therefore, do not need to be weeded out.

To solve this problem we could create an index on PARTCOL. But this would, quite literally, be a waste of space. A better solution is to change our indexes to include PARTCOL. The bitmap indexes will not be significantly larger since the cardinality of (COL1) and (COL1, PARTCOL) will be the same. Here goes:

DROP TABLE list_example;

CREATE TABLE list_example
(
  col1     INTEGER,
  col2     INTEGER,
  col3     INTEGER,
  partcol  DATE
)
PARTITION BY LIST (partcol)
(
  PARTITION p0703 VALUES (DATE '2009-07-03') ,
  PARTITION p0704 VALUES (DATE '2009-07-04')
);

CREATE BITMAP INDEX col1_index ON list_example (col1,partcol) LOCAL;
CREATE BITMAP INDEX col2_index ON list_example (col2,partcol) LOCAL;
CREATE BITMAP INDEX col3_index ON list_example (col3,partcol) LOCAL;

When the indexes are created this way the index join works. Note that somewhat counterintuitively I added the partitioning column as a suffix rather than a prefix. This allows the index to be used when a query is executed without a predicate on the partitioning column.

Advertisements

Blog at WordPress.com.