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.

ORA-02014:cannot select FOR UPDATE when using ROWNUM

9am, morning ...


I’m drinking my coffee and doing my morning run through my emails …

As I’m doing this, I’m regretting I have ever bought anything online …

Few emails not being spam contain some useful stuff for me. A friend of mine emailed me asking if I could help him with his problem.

He had a specific SQL with FOR UPDATE clause which was throwing ORA-02014 at him. Here is his SQL

SELECT tab.*
FROM
  (SELECT tab.ROWID AS rid, tab.*
   FROM changes tab
   WHERE state IN ('N', 'E')
   ORDER BY company, id, change_date
   ) tab
WHERE ROWNUM <= 10
FOR UPDATE OF tab.state;

Full error message is as follows

SQL Error: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

*Cause:    An attempt was made to select FOR UPDATE from a view which had not been merged because the view used syntax (for example, DISTINCT or GROUP BY) preventing the merge, or because of initialization file parameter settings.

*Action:   Determine the reason the view will not merge and, if possible, make changes so that it can.

Table was basically sort of a FIFO stack and he always wanted to process only first 10 oldest rows ordered by given columns. Because he is a very careful fellow he wanted to lock the rows as he was selecting them, because after that there was some processing of the data before changing them.

After few dead ends SQL which did the trick looks as follows

SELECT *
FROM changes
WHERE rowid IN
  (SELECT rid
   FROM
    (SELECT ROWID AS rid
     FROM changes
     WHERE state IN ('N', 'E')
     ORDER BY company, id, change_date
    )
  WHERE ROWNUM <= 10
  )
FOR UPDATE OF state;

I hope this post will save you some time … if not today then maybe some other day :)