Re: Table Partitioning Question

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1156115363.498021_at_bubbleator.drizzle.com...
>
> 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?

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

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

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

>
> 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. Received on Sun Aug 20 2006 - 18:57:05 CDT