Index on sub partition column

I was about to name this blog post “Index mud” but no one would ever google it. So we’ll go with simple name “Index on sub partition column”.

You might call this another OTN post and you would be right. But I think it is a good example of usual – how to NOT do it.

The question was following:

Hi,

I am working on oracle 11g R2 on HP-UX platform.

We need to create a partition table have range (date) and sub partition by list (values) partition. I want to know if column we are using for sub partition (values ) is also need to index as we need to use that column in many of our select queries in where clause.

One member jump in and gave a simple answer:

Yes

.. And I was marked useful? Yap ... simple answer simple click. Not good guys, not good at all.

I presented a different opinion:

Since you are sub partitioning by list, I would expect there are not many values in list for particular sub partition? I would expect that selectivity will be quite low and index might not be of any use at all ... Unless you are selecting only indexed columns, for example.

With low selectivity partition prune will probably suffice. In your case I would do some testing with representative queries as index value is questionable here.

Ok. Let’s have a little presentation here. I’ve created table table1 with four indexes:

CREATE TABLE TABLE1
(
 P NUMBER(3),
 S NUMBER(1),
 filler VARCHAR2(100)
)
PARTITION BY RANGE (P) SUBPARTITION BY LIST (S)
(PARTITION P10 VALUES LESS THAN (11)
 (SUBPARTITION P10_3 VALUES (1,2,3),
  SUBPARTITION P10_6 VALUES (4,5,6),
  SUBPARTITION P10_9 VALUES (7,8,9)),
PARTITION P20 VALUES LESS THAN (21)
 (SUBPARTITION P20_3 VALUES (1,2,3),
  SUBPARTITION P20_6 VALUES (4,5,6),
  SUBPARTITION P20_9 VALUES (7,8,9)),
PARTITION P30 VALUES LESS THAN (31)
 (SUBPARTITION P30_3 VALUES (1,2,3),
  SUBPARTITION P30_6 VALUES (4,5,6),
  SUBPARTITION P30_9 VALUES (7,8,9)));

CREATE INDEX TABLE1_IX1 ON TABLE1(P,S) LOCAL;

TABLE1 has 379MB and TABLE1_IX1 has 54MB.

Each sub partition has around 100 000 rows for each list value (filler column has maximum size in each row). We are not going to go into the index details like testing different column combinations, etc. That is not important right now.

Now if you think about it, why would Oracle have any use for an index in following SQL?

SELECT * FROM TABLE1 WHERE P=10 AND S=4;

We are selecting all columns and selectivity of predicates in given sub partition is 33,3%. Let’s check the run statistics:

----------------------------------------------------------------------------------------------------- | Id  | Operation               | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |<br /> ----------------------------------------------------------------------------------------------------- |   0 |  SELECT STATEMENT       |        |      1 |        |      0 |00:00:01.61 |    8098 |   5271 | |   1 |   PARTITION RANGE SINGLE|        |      1 |    112K|    112K|00:00:01.16 |    5266 |   5264 | |   2 |    PARTITION LIST SINGLE|        |      1 |    112K|    112K|00:00:01.12 |    5266 |   5264 | |*  3 |     TABLE ACCESS FULL   | TABLE1 |      1 |    112K|    112K|00:00:01.07 |    5266 |   5264 | ---------------------------------------------------------------------------------------------------------

Ok. Now let’s force our index:

SELECT /*+ INDEX(TABLE1(P,S)) */ * FROM TABLE1 WHERE P=10 AND S=4;

---------------------------------------------------------------------------------------------------------------------- | Id  | Operation                            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:02.78 | 8196 | 5480 | |   1 |   PARTITION RANGE SINGLE             |            |      1 |    112K|    112K|00:00:02.34 |    5473 |   5473 | |   2 |    PARTITION LIST SINGLE             |            |      1 |    112K|    112K|00:00:02.30 |    5473 |   5473 | |   3 |     TABLE ACCESS BY LOCAL INDEX ROWID| TABLE1     |      1 |    112K|    112K|00:00:02.25 |    5473 |   5473 | |*  4 |      INDEX RANGE SCAN                | TABLE1_IX1 |      1 |    112K|    112K|00:00:00.20 |     270 |    270 | ----------------------------------------------------------------------------------------------------------------------

Now as you can see (check Buffers and Reads), there is no point of using our index in this case as it is more expensive then sub partition full table scan.

Now there are queries, where index will be beneficial, like:

SELECT P,S FROM TABLE1 WHERE P=10 AND S=4;

--------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | --------------------------------------------------------------------------------------------------------- |   0 |  SELECT STATEMENT       |            |      1 |        |      0 |00:00:00.57 |     974 |    275 | |   1 |   PARTITION RANGE SINGLE|            |      1 |    112K|    112K|00:00:00.40 |     270 |    270 | |   2 |    PARTITION LIST SINGLE|            |      1 |    112K|    112K|00:00:00.36 |     270 |    270 | |*  3 |     INDEX RANGE SCAN    | TABLE1_IX2 |      1 |    112K|    112K|00:00:00.31 |     270 |    270 | ---------------------------------------------------------------------------------------------------------

So what should you take from this is, that you should not jump into rush conclusions when adding index but think it through. You should ask yourself questions like

• What kind of queries will you use?
• Which columns will you be most likely selecting?
• What is selectivity of predicates?

It’s no point in wasting space and performance on maintaining index you don’t need.