I have a Table which is range partitioned on numeric(parameterinstanceid) value. I want to select max+1 value of the same column .i have global non-partition index on parameterinstanceid.
select /*+ parallel(a,32,8) */ max(parameterinstanceid) +1 from parameterinstance a;
On checking the explain plan i see that it is doing a INDEX FULL SCAN (MIN/MAX) on the table. i want to do it by checking in max partition first,if it doesnt contain any data ,then next partition ,in desc order .i can write a procedure to do that ,but i want to know if there is a simple query for it. http://www.oramoss.com/blog/2009/06/no-pruning-for-minmax-of-partition-key.html ..it seems that it is a unsolved issue.
EDIT :
The partition names are PI_P01,PI_P02,...PI_P10,PI_PMAXVALUE
explain plan is:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2808487136
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 34 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE ALL | | 1 | 8 | 34 (0)| 00:00:01 | 1 | 11 |
| 3 | INDEX FULL SCAN (MIN/MAX)| PI_PK | 1 | 8 | 34 (0)| 00:00:01 | 1 | 11 |
-----------------------------------------------------------------------------------------------------
and table struct:
Name Null? Type
------------------------------ -------- -----------------
PARAMETERINSTANCEID NOT NULL NUMBER
PARAMINSTANCE2PARAMSETVERSION NOT NULL NUMBER
PARAMINSTANCE2PARAMDEFINITION NOT NULL NUMBER
PARAMINSTANCE2PARENTPARAM NUMBER
SEQUENCE NUMBER
X_CTCV_CONV_ID VARCHAR2(50 CHAR)
X_CONV_RUN_NO NUMBER
and the indexes on the table :
INDEX_NAME POSITION COLUMN_NAME
------------------------------ -------- -----------------------------
PI_UK 1 PARAMINSTANCE2PARAMSETVERSION
PI_UK 2 PARAMINSTANCE2PARAMDEFINITION
PI_PK 1 PARAMETERINSTANCEID
PI_PAD_FK_I 1 PARAMINSTANCE2PARAMDEFINITION
PI_PI_FK_I 1 PARAMINSTANCE2PARENTPARAM
If you have a global index on the partition key, you should get a plan like this:
----------------------------------------------------------------------------
| Id | Operation | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | 1 | 9 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | 1 | 9 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| 1 | 9 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Here the MIN/MAX is very significant, it means that Oracle will stop at the first entry, bypassing the partition table.
The link you have provided is different since the partition key is not indexed (hence it produces a FULL TABLE SCAN instead of an INDEX FULL SCAN.
If you have no index on the partition key, it seems that going for a TOP-N query as suggested by Jonathan Lewis may do exactly what you want. Here is the plan I get with the table test as in the example in your link:
explain plan for
select * from (select col_date_part_key
from test
order by col_date_part_key desc)
where rownum = 1
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Pstart| Pstop |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | | |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | PARTITION RANGE ALL | | 1 | 9 | 12 | 1 |
| 3 | VIEW | | 1 | 9 | | |
|* 4 | SORT ORDER BY STOPKEY| | 1 | 9 | | |
| 5 | TABLE ACCESS FULL | TEST | 1 | 9 | 12 | 1 |
-------------------------------------------------------------------------
As you can see Oracle will start with the last partition (Pstart=12) and will walk up to the first partition (Pstart=1) until it gets one row (Rows=1).
I've run another test with a setup I hope is similar to yours and I find a different, more logical plan. Setup:
create table parameterinstance (
PARAMETERINSTANCEID NUMBER NOT NULL,
PARAMINSTANCE2PARAMSETVERSION NUMBER NOT NULL,
PARAMINSTANCE2PARAMDEFINITION NUMBER NOT NULL,
PARAMINSTANCE2PARENTPARAM NUMBER ,
SEQUENCE NUMBER ,
X_CTCV_CONV_ID VARCHAR2(50 CHAR) ,
X_CONV_RUN_NO NUMBER
) partition by range (PARAMETERINSTANCEID)
( partition p1 values less than (1000) storage (initial 64k),
partition p2 values less than (2000) storage (initial 64k),
partition p3 values less than (3000) storage (initial 64k),
partition p4 values less than (4000) storage (initial 64k),
partition p5 values less than (5000) storage (initial 64k),
partition p6 values less than (6000) storage (initial 64k),
partition p7 values less than (7000) storage (initial 64k),
partition p8 values less than (8000) storage (initial 64k),
partition p9 values less than (9000) storage (initial 64k),
partition p10 values less than (maxvalue) storage (initial 64k)
);
CREATE UNIQUE INDEX PI_PK ON parameterinstance(PARAMETERINSTANCEID) local;
insert into parameterinstance
(SELECT rownum, rownum, rownum, '', '', rpad('x', 50, 'x'), ''
from dual connect by level <= 1e4);
On 11gR2 i get the following plan:
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Pstart| Pstop |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | |
| 1 | PARTITION RANGE ALL | | 1 | 13 | 10 | 1 |
| 2 | SORT AGGREGATE | | 1 | 13 | | |
| 3 | INDEX FULL SCAN (MIN/MAX)| PI_PK | 1 | 13 | 10 | 1 |
-----------------------------------------------------------------------------
Note that the partition are listed in the appropriate order (from 10 downwards to 1). The plan is different in 9iR2:
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Pstart| Pstop |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | PARTITION RANGE ALL | | | | 1 | 10 |
| 3 | INDEX FULL SCAN (MIN/MAX)| PI_PK | 10000 | 40000 | 1 | 10 |
-----------------------------------------------------------------------------
So it seems that some optimization has taken place between 9i and 11g. Time for an upgrade?