Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Help:select against partitions
One of our big tables has 12 partitions on polltime, which is
(Jan_2002,Feb_2002,Mar_2002,...Dec_2002).
The table is fully analyzed.
When we use
Select ....
From Big_table
where polltime >
to_date('01/10/2002 11:00:00','MM/DD/YYYY HH24:MI:SS')
AND polltime < to_date('01/10/2002 11:59:00','MM/DD/YYYY HH24:MI:SS')
the cost is about 9000.
but if we use
Select ....
From Big_table partition (Jan_2002)
where polltime >
to_date('01/10/2002 11:00:00','MM/DD/YYYY HH24:MI:SS')
AND polltime < to_date('01/10/2002 11:59:00','MM/DD/YYYY HH24:MI:SS')
The cost is only 200.
Why oracle does not know which partition to go, and needs us to tell him which partition?
Is there any way we can avoid of specifying WHICH partition in the query and still get the same performance?
Thanks for your help
-- Sent by dbadba62 from hotmail part of com This is a spam protected message. Please answer with reference header. Posted via http://www.usenet-replayer.com/cgi/content/newReceived on Wed Feb 20 2002 - 15:45:54 CST