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

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

Partitioned tables & indexes

From: sol beach <sol.beach_at_gmail.com>
Date: Wed, 4 May 2005 14:02:13 -0700
Message-ID: <40a16b36050504140231519d65@mail.gmail.com>


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= .
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 >=3D TO_DATE('2005-01-01 00:00:00','YYYY-MM-DD HH24:MI= :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!

--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 04 2005 - 17:06:39 CDT

Original text of this message

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