[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;