Partition pruning using non-prefixed local indexes

This small post is from nice and frighten world of partitions, specifically the case of partition pruning. If you are using partitions in your application there is fair chance that your database is quite big and performance demanding. If you are not working in classic Data Warehousing manner you have to care about DML statement performance. One of the basic rules of DML performance is less indexes you have and less index columns in existing indexes then higher is the DML performance. Thus one of the architectural DBA targets is to reduce the number of indexed columns by just most critical. Another axiom statement is that smaller indexes is easier to support and manage then big one, thus local indexes usually more preferable. The conclusion from two previous axiom is that probably most indexes on partitioned tables in your application is non-prefixed local indexes. Now we came to the topic of this post how oracle optimizer deal with such indexes and what we can do with it.

Lets create list base partitioned table transactions

Column Name Comment
SERNO Unique identifier
PARTITIONKEY Partition Key
SGENERAL Some indexed Field

And create two indexes TRANSACTIONS_PK (SERNO, PARTITIONKEY) and local partitioned non-prefixed index TRANSACTIONSI (SGENERAL).

Let’s start from the most common statement


EXPLAIN PLAN FOR
 SELECT SGENERAL, count(1)
 FROM transactions
 WHERE PARTITIONKEY='JUL2012' AND SGENERAL is not null
 GROUP BY SGENERAL;

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————-
Plan hash value: 2338610280

Id Operation Name

Rows

Bytes

Cost

Time

Pstart

Pstop

0 SELECT STATEMENT

5

55

69978 (1)

00:14:00

1 HASH GROUP BY

5

55

69978 (1)

00:14:00

2 PARTITION LIST SINGLE

5340K

56M

69727 (1)

00:13:57

KEY

KEY

*3 TABLE ACCESS FULL TRANSACTIONS

5340K

56M

69727 (1)

00:13:57

19

19

Predicate Information (identified by operation id):
—————————————————
3 – filter(“STGENERAL” IS NOT NULL)

Does it look like non-optimal? Optimizer has chosen to do FULL SCAN even if it has nice looking index TRANSACTIONSI. The problem is that partition pruning works only for prefixed indexes, that means you have to pay for support extra column in your index even if you do not need it because you clearly can get the partition name based from the identical rules on a table.

But luckily we have extended syntaxes. Uses it we can manually define the partition that should be used. In our case the partition name is equal to the partition key. Getting the partition name is very simple for list partitions but can be received in all other cases too.

EXPLAIN PLAN FOR
 SELECT SGENERAL, count(1)
 FROM TRANSACTIONS PARTITION ("JUL2012")
 WHERE SGENERAL is not null
 GROUP BY SGENERAL;

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————-
Plan hash value: 198159339

Id Operation Name

Rows

Bytes

Cost

Time

Pstart

Pstop

0 SELECT STATEMENT

4

20

10 (20)

00:00:01

1 SORT GROUP BY NOSORT

4

20

10 (20)

00:00:01

2 PARTITION LIST SINGLE

8890

44450

8 (0)

00:00:01

KEY

KEY

*3 INDEX FULL SCAN TRANSACTIONSI

8890

44450

8 (0)

00:00:01

19

19

Predicate Information (identified by operation id):

—————————————————
3 – filter(“SGENERAL” IS NOT NULL)

This plan looks much better. According to optimizer estimations we can save 14 minutes on it. But there is even more attractive syntaxes for those who do not want to spend their time calculating the partition name

 EXPLAIN PLAN FOR
 SELECT SGENERAL, count(1)
 FROM TRANSACTIONS PARTITION FOR ('JUL2012')
 WHERE SGENERAL is not null
 GROUP BY SGENERAL;

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————
Plan hash value: 198159339

Id Operation Name

Rows

Bytes

Cost

Time

Pstart

Pstop

0 SELECT STATEMENT

4

20

10 (20)

00:00:01

1 SORT GROUP BY NOSORT

4

20

10 (20)

00:00:01

2 PARTITION LIST SINGLE

8890

44450

8 (0)

00:00:01

KEY

KEY

*3 INDEX FULL SCAN TRANSACTIONSI

8890

44450

8 (0)

00:00:01

19

19

Predicate Information (identified by operation id):
—————————————————
3 – filter(“SGENERAL” IS NOT NULL)

