为测试compress partition table 建立环境

-- Create table
create table channel
(
  srv_group_id   number not null,
  begin_snaptime date not null,
  card_id        number not null,
  col_data       number default 0
) compress
partition by range (begin_snaptime)
(
  partition p200810 values less than (to_date('2008/11/01','YYYY/MM/DD')) ,
  partition p200811 values less than (to_date('2008/12/01','YYYY/MM/DD')) ,
  partition p200812 values less than (to_date('2009/01/01','YYYY/MM/DD')) ,
  partition p200901 values less than (to_date('2009/02/01','YYYY/MM/DD')) ,
  partition p200902 values less than (to_date('2009/03/01','YYYY/MM/DD')) ,
  partition p200903 values less than (to_date('2009/04/01','YYYY/MM/DD')) ,
  partition pmax values less than (to_date('2015/12/01','YYYY/MM/DD'))
 
)
tablespace USERS
  pctfree 0
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes
create unique index ind_channels_pk on channel (srv_group_id, begin_snaptime, card_id)
  tablespace USERS
  pctfree 0
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

create or replace procedure pro_init_data
is
   v_srv_group_id number;
   v_begin_snaptime date;
   v_card_id number;
   v_col_data number;
begin
     for i in 1..50 loop
         v_srv_group_id := i;
         for j in 1 .. 120 loop
              v_card_id :=j;
              v_begin_snaptime :=to_date('2008/10/01','YYYY/MM/DD');
              dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS'));
              loop                                  
                  v_col_data :=dbms_random.value(1,5000);
                  insert/*+ append*/ into channel values(v_srv_group_id ,v_begin_snaptime , v_card_id , v_col_data );
                  v_begin_snaptime := v_begin_snaptime + 10/1440;
                  exit when v_begin_snaptime > sysdate;
              end loop;
              commit;
         end loop;    
     end loop;
end pro_init_data;
/

exec dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'channel',estimate_percent => 100,degree => 4,cascade => true);