--
-- setup script for blog post on understanding AUTO_SAMPLE_SIZE
-- Author: Maria Colgan maria.colgan@oracle.com (@SQLMaria)

set pages 9999
set lines 150
set echo on
spool setup_for_compare.log

-- Check how many rows are in the customers table
Select count(*) from customers;

-- Gather statistics with a manually specified sample size
BEGIN
 dbms_stats.gather_table_stats('SH','CUSTOMERS',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');
END;
/

-- Check the sample size listed for the customers table and its columns
Select  table_name, sample_size
From    user_tables
Where  table_name='CUSTOMERS';

Select  column_name, sample_size
From    user_tab_col_statistics
Where  table_name='CUSTOMERS';

-- Gather statistics on the customers table with histograms being automatically created
BEGIN
 dbms_stats.gather_table_stats('SH','CUSTOMERS');
END;
/

-- Check the sample size listed for the customers table and its columns to what changes with histograms
Select  table_name, sample_size
From    user_tables
Where  table_name='CUSTOMERS';

Select  column_name, sample_size
From    user_tab_col_statistics
Where  table_name='CUSTOMERS';

--