Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Table Partitioning Question

Re: Table Partitioning Question

From: DA Morgan <>
Date: Sun, 20 Aug 2006 20:36:36 -0700
Message-ID: <>

Dereck L. Dietz wrote:
> "DA Morgan" <> wrote in message

>> 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 have had little experience with partitioned tables up till now. I would
> think that if partitioning were to be useful that the indexes should be
> local and that the primary key should include the field partitioned on. Or
> am I mistaken about the primary key?

The primary key is irrelevant to partitioning.

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

> I got the explain plan from Enterprise Manager. I don't have a copy but I
> do plan on doing some work on another program which will be reading the
> partitioned table(s) so I should have plenty of time to get a copy of it. I
> expect the query to run at least 2 hours as it is right now.

EXPLAIN PLAN using DBMS_XPLAN will take less than 100 milliseconds. I strongly recommend using it. If you are unfamiliar with it go to Morgan' Library at and look up the syntax and view a demo.

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

> I haven't tried that but I can certainly look into it.
>> Does this Oracle installation have a version number?

> Oracle 10g release 1.

If not I would strongly urge applying the patch.

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

> The statistics are recreated every week after every load. Unfortunately
> this is a process I'm not responsible for - at least not yet - and they are
> still gathering statistics using the old ANALYZE command.

The ANALYZE command was deprecated a long time ago and is of no value with This is one change that should be made immediately.

>> 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?

> I'd rather go into this in private. Suffice it to say that the DBA at the
> remote site once blamed a bad sector on a disk on someone's nested query
> (read only too). We're also a datawarehouse which is using SHARED SERVERS
> for all connections; all the control files and redo log files are
> multiplexed but are located in the SAME directory on the same disk; when
> monitoring the ADDM alerts I keep seeing PGA, SGA, virtual memory and other
> I/O warnings and tuning suggestions. I don't think my efforts are
> necesarily doomed to failure but it will be an uphill climb.

Contact me off-line. Your management needs to see this as a situation where they either give this DBA some serious training or replace them. What you describe is irresponsible: Likely common but irresponsible.

Oh am I glad for tenure. ;-)

Daniel A. Morgan
University of Washington
(replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sun Aug 20 2006 - 22:36:36 CDT

Original text of this message