select max of a column using range partition

Question

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

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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).

Update

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?

my table does contain index on the same column,but it contains lot of data(100 million+),hence the query is taking lot of time to process(30-40 mins).that is why i was thinking of using the method to scan each partition in desc order.Also Explain plan shows pstart as 1 and pstop as 11 even in the max query.Should it not be reverse pstart=11 and pstop =1 i cannot post my explain plan as it is on production server,but explain plan for select max from a partition is much less than select max from a table. — subodh1989, Oct 11 '12 at 23:40
i just checked ,if i select min from the table it is taking less than a second because the pstart is 1 but select max from table is taking much long because of the same.how to i reverse the pstart to 11 and pstop to 1? — subodh1989, Oct 11 '12 at 23:56
Please post your CREATE TABLE statements with relevant indexes as well as your explain plan. If you have a **global** index on 11gR2, you should get a `FULL INDEX SCAN (MIN/MAX)` that will fetch one row only even if the table is huge. — Vincent Malgrat, Oct 12 '12 at 07:44
If you don't want to post your production explain plan/create table statements, create a similar case with a test table and then we can compare our results. — Vincent Malgrat, Oct 12 '12 at 07:47
i have added the explain plan ,indexes and columns on the table — subodh1989, Oct 12 '12 at 10:53
Ok, your index is locally partitioned, not global. Maybe that explains the difference. What version of Oracle are you running? — Vincent Malgrat, Oct 12 '12 at 12:31
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi — subodh1989, Oct 12 '12 at 14:14
See my updated answer: Are you getting the same result as in 9ir2 with this test case? If you are, then you may need to query the last partition manually. — Vincent Malgrat, Oct 12 '12 at 14:22
i think its because of version upgrade.though i cannot upgrade my oracle on production , i can to it manually itself by quering the last partition as suggested.thanks! — subodh1989, Oct 12 '12 at 14:27

select max of a column using range partition

1 Answers1

Update