Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Partitioned tables & indexes
Hi,
The point is that these type of queries shouldn't need to use an index as y= our query predicate of
WHERE date_created >=3D3D TO_DATE('2005-01-01 00:00:00','YYYY-MM-DD HH24:=
MI=3D
:SS')
AND date_created < TO_DATE('2005-05-04 00:00:00','YYYY-MM-DD
HH24:MI:SS')
is basically saying "give me all the data between the 1st January and 3rd M= ay". A partitioned table will automatically eliminate partitions outside if= this range without using an index (as long as the query is using the parti=tioning key as a where predicate which yours seems to be)
Because of this, performance for this query should remain static so your sl= ow down is either due to partition elimination not working correctly which = can easily be checked from the query plan or is it possible that your query= is a "year to date query" so you are always asking for a 1,000,000 more ro= ws than the previous days query which will inevitably slow down as the year=pogresses due to the extra amount of processing you are asking it to do.
B.T.W. As I mentioned in a previous posting, unless you are only interested= in an hour of so's worth of data your index on the date_created column is = totally redundant and may even harm perfomance. Try forcing your query to u=se it using a hint to see what I mean.
Cheers,
Ian
This email is only intended for the person to whom it is addressed and may = contain confidential information. If you have received this email in error,= please notify the sender and delete this email which must not be copied, d=istributed or disclosed to any other person.
Unless stated otherwise, the contents of this email are personal to the wri= ter and do not represent the official view of Ordnance Survey. Nor can any = contract be formed on Ordnance Survey's behalf via email. We reserve the ri=ght to monitor emails and attachments without prior notice.
Thank you for your cooperation.
Ordnance Survey
Romsey Road
Southampton SO16 4GU
Tel: 023 8079 2000
http://www.ordnancesurvey.co.uk
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 05 2005 - 04:31:44 CDT