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.

Explain Plan and PLAN_TABLE$

Someone told me something a few weeks ago that didn’t make sense to me, and it still doesn’t.

Maybe I’m not seeing something obvious or maybe this is a legacy left over from the days before PLAN_TABLE became a synonym for SYS.PLAN_TABLE$ and SYS.PLAN_TABLE$ existed as a global temporary table? As ever, your comments on what follows are most welcome.

The discussion I was involved in regarded GoldenGate and the impact long running transactions were having. The comment that jumped out at me was that running EXPLAIN PLAN left a transaction open. “We’ll that implies that PLAN_TABLE$ is a global temporary table with on commit delete rows.” was my response. This doesn’t necessarily follow, but it was the first reason I could think of why Oracle would not code EXPLAIN PLAN to automatically commit… But, that raised the question (in my head) of, “Why don’t they just define PLAN_TABLE$ with ON COMMIT PRESERVE ROWS and perform a commit as part of EXPLAIN PLAN”. The discussion quickly moved on, but this seemed something worth spending a few minutes confirming…

First things first, I thought I’d better check the “duration” of PLAN_TABLE$

SYSTEM@orcl> select duration from all_tables where owner = 'SYS' and table_name = 'PLAN_TABLE$';

DURATION
--------------------
SYS$SESSION

SYSTEM@orcl> 

Well, that wasn’t what I was expecting. PLAN_TABLE$ records are persistent for the duration of a session, so why doesn’t EXPLAIN PLAN automatically commit? The best answer I can come up with is that it doesn’t because it just doesn’t. Pathetic, I know.

I can’t imagine that someone would start inspecting execution plans, via EXPLAIN PLAN, midway through a transaction, but hey, they might. However, if we have to learn that DDL, gathering statistics, etc perform implicit commits then why not just add EXPLAIN PLAN to that list?

Anyway, here’s a quick demo to stop you feeling you need to verify this for yourself.

Session 1 – Connect to a new session, verify PLAN_TABLE is empty and run EXPLAIN PLAN

20:59:13 MNASH@orcl> conn mnash
Enter password: 
Connected.
20:59:29 MNASH@orcl> select count(*) from plan_table;

  COUNT(*)
----------
         0

20:59:37 MNASH@orcl> explain plan for select * from t;

Explained.

20:59:50 MNASH@orcl> 

Session 2 – Query v$session and v$transaction to verify that there is a transaction open

20:58:57 SYSTEM@orcl> l
  1  select username,
  2         sid,
  3         sql_id,
  4         event,
  5         state,
  6         seconds_in_wait
  7    from v$session s
  8       , v$transaction t
  9*  where s.taddr = t.addr
20:59:58 SYSTEM@orcl> /

USERNAME     SID SQL_ID        EVENT                         STATE     SECONDS_IN_WAIT
---------- ----- ------------- ----------------------------- --------- ---------------
MNASH         17 6j5vng1ckm1p2 SQL*Net message from client   WAITING                11

21:00:01 SYSTEM@orcl>

Session 1 – Use DBMS_XPLAN.DISPLAY to view the plan

20:59:50 MNASH@orcl> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1161K|   107M|  4697   (1)| 00:00:57 |
|   1 |  TABLE ACCESS FULL| T    |  1161K|   107M|  4697   (1)| 00:00:57 |
--------------------------------------------------------------------------

8 rows selected.

21:00:16 MNASH@orcl>

Session 2 – Query v$session and v$transaction to verify that there is a transaction open

21:00:01 SYSTEM@orcl> /

USERNAME     SID SQL_ID        EVENT                         STATE     SECONDS_IN_WAIT
---------- ----- ------------- ----------------------------- --------- ---------------
MNASH         17               SQL*Net message from client   WAITING                 6

21:00:22 SYSTEM@orcl>

Session 1 – Commit

21:00:16 MNASH@orcl> commit;

Commit complete.

21:00:33 MNASH@orcl> 

Session 2 – Query v$session and v$transaction to verify that the transaction is closed

21:00:22 SYSTEM@orcl> /

no rows selected

21:00:40 SYSTEM@orcl> 

If the above interested you then have a read of the sections containing “commit” in the EXPLAIN PLAN documentation.

EXPLAIN PLAN FOR Anomaly

First of all apologies to anyone who lands here expecting some interesting execution plan analysis… This is about name resolution for the EXPLAIN PLAN command in comparison to everything else I’m aware of.

Wth the usual disclaimer of: I might have missed something… EXPLAIN PLAN is not affected by the “ALTER SESSION SET CURRENT_SCHEMA” command.

