Re: Identifying table partition name

"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message news:130ba93a.0305191509.24bafe3f_at_posting.google.com...
> Instead of trying to do this manually, try this:
> 1. Set your tables in monitoring mode.
> 2. Use DBMS_STATS.GATHER_SCHEMA_STATS with
> granularity set to 'partition' and
> options set to 'GATHER STALE'
>
> This way only partition level statistics will be gathered for only the
> partitions that contain stale data. Actually, you can look up
> user_tab_modifications to find out which partitions has new data since
> time you gather stats on them. If you insist on using
> dbms_stats.gather_table_stats, you probably can query this view to
> identify the partitions that have new data.

Jusung,

Yes I have tried this route. Unfortunately there is quite a time lag between a batch insert of data into a monitored table, and the database 'Monitoring' process making information about it visible via the *_TABLE_MODIFICATIONS views that the 'GATHER STALE' option of DBMS_STATS.GATHER_SCHEMA_STATS relies on.

I really need to to refresh the stats for the table partition immediately the batch insert has completed because there will be other batch processes querying the new data within minutes of it being inserted.

Thanks for the suggestion though. Much appreciated.

Paul Dixon Received on Tue May 20 2003 - 03:02:42 CDT