Dropping partition, effect on library cache sql

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Mon, 21 Apr 2008 12:52:56 -0700 (PDT)
Message-ID: <ff717022-00fc-487a-aa4d-5498b2bf155e@a1g2000hsb.googlegroups.com>

I have a feeling I know the answer to this question, but I am struggling to find a confirmation.

Oracle 9.2.0.8.

If I have a partitioned table (list partitioned), several local indexes and 1 global index and I add a partition and drop one - will all my SQL that references that table be invalidated in the library cache? I suspect it will.

We have an application that was working nicely, then the partition maintenance was done online and the execution plan of a key query totally changed (nice index accesses went to big full scans).

I am thinking the partition maintenance caused the sql to be reparsed, but I am still at a loss as to why the plans changed so dramatically.

The key index on this tables (ie the one the query should use) was a local index - am I correct in that it will not be marked unusable by partition maintenance? Received on Mon Apr 21 2008 - 14:52:56 CDT

This message: [ Message body ]
Next message: Shakespeare: "Re: RHEL4 Linux/MF CObol precompiler question-"
Previous message: Frank van Bortel: "Re: Best way to drop contents of a tablespace?"
Next in thread: Ronny: "Re: Dropping partition, effect on library cache sql"
Reply: Ronny: "Re: Dropping partition, effect on library cache sql"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message