RE: INDEX RANGE SCAN MIN/MAX

This is a case to set the event 10053. you will see what the optimizer.  

Beside that, I presume you copied from th 131 towards the 71 otherwise you have missing stats.
However, in the first case, I don't understand why you pstart is > to pstop. It is not obvious you are using an index in desc order. The second case with pstart 1 to pstop 113 show that there are no partitions prunning.    

bp

From: Merrill, Chris [mailto:CMerrill_at_concordefs.com] Sent: Friday, 13 October, 2006 9:42 PM
To: 'oracle-l_at_freelists.org'
Subject: INDEX RANGE SCAN MIN/MAX

Hello,  

 I have a large partitioned table that resides on 2 different database. This table is partitioned by a varchar2 date (YYYYMMDD) daily.

Both tables have a "maxvalue" partition. I am copying the stats for each database so they are the same. However on one database I have 71 partitions

While the other has 113.  

The following query is executed against each database.  

Select max(batch_date) from table where id = "value" and level_no = 'value;  

In one database the explain plan is below. The "pstart" is 71 and "pstop" is 1.  

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop  

SELECT STATEMENT Optimizer Mode=ALL_ROWS                   1
7                                                           

  SORT AGGREGATE                1          31


    PARTITION RANGE ALL                    84 K     2 M       72
71         1

      FIRST ROW                        84 K     2 M       72


        INDEX RANGE SCAN (MIN/MAX)  CLD.HL_CARD_TYPE_SUM_NETID_NIDX
84 K     2 M       72
71         1

 

 

When I run against the second database I get: The pstart in this case 1 and it scans all partitions.  

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop  

SELECT STATEMENT Optimizer Mode=ALL_ROWS                   1
10                                                          

  SORT AGGREGATE                1          31


    PARTITION RANGE ALL                    134 K    3 M       10
1          113

      INDEX FAST FULL SCAN   CLD.HL_CARD_TYPE_BATCH_IDX       134 K    3
M       10                                             1            113

 

 

The information in this message may be proprietary and/or confidential, and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify First Data immediately by replying to this message and deleting it from your computer.

--
http://www.freelists.org/webpage/oracle-l