ITPub博客

首页 > 数据库 > Oracle > [20151010]物化视图的物理表建成索引组织表

[20151010]物化视图的物理表建成索引组织表

原创 Oracle 作者:lfree 时间:2015-10-12 10:39:41 0 删除 编辑

[20151010]物化视图的物理表建成索引组织表.txt

--论坛有人问这个问题,自己测试看看是否可行?
http://www.itpub.net/thread-1939707-1-1.html

1.环境:
SCOTT@test01p> @ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0


create table t ( id number CONSTRAINTS pk_t primary key , name varchar2(20));
insert into t  select rownum,'test' from dual connect by level<=10;
commit ;

create materialized view log on t;
create table mv_t ( id number  , name varchar2(20) ,constraint mv_t_iot_pk PRIMARY KEY(id)) ORGANIZATION INDEX;
insert into mv_t select * from t;
commit ;

2.建立物化视图看看。

create materialized view mv_t on prebuilt table refresh fast start with sysdate next sysdate + 1/24/60 as select * from t;
--create materialized view mv_t on prebuilt table refresh fast on commit  as select * from t;

SCOTT@test01p> insert into t values (11,'a');
1 row created.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> update t set name='aaaa' where id=1;
1 row updated.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> delete from t where id=2;
1 row deleted.

SCOTT@test01p> commit ;
Commit complete.


3.等上1分钟:

SCOTT@test01p> select * from mv_t;
        ID NAME
---------- --------------------
         1 aaaa
         3 test
         4 test
         5 test
         6 test
         7 test
         8 test
         9 test
        10 test
        11 a
10 rows selected.

SCOTT@test01p> SELECT JOB, LOG_USER, LAST_DATE, NEXT_DATE, FAILURES FROM USER_JOBS;
       JOB LOG_USER             LAST_DATE           NEXT_DATE             FAILURES
---------- -------------------- ------------------- ------------------- ----------
        22 SCOTT                2015-10-10 22:27:14 2015-10-10 22:28:14          0

--数据已经同步,1点问题都没有。

--上面我采用先建表,然后on prebuilt table的方式来建立materialized view。
--补充采用直接建立的方式看看:

SCOTT@test> drop materialized view mv_t;
Materialized view dropped.

SCOTT@test> drop table mv_t purge ;
Table dropped.

SCOTT@test> create materialized view mv_t ( id  , name ) ORGANIZATION INDEX refresh fast on commit  as select * from t;
Materialized view created.

SCOTT@test> select dbms_metadata.get_ddl( 'TABLE', 'MV_T', user )  c100 from dual;
C100
----------------------------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."MV_T"
   (    "ID" NUMBER,
        "NAME" VARCHAR2(20),
         PRIMARY KEY ("ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
PCTTHRESHOLD 50;
--这样直接建立物化视图是索引组织表。主键也是ID。

SCOTT@test> select dbms_metadata.get_ddl( 'MATERIALIZED VIEW', 'MV_T', user )  c100 from dual;
ERROR:
ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1
--google 发现不能这样执行MATERIALIZED VIEW之间空格换成下划线_。

SCOTT@test> select dbms_metadata.get_ddl( 'MATERIALIZED_VIEW', 'MV_T', user )  c100 from dual;
C100
----------------------------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."MV_T" ("ID", "NAME")
  ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
PCTTHRESHOLD 50
  BUILD IMMEDIATE
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  REFRESH FAST ON COMMIT
  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS select * from t;

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2466
  • 访问量
    6275463