-- #############################################################################################
--
-- %Purpose: ANALYZE table with estimate or compute, 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 < 0 the function returns -1
--                       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.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.sql