Re: Table Partitioning Question

Dereck L. Dietz wrote:

> Where I work there are a number of large partitioned tables which have
> terrible performance.
>
> They also only have global indexes on these tables.

Enough said.

   The primary key seems
> to be a system generated sequence number. There is a non-unique index on
> the SRV_DT_FROM field.
>
> I know from using an explain plan on a query that even with a date specified
> in the query that Oracle will go through every partition instead of only the
> partition(s) the year or years are in.

The indexes should almost undoubtedly be local not global and it seems likely your understanding of the partitioning, translated into your WHERE clauses is not correct leading to a lack of pruning.

> I know this is pretty sketchy but other than trying to get them to use
> partitioned indexes what other avenues are there for getting better
> performance out of these partitioned tables?

It would be nice to see the Explain Plan? Be sure you create it using DBMS_XPLAN not some legacy script.

Can you run DBMS_METADATA on the remote server and get the actual DDL?

Does this Oracle installation have a version number?

Are statistics current and created using DBMS_STATS? Using what METHOD_OPT value?

But in the end it is likely inevitable that you will either have global indexes or good performance. You seem to indicate that your efforts are doomed to failure in advance. Why?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org