Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Partitioned tables & indexes

RE: Partitioned tables & indexes

From: Ian Cary (C) <Ian.Cary_at_ordnancesurvey.co.uk>
Date: Thu, 5 May 2005 09:27:17 +0100
Message-ID: <AB4FD5014A06414688019F9244194C66DED967@EXCHMSA.ordsvy.gov.uk>


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-l
Received on Thu May 05 2005 - 04:31:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US