From StephenAndert@firsthealth.com Thu, 03 Jan 2002 07:29:32 -0800
From: "Stephen Andert" <StephenAndert@firsthealth.com>
Date: Thu, 03 Jan 2002 07:29:32 -0800
Subject: Re: Analyzing partitioned tables
Message-ID: <F001.003E5E2B.20020103070528@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain



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 AndertScottsdale, Arizona
 
>>> rgramolini@tax.state.vt.us 01/03/02 06:00AM 
>>>Good morning everyone,Can anyone give me the command to 
analyze a partitioned tabled?  To analyzethis table unpartitioned I 
use:  analyze table schema.table_name computestatistics;I am 
partitioning it into 6 partitions and want to compute statistics oneach 
partition.Thanks in advance,Ruth-- Please see the 
official ORACLE-L FAQ: <A 
href="http://www.orafaq.com/">http://www.orafaq.com-- Author: Ruth 
Gramolini  INET: rgramolini@tax.state.vt.usFat City Network 
Services    -- (858) 538-5051  FAX: (858) 538-5051San 
Diego, California        -- Public Internet 
access / Mailing 
Lists--------------------------------------------------------------------To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).