FAQ
Hello,

In this test case (copied from Tom Kyte's site, thanks!)

CREATE TABLE table2000 ( x int, y int, z DATE)
PARTITION BY RANGE (z)

(
PARTITION tab_1999_h1 VALUES LESS
THAN(to_date('30-jun-1999','dd-mon-yyyy')),

PARTITION tab_1999_h2 VALUES LESS
THAN(to_date('31-dec-1999','dd-mon-yyyy')),
PARTITION tab_2000_h1 VALUES LESS
THAN(to_date('30-jun-2000','dd-mon-yyyy')),
PARTITION tab_2000_h2 VALUES LESS
THAN(to_date('31-dec-2000','dd-mon-yyyy'))
)

insert into table2000 values ( 1, 1, '15-jun-1999' );
insert into table2000 values ( 2, 2, '15-dec-1999' );
insert into table2000 values ( 3, 3, '15-jun-2000' );
insert into table2000 values ( 4, 4, '15-dec-2000' );

commit

exec dbms_stats.gather_Table_Stats(null,'TABLE2000',no_invalidate=>false);

Now I want to get the MIN() of the partition key column for all the table

explain plan for select min(z) from table2000;
Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop |
0 | SELECT STATEMENT | | 1 | 8 | 3 (0)|
00:00:01 | | |
1 | PARTITION RANGE ALL| | 1 | 8 | |
1 | 4 |
2 | SORT AGGREGATE | | 1 | 8 | |
3 | TABLE ACCESS FULL| TABLE2000 | 4 | 32 | 3 (0)|
00:00:01 | 1 | 4 |
-------------------------------------------------------------------------------------------------

PARTITION RANGE ALL ?

This particular query is equivalent to

explain plan for select min(z) from table2000 partition (tab_1999_h1);
Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
0 | SELECT STATEMENT | | 1 | 8 | 2
(0)| 00:00:01 | | |
1 | PARTITION RANGE SINGLE| | 1 | 8 |
1 | 1 |
2 | SORT AGGREGATE | | 1 | 8 |
3 | TABLE ACCESS FULL | TABLE2000 | 1 | 8 | 2
(0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------

However the optimizer does not seem to recognize this optimization. I
ran the test on 10.2.0.5 and 11.2.0.1 . Same can be applicable to
MAX() .

If I create an index on the partition key

create index z_idx on table2000 (z) local;

the explain plan then
Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
0 | SELECT STATEMENT | | 1 | 8 | 1
(0)| 00:00:01 | | |
1 | PARTITION RANGE ALL | | 1 | 8 |
1 | 4 |
2 | SORT AGGREGATE | | 1 | 8 |
3 | INDEX FULL SCAN (MIN/MAX)| Z_IDX | 1 | 8 | 1
(0)| 00:00:01 | 1 | 4 |
-----------------------------------------------------------------------------------------------------

and again with the partition name

explain plan for select min(z) from table2000 partition (tab_1999_h1);
Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
0 | SELECT STATEMENT | | 1 | 8 | 1
(0)| 00:00:01 | | |
1 | PARTITION RANGE SINGLE | | 1 | 8 |
1 | 1 |
2 | SORT AGGREGATE | | 1 | 8 |
3 | INDEX FULL SCAN (MIN/MAX)| Z_IDX | 1 | 8 | 1
(0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------------

So it seems that the optimizer is not able to take advantage of the
info stored in the dictionary for this particular case and goes for
partition range all instead of scanning just one partition (the "initial" one).

If the index is however global, the INDEX FULL SCAN (MIN/MAX) works
correctly (as expected).

Bug or Enhancement report ?

regards.

--
Nilo Segura
Oracle Support - IT/DB
CERN - Geneva
Switzerland
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Maxim Demenko at Feb 8, 2011 at 5:12 pm
    This particular query is not equivalent for at least some reasons.
    1) There is no quarantee, that data is available in the first partition
    at all (you can't consider fresh statistics as replacement for that)
    2) You can get data in wrong partitions (for example by executing
    exchange partition without validation)

    Best regards

    Maxim
    On 08.02.2011 17:53, Nilo Segura wrote:
    This particular query is equivalent to

    explain plan for select min(z) from table2000 partition (tab_1999_h1);
    --
    http://www.freelists.org/webpage/oracle-l
  • Nilo Segura at Feb 8, 2011 at 5:35 pm
    Ok, after some kind replies, I understood why it has to run the
    partition range all. I did the mistake of generalizing a particular
    case I was handling.

    thanks!

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 8, '11 at 4:53p
activeFeb 8, '11 at 5:35p
posts3
users2
websiteoracle.com

2 users in discussion

Nilo Segura: 2 posts Maxim Demenko: 1 post

People

Translate

site design / logo © 2022 Grokbase