Re: Dropping partition, effect on library cache sql

Did you generate index stats (--> DBMS_STATS) for the new partition's local index and table stats for the new partition? CBO loves to have them...

stephen O'D schrieb:
> 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 Fri Apr 25 2008 - 14:37:38 CDT