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