The only problem with last syntax is that it is supported only from 11.2 version. The syntax was developed to support interval partitions but can be handy for all other types.

Does it looks that with new syntax we came to the kingdom of wealth and prosperity, where we can easily avoid prefixes on local indexes and still use the effective partition pruning? But it is not. The hidden rock is that the partition keys in PARTITION FOR clause could not be defined throw the variables like in WHERE clause. Below there are examples of such attempt

Firstly traditional approach

 DECLARE
 v_part char(7):='JUL2012';
 BEGIN
 FOR REC IN (SELECT SGENERAL, count(1) FROM TRANSACTIONS
 WHERE PARTITIONKEY=v_part AND SGENERAL is not null
 GROUP BY SGENERAL)
 LOOP
 NULL;
 END LOOP;
 END;
 /
 Elapsed: 00:12:42.05

It works but does not looks like very performance effective. Let’s go and try 10g approach in PL/SQL

DECLARE
 v_part varchar2(10):="JUL2012";
 BEGIN
 FOR REC IN (SELECT SGENERAL, count(1) FROM TRANSACTIONS PARTITION (v_part)
 WHERE SGENERAL is not null
 GROUP BY SGENERAL)
 LOOP
 NULL;
 END LOOP;
 END;
 /

ERROR at line 2:
 ORA-06550: line 2, column 25:
 PLS-00201: identifier "JUL2012" must be declared
 ORA-06550: line 2, column 10:
 PL/SQL: Item ignored

It looks like kind of expected behavior. You could not use table name as variable, why you should be able to use partition name. Personally I put a lot of hope on the last test with PARTITIONKEY reference

DECLARE<span style="color: #000000;">
v_part char(7):='JUL2012';
 BEGIN
 FOR REC IN (SELECT SGENERAL, count(1) FROM TRANSACTIONS
 PARTITION FOR (v_part)
 WHERE SGENERAL is not null
 GROUP BY SGENERAL)
 LOOP
 NULL;
 END LOOP;
 END;
 /

ERROR at line 1:
 ORA-14763: Unable to resolve FOR VALUES clause to a partition number
 ORA-06512: at line 4

It was the first time I see the error thus have a look into the documentation

ORA-14763: Unable to resolve FOR VALUES clause to a partition number

Cause: Could not determine the partition corresponding to the FOR VALUES clause.

Action: Remove bind variables and dependencies on session parameters from the values specified in the FOR VALUES clause.

Ha-Ha-Ha nothing change we still could not pass partition key values into query.

Summarizing oracle offer the mechanism that allows to use partition pruning on non-prefixed local indexes but have not support the syntaxes in pl/sql using binds. The usage of this syntaxes have sense only in case of big queries (e.g. reports) running on a table, when gains from effective execution plan prevail over the loses of dynamic sql.

About these ads

4 thoughts on “Partition pruning using non-prefixed local indexes

  1. Hi,

    nice post.

    Here is an example where you can use different partition names (10g Syntax).

    DECLARE
    type t_cur is ref cursor;
    cur_sql t_cur;
    v_part varchar2(10):=”0001610304″;
    v_sql varchar2(300);
    BEGIN
    v_sql=’SELECT SGENERAL, count(1) FROM TRANSACTIONS PARTITION (‘||v_part||’) WHERE SGENERAL is not null GROUP BY SGENERAL’;
    open cur_sql for v_sql;
    LOOP
    NULL;
    END LOOP;
    END;
    /

    • Hi Christian,
      thank you for your reply.
      Dynamic sql can be the valuable workaround but you have to take into account the parsing costs.
      Sometime it have sense, sometime the gains less then loses.
      Cheers

  2. Exactly, this is what I can see, that it’s great the option of “PARTITION BY RANGE (..) INTERVAL ( NUMTODSINTERVAL (1, ‘day’) )”
    Combined with “PARTITION FOR” could be great. The pity is that this can’t be done in my pl/sql :(
    At less without dynamic sql!!
    So, if I want a full scan of one day in a partitioned table by ‘day’. What do you think could be the better approach?
    1- With where CONSOLIDATION_DATE between startDay and endDay
    2- With dynamic sql and “PARTITION FOR”

    Is there any other better solution?
    I think I will build the two solutions and finally test both of them.
    Thank you!

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