set timing on;
alter session enable parallel dml;
alter session enable parallel ddl;

-- DROP TABLE tpart_demo PURGE ;
CREATE TABLE tpart_demo NOLOGGING PARALLEL 2 COMPRESS
PARTITION BY RANGE (time_id) 
SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4 (
  PARTITION sales_1998 VALUES LESS THAN (to_date('19990101', 'YYYYMMDD')),      
  PARTITION sales_1999 VALUES LESS THAN (to_date('20000101', 'YYYYMMDD')),
  PARTITION sales_2000 VALUES LESS THAN (to_date('20010101', 'YYYYMMDD')),
  PARTITION sales_def  VALUES LESS THAN (to_date('20100101', 'YYYYMMDD')) )
AS SELECT /*+ parallel(s,2) parallel(c,2) */ c.country_id, s.cust_id, s.time_id, s.amount_sold
FROM sh.sales s, sh.customers c
WHERE s.cust_id = c.cust_id
AND time_id < to_date('20010101', 'YYYYMMDD');

SELECT to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') "sysdate-for-create-index-step" FROM dual ;
CREATE BITMAP INDEX bi_tpart_demo_country_id ON tpart_demo ( country_id ) NOLOGGING PARALLEL 2 LOCAL ;

-- run the stats control steps at the end : after 10gR2 during index creation stats are gathered automatically
 
SELECT to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') "sysdate-for-1st-stats-cascade" FROM dual ;
BEGIN 
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'tpart_demo', estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>2, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', granularity=>'ALL', CASCADE=>TRUE); 
END; 
/

-- run the stats control steps at the end : with cascade=>TRUE option index subpartitions are re-analyzed

-- a small ETL demo here : set indexes unusable-load-rebuild the indexes
-- ALTER INDEX BI_TPART_DEMO_COUNTRY_ID MODIFY PARTITION SALES_DEF UNUSABLE;
-- if done ORA-14287 during rebuild after load
-- modify here the sunpartition names as required for your environment
ALTER INDEX BI_TPART_DEMO_COUNTRY_ID MODIFY SUBPARTITION SYS_SUBP113 UNUSABLE ;
ALTER INDEX BI_TPART_DEMO_COUNTRY_ID MODIFY SUBPARTITION SYS_SUBP114 UNUSABLE ;
ALTER INDEX BI_TPART_DEMO_COUNTRY_ID MODIFY SUBPARTITION SYS_SUBP115 UNUSABLE ;
ALTER INDEX BI_TPART_DEMO_COUNTRY_ID MODIFY SUBPARTITION SYS_SUBP116 UNUSABLE ;

-- 10gR2 enabled by default, so no need
-- alter session set skip_unusable_indexes=true;

-- a partition load operation
INSERT /*+ append parallel(t,2) */ INTO tpart_demo t
SELECT /*+ parallel(s,2) parallel(c,2) */ c.country_id, s.cust_id, s.time_id, s.amount_sold
FROM sh.sales s, sh.customers c
WHERE s.cust_id = c.cust_id
AND time_id > to_date('20010101', 'YYYYMMDD');
commit;

SELECT to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') "sysdate-for-rebld-index-subps" FROM dual ;
ALTER INDEX BI_TPART_DEMO_COUNTRY_ID REBUILD SUBPARTITION SYS_SUBP113 parallel 2 ;
ALTER INDEX BI_TPART_DEMO_COUNTRY_ID REBUILD SUBPARTITION SYS_SUBP114 parallel 2 ;
ALTER INDEX BI_TPART_DEMO_COUNTRY_ID REBUILD SUBPARTITION SYS_SUBP115 parallel 2 ;
ALTER INDEX BI_TPART_DEMO_COUNTRY_ID REBUILD SUBPARTITION SYS_SUBP116 parallel 2 ;
-- ALTER INDEX BI_TPART_DEMO_COUNTRY_ID REBUILD PARTITION SALES_DEF nologging parallel 2;
-- ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

-- run the stats control steps at the end : after rebuild like creation index subpartition stats are gathered

SELECT to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') "sysdate-for-2nd-stats-cascade" FROM dual ;
BEGIN 
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'tpart_demo', estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>2, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', granularity=>'ALL', CASCADE=>TRUE, partname=>'sales_def'); 
END; 
/

-- run the stats control steps at the end : with cascade=>TRUE option index subpartitions are again re-analyzed

-- and this is the stats control steps part :)
SELECT dt.table_name,
       num_rows,
       to_char(last_analyzed,
               'dd/mm/yyyy hh24:mi:ss') last_analyzed,
       sample_size
  FROM dba_tables dt
 WHERE table_name = 'TPART_DEMO';

SELECT dtp.table_name,
       dtp.partition_name,
       num_rows,
       to_char(last_analyzed,
               'dd/mm/yyyy hh24:mi:ss') last_analyzed,
       sample_size
  FROM dba_tab_partitions dtp
 WHERE table_name = 'TPART_DEMO';

SELECT dts.table_name,
       dts.partition_name,
       dts.subpartition_name,
       num_rows,
       to_char(last_analyzed,
               'dd/mm/yyyy hh24:mi:ss') last_analyzed,
       sample_size
  FROM dba_tab_subpartitions dts
 WHERE table_name = 'TPART_DEMO';

SELECT dth.table_name,
       column_name,
       endpoint_number,
       endpoint_value,
       endpoint_actual_value
  FROM dba_tab_histograms dth
 WHERE table_name = 'TPART_DEMO';

SELECT di.table_name,
       di.table_name,
       num_rows,
       to_char(last_analyzed,
               'dd/mm/yyyy hh24:mi:ss') last_analyzed,
       sample_size,
       leaf_blocks,
       clustering_factor
  FROM dba_indexes di
 WHERE table_name = 'TPART_DEMO';

SELECT dip.index_name,
       dip.partition_name,
       dip.subpartition_count,
       num_rows,
       to_char(last_analyzed,
               'dd/mm/yyyy hh24:mi:ss') last_analyzed,
       sample_size,
       leaf_blocks,
       clustering_factor
  FROM dba_ind_partitions dip
 WHERE index_name LIKE 'BI_TPART_DEMO%';

SELECT dis.index_name,
       dis.partition_name,
       dis.subpartition_name,
       num_rows,
       to_char(last_analyzed,
               'dd/mm/yyyy hh24:mi:ss') last_analyzed,
       sample_size,
       leaf_blocks,
       clustering_factor
  FROM dba_ind_subpartitions dis
 WHERE index_name LIKE 'BI_TPART_DEMO%';