INDEX RANGE SCAN MIN/MAX

From: Merrill, Chris <CMerrill_at_concordefs.com>
Date: Fri, 13 Oct 2006 14:42:03 -0500
Message-ID: <17ECCBDCF27C544583F2CAD928F953261121BB59@memex1.corp.cefs.int>

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

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 Received on Fri Oct 13 2006 - 14:42:03 CDT