This little investigation all started with a report of “explain plan” not working in PL/SQL Developer. On digging into it we discovered 3 PLAN_TABLE tables in the database plus a couple of synonyms. The names have been changed to protect the innocent, so to speak. Anyway it looked something like this:


SQL> select owner, object_name, object_type from dba_objects where object_name = 'PLAN_TABLE';

OWNER                          OBJECT_NAME OBJECT_TYPE
------------------------------ ----------- -------------------
PUBLIC                         PLAN_TABLE  SYNONYM
APP_SCHEMA                     PLAN_TABLE  TABLE
USER01                         PLAN_TABLE  SYNONYM
SYSTEM                         PLAN_TABLE  TABLE
SYS                            PLAN_TABLE  TABLE

With the PUBLIC synonym pointing to SYSTEM.PLAN_TABLE and the synonym owned by USER01 pointing to APP_SCHEMA.PLAN_TABLE. Just to spice things up, a logon trigger used ALTER SESSION SET CURRENT_SCHEMA to APP_SCHEMA for pretty much all database users. After a quick test I confirmed that things were definitely not working as expected via SQL*Plus as shown below:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining op
JServer Release 9.2.0.8.0 - Production

MARTINNASH@orcl> explain plan for
  2  select * from dual;

Explained.

MARTINNASH@orcl> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------


--------------------------------------------------------------------------------------------------------
| Id  | Operation                            |  Name           | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|-------------------------------------- NOT THE QUERY I JUST RAN --------------------------------------|
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  <------------ TEXT REMOVED ------------>

Note: cpu costing is off

Remember the logon trigger changes my current schema to APP_SCHEMA.

MARTINNASH@bi20p> alter session set current_schema = martinnash;

Session altered.

MARTINNASH@bi20p> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |
--------------------------------------------------------------------

Note: rule based optimization

I traced the use of EXPLAIN PLAN FOR and found (taken from 11g rather than 9i):

insert into plan_table (statement_id, timestamp, operation, options,object_node, object_owner, object_name, object_instance, object_type,search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, object_alias, plan_id, depth, remarks, other_xml ) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)

But as demonstrated below (again 11g rather than 9i) EXPLAIN PLAN FOR doesn’t appear to honour changing schema using ALTER SESSION SET CURRENT_SCHEMA.

I created a user MNASH and created a PLAN_TABLE in MNASH’s schema using $ORACLE_HOME/rdbms/admin/utlxplan.sql. This was in addition to the PUBLIC synonym PLAN_TABLE for the SYS.PLAN_TABLE$ temporary table created by default.

SQL> explain plan for 
  2  select * from dual;      

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

All cool and working as expected. The following shows that not only are the plan records written to MNASH.PLAN_TABLE, but PLAN_TABLE is resolved to MNASH.PLAN_TABLE.

SQL> select count(*) from sys.plan_table$;

  COUNT(*)
----------
         0

SQL> select count(*) from mnash.plan_table;

  COUNT(*)
----------
         2

SQL> select count(*) from plan_table;

  COUNT(*)
----------
         2

SQL> 

However, if I change my current schema to that of another user and run EXPLAIN PLAN then use DBMS_XPLAN it does not give my execution plan…

SQL> alter session set current_schema = scott;

Session altered.

SQL> select sys_context('userenv','session_user') username
  2       , sys_context('userenv','current_schema') schema from dual;

USERNAME   SCHEMA
---------- ----------
MNASH      SCOTT

SQL> explain plan for
  2  select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE

Looking at where EXPLAIN PLAN FOR has written the rows reveals that the PUBLIC synonym PLAN_TABLE that points to SYS.PLAN_TABLE$ is not “seen” by EXPLAIN PLAN FOR, but is seen by the SELECT statements.

SQL> select count(*) from sys.plan_table$;

  COUNT(*)
----------
         0

SQL> select count(*) from mnash.plan_table;

  COUNT(*)
----------
         4

SQL> select count(*) from plan_table;

  COUNT(*)
----------
         0

So the PLAN_TABLE that is resolved first by EXPLAIN PLAN FOR is not the same PLAN_TABLE that is resolved first by standard SQL including DBMS_XPLAN, which is defined with “invoker rights”.

SQL> select * from table(dbms_xplan.display('mnash.plan_table'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Obviously this is not something that is going to be affecting loads of people, after all you’d not only have to be using ALTER SESSION SET CURRENT_SCHEMA, but you’d also have to have created multiple PLAN_TABLE tables in your database (and use them). That said if you do find yourself in the same situation then hopefully this will explain what is going on.

If anyone can explain why/how EXPLAIN PLAN FOR doesn’t see itself as running in the “CURRENT_SCHEMA”/”SESSION_SCHEMA” then I’d be very interested to know.