Re: Partitioned tables & indexes

Consider yourself lucky -- Oracle is not using that index because the index is probably useless, so the CBO is making the right decision. How many distinct DATE_CREATED values are there in each partition? There is a good possibility that, if the time-component of the DATE datatype is truncated, there is only 1 value. In which case an FTS is the best approach and this index should simply be dropped so it doesn't take up space and slow down inserts...

What you want is "partition-pruning". That is, you want Oracle to only query those partitions specified in the WHERE clause of your query. That is probably happening, though you didn't include any EXPLAIN PLAN information.

Your EXPLAIN PLAN should indicate whether partition pruning is occurring; be sure to use the DBMS_XPLAN.DISPLAY procedure for this (if 9i or above); otherwise use the "utlxplp.sql" script in "$ORACLE_HOME/rdbms/admin".

If the query is partition-pruning and is still moving slower and slower, then you simply have more and more data in the partitions you are accessing. Try to use another locally-indexed column to avoid the FTS...

On Wed, 4 May 2005 14:02:13 -0700, sol beach wrote
> 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

--
http://www.freelists.org/webpage/oracle-l