物化檢視分割槽實驗

今天同事需要將原有的霧化試圖做一個分割槽的霧化試圖，可能效率會更快一些，讓幫忙看一下，以下是做的實驗驗證一下，實驗如下：

create table t3 (id number primary key,day_date varchar2(30));
create MATERIALIZED VIEW log on t3 with rowid including new values;
 create materialized view mv_test_id partition by range(day_date)
  (
  PARTITION P1 VALUES LESS THAN (TO_DATE('20170101', 'YYYYMMDD'))
    ) tablespace users
       nologging
       build immediate refresh FAST
        WITH PRIMARY KEY
         on demand
     enable query rewrite
   as SELECT id,day_date from t3;
 

select * from mv_test_id；
alter table  mv_test_id add partition part2 values less than (TO_DATE('20170201', 'YYYYMMDD'));


自動增加分割槽需要定義job  
 
  CREATE OR REPLACE PROCEDURE guan_add_partition
--為一個使用者下所有分割槽表自動增加分割槽.分割槽的列為date型別,分割槽名類似:p200706.
 AS
  v_table_name     VARCHAR2(50);
  v_partition_name VARCHAR2(50);
  v_month          CHAR(6);
  v_add_month_1    CHAR(6);
  v_sql_string     VARCHAR2(2000);
  v_add_month      VARCHAR2(20);
  CURSOR cur_part IS
    SELECT DISTINCT u.table_name, MAX(p.partition_name) max_part_name
      FROM user_tables u, user_tab_partitions p
     WHERE u.table_name = p.table_name
       AND u.partitioned = 'YES'
     GROUP BY u.table_name;
BEGIN
  SELECT to_char(SYSDATE, 'yyyymm') INTO v_month FROM dual;
  SELECT to_char(add_months(SYSDATE, 1), 'yyyymm')
    INTO v_add_month_1
    FROM dual;
  SELECT to_char(add_months(trunc(SYSDATE, 'mm'), 2), 'yyyy-mm-dd')
    INTO v_add_month
    FROM dual;
  OPEN cur_part;
  LOOP
    FETCH cur_part
      INTO v_table_name, v_partition_name;
    EXIT WHEN cur_part%NOTFOUND;
    IF to_number(substr(v_partition_name, 2)) <=
       to_number(substr(v_month, 1)) THEN
      v_sql_string := 'alter table ' || v_table_name || ' add partition p' ||
                      v_add_month_1 || ' VALUES LESS THAN ( to_date(''' ||
                      v_add_month ||
                      ''',''yyyy-mm-dd'') ) tablespace users';
      EXECUTE IMMEDIATE v_sql_string;
    ELSE
      NULL;
    END IF;
  END LOOP;
  CLOSE cur_part;
END;

定義schedule，每個月月初呼叫建立分割槽儲存過程。