INDEX RANGE SCAN MIN/MAX
- From: "Merrill, Chris" <CMerrill@xxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 Oct 2006 14:42:03 -0500
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.
Other related posts:
