基于物化视图创建分区（分区的物化视图）

基于物化视图创建分区

 物化视图是实际存在的物理表，支持触发器，因此堆表可以使用的方法，物化视图同样适用。有时候对于数据量大的物化视图而言，可以用分区的技术，减少读取数据的范围从而加速查询的速度

1：创建穿件源表插入数据

SQL> create table t3(id number,day_date date);

 

Table created.

 

create procedure p6 is

i number;

begin

  for i in 1 .. 1000 loop

  insert into t3 values(i,trunc(sysdate+i));

  end loop;

  commit;

 

SQL> exec p6();

 

PL/SQL procedure successfully completed.

 

2：创建物化视图日志

SQL> create MATERIALIZED VIEW log on t3;

create MATERIALIZED VIEW log on t3

*

ERROR at line 1:

ORA-12014: table 'T3' does not contain a primary key constraint

 

SQL> alter table t3 add  constraint pk_id primary key(id);

 

Table altered.

 

SQL>  create MATERIALIZED VIEW log on t3 with primary key including new values;

 

Materialized view log created.

 

Note：可以看到物化视图是基于主键

 

3：创建分区的物化视图

SQL> create materialized view mv_t3

  2      partition by range(day_date)

  3        interval(numtoyminterval(1, 'month'))

  4        (

  5        PARTITION P1 VALUES LESS THAN (TO_DATE('20170101', 'YYYYMMDD'))

  6        ) tablespace users

  7          nologging

  8          build immediate refresh FAST

  9          WITH PRIMARY KEY

 10          on demand

 11          enable query rewrite

 12          as SELECT id,day_date from t3;

 

Materialized view created.

 

Note：采用interval自动的间隔分区，如果分区的间隔是Year，Month，用numtoyminterval函数。如果是day，minute，second，用numtodsinterval函数

4：查看执行计划

SQL> set autotrace on;

SQL> set linesize 300

SQL> select * from mv_t3 where day_date=to_date('20180916','YYYYMMDD');

 

        ID DAY_DATE

---------- ---------

       440 16-SEP-18

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 4281692734

 

------------------------------------------------------------------------------------------------

| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |       |     1 |    22 |     5   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE|       |     1 |    22 |     5   (0)| 00:00:01|    22 |    22 |

|*  2 |   MAT_VIEW ACCESS FULL | MV_T3 |     1 |    22 |     5   (0)| 00:00:01 |    22 |    22 |

------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("DAY_DATE"=TO_DATE(' 2018-09-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        599  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL> set autotrace off;

 

Note：可以看到确实用了分区22