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

1 Comment »

  1. There is a flaw in this analysis. It turns out that the real reason that the index_join hint is ignored when the predicate is supplied is because of a general restriction on index joins that I was unaware of (until Jonathan Lewis explained it to me). That restriction is that all columns in the table referenced must be part of the indexes involved. You can, however, reference as many other columns as you want in other tables.

    It occurred to me that I may be able to perform a self-join based on the rowid. For example:

    SELECT b.* FROM t1 a,t1 b
    WHERE a.rowid=b.rowid AND a.c1='X' and a.C2='Y' ;

    Assuming that columns C1 and C2 are indexed in T1 this neat trick would seem to make the index join legal as the table with alias ‘a’ only references “columns” C1, C2 and ROWID that are all part of the indexes.

    Unfortunately, ROWID is not considered an indexed column! A bit silly as it is this “column” that is used in the join predicate in an index join! It turns out that Jonathan himself did not realise this so the discussion was mutually beneficial!

    Comment by tonyhasler — January 3, 2011 @ 4:58 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: