oracle10g partition分区表与物化视图materialized view

--创建一个范围分区表

  1  create table t_partition(a int,b int)

  2  partition by range(a)

  3  (partition p1 values less than(10),

  4   partition p2 values less than(maxvalue)

  5* )

SQL> /

Table created.

--插入数据到分区表

SQL> insert into t_partition select level,level from dual connect by level<=1000;

1000 rows created.

SQL> select count(*) from t_partition;

  COUNT(*)

----------

      1000

SQL> commit;

Commit complete.

--收集统计信息

SQL> exec dbms_stats.gather_table_stats(user,'t_partition');

PL/SQL procedure successfully completed.

--查看分区表相关信息

SQL> select table_name,partition_name,num_rows,high_value,partition_position from user_tab_partitions;

TABLE_NAME           PARTITION_NAME                   NUM_ROWS HIGH_VALUE PARTITION_POSITION

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

T_PARTITION          P2                                    991 MAXVALUE                    2

T_PARTITION          P1                                      9 10                          1

SQL> 

SQL> create materialized view mv_t_partition build deferred as select a,b from t_partition;

create materialized view mv_t_partition build deferred as select a,b from t_partition

                                                                          *

ERROR at line 1:

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

SQL> alter table t_partition add primary key(a);

Table altered.

--基于分区表创建物化视图

SQL> create materialized view mv_t_partition build deferred as select a,b from t_partition;

Materialized view created.

--build deferred即在创建物化视图不马上加载数据

SQL> select * from mv_t_partition;

no rows selected

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-766704/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-766704/