RE: Partitioned tables & indexes
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <sol.beach@xxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 May 2005 07:51:03 -0400
It looks to me like you have a design problem. You partitioned by month
but you are querying by quarter. Maybe you should have partitioned by
quarter - you know - another column in the table that a trigger would
populate that could hold values like 200501 which means Jan. thru March
of 2005?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of sol beach
Sent: Wednesday, May 04, 2005 5:02 PM
To: Oracle-L Freelists
Subject: Partitioned tables & indexes
Oracle v9.2
I'll admit I don't have a lot of real world experience using
partitioned tables or indexes.
I've inherited an application which has a couple of tables partitioned
on "DATE_CREATED";
where each partition contains 1 month data.
Records are ONLY inserted into this table at a rate about a million per
day=3D
.
There is an index (partitioned) on the DATE_CREATED column.
I've run EXPLAIN PLANS on a half dozen or more SQL queries this morning
all of which have a where clause similar to the following:
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')
NONE of queries ever used the index & always did a FTS.
What could be done to actually start using this index?
The bottom line problem is that queries against this table are just
taking longer & longer to
complete.
Any ideas, hints, suggestions or Fine Manual names so I can RTFM would
be welcomed.
TIA!
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
