oracle10g partition分割槽表與物化檢視materialized view

wisdomone1發表於2013-07-19
OracleZedView
--建立一個範圍分割槽表
  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章