-- To run on Oracle 18c
-- sqlplus hr/hr@//localhost:1521/XEPDB1 @count_rows.sql

SET ECHO ON

SPOOL count_rows.out

WHENEVER SQLERROR EXIT
DECLARE
   l_username user_users.USERNAME%TYPE;
   l_tablecount NUMBER;
BEGIN

   SELECT username
     INTO l_username
     FROM user_users
        ;

   IF l_username != 'HR' THEN
      raise_application_error(-20000, 'Can only be used with HR user');
   END IF;

   SELECT COUNT(*) -- Avoid "ORA-01403: no data found" by counting
     INTO l_tablecount
     FROM user_tables
    WHERE table_name = 'TEST_STATS_ESTIMATE'
        ;

   IF l_tablecount = 1 THEN
      raise_application_error(-20001, 'Table name collision, please rename or drop existing TEST_STATS_ESTIMATE');
   END IF;
END;
/
WHENEVER SQLERROR CONTINUE

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';
COLUMN last_analyzed FORMAT 99999999 HEADING 'Last|Analyzed'
COLUMN num_rows      FORMAT 99999    HEADING 'Num|Rows'
COLUMN table_name    FORMAT a19

-- Start with no stats
EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS(ownname=>'HR');

-- Show num_rows is NULL without stats
SELECT table_name, num_rows, last_analyzed
  FROM user_tables;

SET TIMING ON
-- Gather stats on a single table, timed
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR', tabname=>'DEPARTMENTS', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>TRUE, degree=>DBMS_STATS.DEFAULT_DEGREE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
SET TIMING OFF

-- See that one table now has a num_rows populated
SELECT table_name, num_rows, last_analyzed
  FROM user_tables;

SET TIMI ON
-- Gather stats for the schema, timed
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR', cascade => TRUE, options => 'GATHER AUTO');
SET TIMI OFF

-- All tables have num_rows populated
SELECT table_name, num_rows, last_analyzed
  FROM user_tables;

-- Create a new 10K row table to demo how stats are an estimate, not a count
CREATE TABLE test_stats_estimate AS
SELECT LEVEL id, CAST(DBMS_RANDOM.string('a',100) AS VARCHAR2(100)) txt FROM dual
CONNECT BY LEVEL <= 10000;

-- For a 10% sample for demo, can't use DBMS_STATS.AUTO_SAMPLE_SIZE here as the table is too small and Oracle will pick 100%
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR', tabname=>'TEST_STATS_ESTIMATE', estimate_percent=>10, cascade=>TRUE, degree=>DBMS_STATS.DEFAULT_DEGREE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

-- Note: We know there are 10K rows in this table but due to estimation, a different number is shown
SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'TEST%';

-- What happens if we delete half the rows?
DELETE FROM test_stats_estimate WHERE ROWNUM <= 5000;
COMMIT;
-- Verify the rows are gone
SELECT COUNT(*) FROM test_stats_estimate;
-- Note: no change to num_rows
SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'TEST%';

-- Direct path insert another 10K rows
INSERT /*+ APPEND */ INTO test_stats_estimate
SELECT LEVEL id, CAST(DBMS_RANDOM.string('a',100) AS VARCHAR2(100)) txt FROM dual
CONNECT BY LEVEL <= 10000;
COMMIT;
-- Note: Again no change to num_rows
SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'TEST%';

-- We can even truncate the table and see the num rows is still unchanged
TRUNCATE TABLE test_stats_estimate;
SELECT COUNT(*) FROM test_stats_estimate;
SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name LIKE 'TEST%';

-- Modifications are tracked in user_tab_modifications
-- Th ability to turn off monitoring (ALTER TABLE x NOMONITORING) was deprecated in 11g
COLUMN inserts   FORMAT 99999
COLUMN updates   FORMAT 99999
COLUMN deletes   FORMAT 99999
COLUMN truncated FORMAT a9
SELECT table_name, inserts, updates, deletes, truncated FROM user_tab_modifications;

-- The user_tab_modifications details are summarized into stale_stats
COLUMN stale_stats FORMAT a11
SELECT table_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics;

-- Ask Oracle to gather stale stats
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (ownname => 'HR', cascade => TRUE, options => 'GATHER AUTO');
-- Note: Accurate num_rows estimate
SELECT table_name || NULLIF(' : ' || partition_name,' : ') table_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics;


DROP TABLE test_stats_estimate;

EXIT