-- PURPOSE: ANALYZE table with estimate or compute, depending on table size, se e 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