ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g partition分区表与物化视图materialized view

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

原创 Linux操作系统 作者:wisdomone1 时间:2013-07-19 15:18:19 0 删除 编辑
--创建一个范围分区表
  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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2150
  • 访问量
    11861131