Re: Analyzing partitioned tables

[Stephen Andert]

Here is what we use which also incorporates using our own definition of STALE with monitoring on.  It includes indexes as well as partitioned and non-partitioned tables as well as non-monitored tables.   HTH      select 'analyze table ' || '${SCHEMA}.' || a.table_name            || ' compute statistics;'          from dba_tables a, all_tab_modifications b          where  a.owner = upper('${SCHEMA}')          and    b.table_owner = a.owner          and    a.table_name = b.table_name          and    a.partitioned = 'NO'          and    (a.num_rows is null                   or ((b.inserts+b.deletes+b.updates)*100)                      / decode(a.num_rows,0,1,a.num_rows) > ${PERCENTAGE}                   or b.truncated = 'YES')  union      select 'analyze table ' || '${SCHEMA}.' ||            a.table_name || ' partition (' ||            b.partition_name || ') compute statistics;'         from dba_tab_partitions a, all_tab_modifications b         where a.table_owner = upper('${SCHEMA}')         and   b.table_owner = a.table_owner         and   a.table_name = b.table_name         and   a.partition_name = b.partition_name         and (a.num_rows is null                 or ((b.inserts+b.deletes+b.updates)*100)                 / decode(a.num_rows,0,1,a.num_rows) > ${PERCENTAGE}                 or b.truncated = 'YES') union   select 'analyze index ' || '${SCHEMA}.' || index_name ||                    ' compute statistics;'         from dba_indexes         where table_owner=upper('${SCHEMA}' )         and   index_name not in (select distinct index_name                                 from dba_part_indexes                                 where owner=upper('${SCHEMA}')                                 and locality = 'LOCAL')         and table_name in                 (select distinct a.table_name         from dba_tab_partitions a, all_tab_modifications b         where a.table_owner = upper('${SCHEMA}')         and   b.table_owner = a.table_owner         and   a.table_name = b.table_name         and   a.partition_name = b.partition_name         and (a.num_rows is null                 or ((b.inserts+b.deletes+b.updates)*100)                 / decode(a.num_rows,0,1,a.num_rows) > ${PERCENTAGE}                 or b.truncated = 'YES')) union      select 'analyze table ' || '${SCHEMA}.' ||             table_name || ' compute statistics;'         from dba_tables         where owner = upper('${SCHEMA}')         and   monitoring = 'NO';   If you have any questions, please feel free to call me or drop me a note.   Stephen Andert Scottsdale, Arizona   >>> [EMAIL PROTECTED] 01/03/02 06:00AM >>> Good morning everyone, Can anyone give me the command to analyze a partitioned tabled?  To analyze this table unpartitioned I use:  analyze table schema.table_name compute statistics; I am partitioning it into 6 partitions and want to compute statistics on each partition. Thanks in advance, Ruth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini   INET: [EMAIL PROTECTED] Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California        -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).  You may also send the HELP command for other information (like subscribing).