Sunday, March 8, 2009

Script: ANALYZE TABLE with Estimate or Compute Statistic, depending on table size

###############################################################
--
-- %Purpose: This script will help us in ANALYZE table
--                 with estimate or compute statistic, 
--                 depending on table size, see SIGN(n)
--
-- Use:    Any table less than 10 MB in total size has statistics computed
--           while tables larger than 10 MB have statistics estimated.
--           SIGN(n) ==> if n <>
--                       if n = 0 the functions returns 0
--                       if n > 0 the functions returns 1
--
-- #############################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
spool compute_or_estimate_stat.sql
--
SELECT 'ANALYZE TABLE '||owner||'.'||table_name||' '||
       DECODE(SIGN(10485760 - initial_extent),1,'COMPUTE STATISTICS;',
                                    'ESTIMATE STATISTICS;')
FROM   sys.dba_tables
WHERE  owner NOT IN ('SYS','SYSTEM');
/
--
spool off;
set feed on;
@compute_or_estimate_stat.sql

No comments:

Post a Comment