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

connect oe/oe

set pages 9999
set line s150
set echo on
column table_name format a20
column name       format a20
column column_name format a20
spool setup_for_incremental.log

-- Create a copy of the eo.orders table called orders2 that is partitioned
drop table orders2 purge;

 CREATE TABLE "OE"."ORDERS2"
 PARTITION BY RANGE ("ORDER_DATE")
 (PARTITION "ORDERS_FEB_2007"  VALUES LESS THAN (TO_DATE('2007-03-01', 'YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2007"  VALUES LESS THAN (TO_DATE('2007-04-01','YYYY-MM-DD')),
  PARTITION "ORDERS_APR_2007"  VALUES LESS THAN (TO_DATE('2007-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2007"  VALUES LESS THAN (TO_DATE('2007-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2007"  VALUES LESS THAN (TO_DATE('2007-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2007"  VALUES LESS THAN (TO_DATE('2007-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2007"  VALUES LESS THAN (TO_DATE('2007-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2007"  VALUES LESS THAN (TO_DATE('2007-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2007"  VALUES LESS THAN (TO_DATE('2007-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2007"  VALUES LESS THAN (TO_DATE('2007-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2007"  VALUES LESS THAN (TO_DATE('2008-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2008"  VALUES LESS THAN (TO_DATE('2008-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2008"  VALUES LESS THAN (TO_DATE('2008-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2008"  VALUES LESS THAN (TO_DATE('2008-04-01','YYYY-MM-DD' )),
  PARTITION "ORDERS_APR_2008"  VALUES LESS THAN (TO_DATE('2008-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2008"  VALUES LESS THAN (TO_DATE('2008-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2008"  VALUES LESS THAN (TO_DATE('2008-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2008"  VALUES LESS THAN (TO_DATE('2008-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2008"  VALUES LESS THAN (TO_DATE('2008-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2008"  VALUES LESS THAN (TO_DATE('2008-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2008"  VALUES LESS THAN (TO_DATE('2008-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2008"  VALUES LESS THAN (TO_DATE('2008-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2008"  VALUES LESS THAN (TO_DATE('2009-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2009"  VALUES LESS THAN (TO_DATE('2009-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2009"  VALUES LESS THAN (TO_DATE('2009-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2009"  VALUES LESS THAN (TO_DATE('2009-04-01','YYYY-MM-DD' )),
  PARTITION "ORDERS_APR_2009"  VALUES LESS THAN (TO_DATE('2009-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2009"  VALUES LESS THAN (TO_DATE('2009-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2009"  VALUES LESS THAN (TO_DATE('2009-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2009"  VALUES LESS THAN (TO_DATE('2009-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2009"  VALUES LESS THAN (TO_DATE('2009-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2009"  VALUES LESS THAN (TO_DATE('2009-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2009"  VALUES LESS THAN (TO_DATE('2009-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2009"  VALUES LESS THAN (TO_DATE('2009-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2009"  VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2010"  VALUES LESS THAN (TO_DATE('2010-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2010"  VALUES LESS THAN (TO_DATE('2010-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2010"  VALUES LESS THAN (TO_DATE('2010-04-01','YYYY-MM-DD' )),
  PARTITION "ORDERS_APR_2010"  VALUES LESS THAN (TO_DATE('2010-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2010"  VALUES LESS THAN (TO_DATE('2010-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2010"  VALUES LESS THAN (TO_DATE('2010-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2010"  VALUES LESS THAN (TO_DATE('2010-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2010"  VALUES LESS THAN (TO_DATE('2010-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2010"  VALUES LESS THAN (TO_DATE('2010-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2010"  VALUES LESS THAN (TO_DATE('2010-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2010"  VALUES LESS THAN (TO_DATE('2010-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2010"  VALUES LESS THAN (TO_DATE('2011-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2011"  VALUES LESS THAN (TO_DATE('2011-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2011"  VALUES LESS THAN (TO_DATE('2011-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2011"  VALUES LESS THAN (TO_DATE('2011-04-01','YYYY-MM-DD' )),
  PARTITION "ORDERS_APR_2011"  VALUES LESS THAN (TO_DATE('2011-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2011"  VALUES LESS THAN (TO_DATE('2011-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2011"  VALUES LESS THAN (TO_DATE('2011-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2011"  VALUES LESS THAN (TO_DATE('2011-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2011"  VALUES LESS THAN (TO_DATE('2011-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2011"  VALUES LESS THAN (TO_DATE('2011-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2011"  VALUES LESS THAN (TO_DATE('2011-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2011"  VALUES LESS THAN (TO_DATE('2011-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2011"  VALUES LESS THAN (TO_DATE('2012-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2012"  VALUES LESS THAN (TO_DATE('2012-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2012"  VALUES LESS THAN (TO_DATE('2012-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2012"  VALUES LESS THAN (TO_DATE('2012-04-01','YYYY-MM-DD')),
  PARTITION "ORDERS_APR_2012"  VALUES LESS THAN (TO_DATE('2012-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2012"  VALUES LESS THAN (TO_DATE('2012-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2012"  VALUES LESS THAN (TO_DATE('2012-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2012"  VALUES LESS THAN (TO_DATE('2012-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2012"  VALUES LESS THAN (TO_DATE('2012-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2012"  VALUES LESS THAN (TO_DATE('2012-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2012"  VALUES LESS THAN (TO_DATE('2012-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2012"  VALUES LESS THAN (TO_DATE('2012-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2012"  VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD'))
  )
 AS SELECT ORDER_ID, to_date(to_char(ORDER_DATE, 'DD-MON-YYYY, HH24:MI:SS'), 'DD-MON-YYYY, HH24:MI:SS') ORDER_DATE,
           ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID
 FROM OE.ORDERS;

-- Enable incremental statistics on the Orders2 table
BEGIN
 dbms_stats.set_table_prefs('OE','ORDERS2','INCREMENTAL','TRUE');
END;
/

--Check that the preference was set correctly
SELECT dbms_stats.get_prefs('INCREMENTAL','OE','ORDERS2')
FROM   dual;

-- Gather statistics on the ORDERS2 table
BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/

-- Check the last_analyzed date for the ORDERS2 table and its partitions
SELECT table_name, to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS')  last_analyze`
FROM   user_tables
WHERE   table_name='ORDERS2';

SELECT   partition_name,  to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS') last_analyze
FROM     user_tab_partitions
WHERE    table_name='ORDERS2'
ORDER BY partition_position;

-- Check the actual column stats
SELECT column_name, num_distinct, num_nulls
FROM   user_tab_col_statistics
WHERE   table_name='ORDERS2';

-- Check we actually did do an incremental stats gather

SELECT o.name, decode(bitand(h.spare2, 8), 8, 'yes', 'no') incremental
FROM   sys.hist_head$ h, sys.obj$ o
WHERE  h.obj# = o.obj#
AND    o.name = 'ORDERS2'
AND    o.subname is null;

--Now lets make a change to the data in the ORDERS2 table
UPDATE orders2
SET    order_status=80
WHERE  order_id=2459;

commit;

-- We also need to simulate a load into the ORDERS2 table
INSERT /*+ APPEND */ INTO orders2
SELECT * FROM ext_orders;

commit;

-- Regather statistics on the ORDERS2 table
BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/

-- lets check the last_analyzed date for the ORDERS2 table and its paritions
SELECT table_name, to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS') last_analyze
FROM   user_tables
WHERE   table_name='ORDERS2';

SELECT   partition_name, to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS') last_analyze
FROM     user_tab_partitions
WHERE    table_name='ORDERS2'
ORDER BY partition_position;
--