Databases
StackOverflow2013
Postsdbo
POselect max of a column using range partition
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POselect max of a column using range partition
    text
    copied!<p>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.</p> <pre><code>select /*+ parallel(a,32,8) */ max(parameterinstanceid) +1 from parameterinstance a; </code></pre> <p>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. <a href="http://www.oramoss.com/blog/2009/06/no-pruning-for-minmax-of-partition-key.html" rel="nofollow">http://www.oramoss.com/blog/2009/06/no-pruning-for-minmax-of-partition-key.html</a> ..it seems that it is a unsolved issue.</p> <p>EDIT :</p> <p>The partition names are PI_P01,PI_P02,...PI_P10,PI_PMAXVALUE</p> <p>explain plan is:</p> <pre><code>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 | ----------------------------------------------------------------------------------------------------- </code></pre> <p>and table struct:</p> <pre><code> 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 </code></pre> <p>and the indexes on the table :</p> <pre><code> 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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload