Re: Identifying table partition name

"Paul Dixon" <antispam_at_bt.com> wrote in message news:<bab1bt$rnq$1_at_pheidippides.axion.bt.co.uk>...
> I am trying to write a generic procedure, based on
> dbms_stats.gather_table_stats, to gather fresh table stats for a single
> partition of any (date) range partioned table. I plan to run the procedure
> immediately after an overnight batch insert of new day's data into various
> partitioned tables. My problem is that dbms_stats needs to be supplied with
> the partition name if I want to limit the stats refresh to just one
> partition.
>
> From the date value for my new day's data is there a simple way of
> identifying the name of the table partition that would hold it? (RDBMS is
> Oracle 8.1.7.0 Enterprise Edition on HPUX).
>
> I've searched the documentation for the built in PL/SQL packages but haven't
> found anything that does this. I've also looked at the USER_TAB_PARTITIONS
> view, and although the information I need could be obtained by scanning
> through the approriate rows in order, and evaluating the expression in the
> high_values column, it doesn't look simple. I'm guessing the query optimiser
> has a better way of doing this!!
>
> Any suggestions or pointers to an easier solution would be very much
> appreciated.
>
>
> Paul Dixon

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 Yang