RE: Partitioned tables & indexes
- From: "Ian Cary (C)" <Ian.Cary@xxxxxxxxxxxxxxxxxxxx>
- To: <sol.beach@xxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 May 2005 09:27:17 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
