1)partition table example01 exchange partition

1@@@@Moving Data from staging table into fact table. @@@ @@@<1>create tbs for index and table. @@@ @@@I use 8k a block, 8k*8=64k, 8k*5=40k, so I use 8 blocks for table storage @@@and use 5 blocks for index storage. SYS@ocp> ed   1  CREATE TABLESPACE sales_q1_2005   2  DATAFILE '/u01/app/oracle/oradata/OCP/sales_q1_2005.dbf'   3  SIZE 5M AUTOEXTEND ON   4  DEFAULT STORAGE (initial 64k next 64k pctincrease 0   5*              maxextents unlimited) SYS@ocp> / Tablespace created. SYS@ocp> ed   1  CREATE TABLESPACE sales_q1_2005_idx   2  DATAFILE '/u01/app/oracle/oradata/OCP/sales_q1_2005_idx.dbf'   3  SIZE 3M AUTOEXTEND ON   4  DEFAULT STORAGE (initial 40k next 40k pctincrease 0   5*              maxextents unlimited) SYS@ocp> / Tablespace created. @@@ @@@<2>add table partition. @@@ SYS@ocp> ed   1  ALTER TABLE sh.sales ADD PARTITION sales_q1_2005   2  VALUES LESS THAN (to_date('01-JAN-2005','dd-mon-yyyy'))   3  PCTFREE 0 PCTUSED 99   4  STORAGE (initial 64k next 64K pctincrease 0)   5* TABLESPACE sales_q1_2005 SYS@ocp> / Table altered. @@@ @@@<3>create a staging table for moving data. @@@ SYS@ocp> create table sh.staging_table as select * from sh.sales where 1 != 1; Table created. SYS@ocp> insert into sh.staging_table 2> values(987,987,to_date('15-AUG-2004','dd-mon-yyyy'),987,987,987,987); 1 row created. SYS@ocp> insert into sh.staging_table 2> values(987,987,to_date('15-AUG-2004','dd-mon-yyyy'),987,987,987,987); 1 row created. SYS@ocp> commit; Commit complete. SYS@ocp> select * from sh.staging_table;    PROD_ID    CUST_ID TIME_ID         CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD ---------- ---------- ------------------ ---------- ---------- ------------- -----------        987      987 15-AUG-04         987       987         987         987        987      987 15-AUG-04         987       987         987         987 @@@ @@@<4>moving new data to new partition, and inspect. @@@ @@@Exchange is meaning to exchange the data between two tables. @@@And the date key column must be corresponding with each other. SYS@ocp> ed   1  ALTER TABLE sh.sales   2  EXCHANGE PARTITION sales_q1_2005 WITH TABLE sh.staging_table   3* WITHOUT VALIDATION SYS@ocp> / Table altered. SYS@ocp> select * from sh.staging_table; no rows selected SYS@ocp> select * from sh.sales where prod_id=987;                      PROD_ID    CUST_ID TIME_ID         CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD ---------- ---------- ------------------ ---------- ---------- ------------- -----------        987      987 15-AUG-04         987       987         987         987        987      987 15-AUG-04         987       987         987         987 @@@ @@@<5>(supplement)insert direct path, as select @@@ SYS@ocp> INSERT /*+ APPEND */ INTO sh.sales SELECT * FROM sh.staging_table; @@@as select: create table test parallel nologging as select * from tmp_test;

2@@@@maintance after load data.

@@@ @@@<1>rebuild the index for partition table. @@@ SYS@ocp> alter index sh.LOCAL_SALES_IDX rebuild partition sales_q1_2005; Index altered. @@@ @@@<2>gather optimize info. @@@ SYS@ocp> exec dbms_stats.gather_table_stats('sh','sales','sales_q1_2005',dbms_stats.auto_sample_size); PL/SQL procedure successfully completed. <3>verify the Dimensions <4>refresh the materialized views <5>gather optimizer statistics for the materialized views <6>back up the database table, or partition <7>publish the data... skip......