[20120809]学习物化视图(补充).txt

[20120809]学习物化视图(补充).txt
1.测试环境:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table sales as select * from sh.sales;
insert into sales select * from sales;
insert into sales select * from sales;
insert into sales select * from sales;


CREATE MATERIALIZED VIEW LOG ON "SCOTT"."SALES" WITH ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES;


drop MATERIALIZED VIEW SCOTT.MV1;


CREATE MATERIALIZED VIEW SCOTT.MV1


 REFRESH FAST on commit 


 WITH ROWID


 enable QUERY REWRITE AS


 SELECT SCOTT.SALES.CHANNEL_ID C1,


 SUM("SCOTT"."SALES"."AMOUNT_SOLD") M1,


 COUNT("SCOTT"."SALES"."AMOUNT_SOLD") M2,


 COUNT(*) M3


 FROM SCOTT.SALES


 GROUP BY SCOTT.SALES.CHANNEL_ID;




2.开始测试:


SQL> set timing on


SQL> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)


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


         2        210770739    2064200


         4        109654416     947328


         3        463002086    4322624


         9       2219410.08      16592


Elapsed: 00:00:00.00


SQL> @dpc


PLAN_TABLE_OUTPUT


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


SQL_ID  crucrsdqdrm0b, child number 0


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


select channel_id,sum(amount_sold),count(*) from sales group by


channel_id


Plan hash value: 2958490228


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


| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|


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


|   0 | SELECT STATEMENT             |      |        |     3 (100)|


|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|


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


Note


-----


   - Warning: basic plan statistics not available. These are only collected when:


       * hint 'gather_plan_statistics' is used for the statement or


       * parameter 'statistics_level' is set to 'ALL', at session or system level




20 rows selected.


Elapsed: 00:00:00.02


--可以发现由于使用了物化视图,执行很快!


3.修改记录看看


回话1执行如下(不提交):


SQL1> update sales set amount_sold=amount_sold*1 where rownum


1 row updated.




回话2执行如下:


SQL2> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)


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


         2        210770739    2064200


         4        109654416     947328


         3        463002086    4322624


         9       2219410.08      16592


SQL2> @dpc


PLAN_TABLE_OUTPUT


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


SQL_ID  crucrsdqdrm0b, child number 0


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


select channel_id,sum(amount_sold),count(*) from sales group by


channel_id


Plan hash value: 2958490228


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


| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|


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


|   0 | SELECT STATEMENT             |      |        |     3 (100)|


|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|


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


Note


-----


   - Warning: basic plan statistics not available. These are only collected when:


       * hint 'gather_plan_statistics' is used for the statement or


       * parameter 'statistics_level' is set to 'ALL', at session or system level


20 rows selected.


--由于没有提交,回话执行访问mview.


回到回话1执行:


SQL1> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)


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


         2        210770739    2064200


         4        109654416     947328


         3        463002086    4322624


         9       2219410.08      16592


Elapsed: 00:00:01.94


SQL1> @dpc


PLAN_TABLE_OUTPUT


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


SQL_ID  crucrsdqdrm0b, child number 1


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


select channel_id,sum(amount_sold),count(*) from sales group by


channel_id


Plan hash value: 2895541888


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


| Id  | Operation          | Name  | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |


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


|   0 | SELECT STATEMENT   |       |        |  9972 (100)|       |       |          |


|   1 |  HASH GROUP BY     |       |      4 |  9972   (4)|   899K|   899K| 2757K (0)|


|   2 |   TABLE ACCESS FULL| SALES |   7350K|  9734   (1)|       |       |          |


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


--可以发现,这种方式由于事务没有提交,mview也无法刷新,这种仅仅通过访问sales获得结果.


--这样访问时间变长.接着提交事务.


SQL1> commit;


SQL1> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)


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


         2        210770739    2064200


         4        109654416     947328


         3        463002086    4322624


         9       2219410.08      16592


Elapsed: 00:00:01.88


SQL> @dpc


PLAN_TABLE_OUTPUT


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


SQL_ID  crucrsdqdrm0b, child number 1


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


select channel_id,sum(amount_sold),count(*) from sales group by


channel_id


Plan hash value: 2895541888


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


| Id  | Operation          | Name  | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |


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


|   0 | SELECT STATEMENT   |       |        |  9972 (100)|       |       |          |


|   1 |  HASH GROUP BY     |       |      4 |  9972   (4)|   899K|   899K|  850K (0)|


|   2 |   TABLE ACCESS FULL| SALES |   7350K|  9734   (1)|       |       |          |


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


--奇怪,一旦出现这种情况后,执行计划不再访问物化视图.切换回话2执行.


SQL2> set timing on


SQL2> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)


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


         2        210770739    2064200


         4        109654416     947328


         3        463002086    4322624


         9       2219410.08      16592


Elapsed: 00:00:01.88


--回话2也一样,执行计划不再访问物化视图.


SQL2> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;


MVIEW_NAME                     LAST_REF LAST_REFRESH_DATE


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


MV1                            FAST     2012-08-09 15:55:39


Elapsed: 00:00:00.00


4.再修改记录并且commit看看.不再重复问题同上.


SQL1> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)


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


         2        210770739    2064200


         4        109654416     947328


         3        463002086    4322624


         9       2219410.08      16592


Elapsed: 00:00:01.81


SQL1> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;


MVIEW_NAME                     LAST_REF LAST_REFRESH_DATE


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


MV1                            FAST     2012-08-09 16:01:19


SQL1> select sql_id,child_number,sql_text from v$sql where sql_id='crucrsdqdrm0b';


SQL_ID        CHILD_NUMBER SQL_TEXT


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


crucrsdqdrm0b            1 select channel_id,sum(amount_sold),count(*) from sales group by channel_id


Elapsed: 00:00:00.00


--可以发现仅仅存在child_number=1. child_number=0的不存在了.难道物化视图没刷新,还是有问题?


5.修改语句如下:


select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


为


Select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


SQL1> Select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)


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


         2        210770739    2064200


         4        109654416     947328


         3        463002086    4322624


         9       2219410.08      16592


Elapsed: 00:00:00.00


SQL1> @dpc


PLAN_TABLE_OUTPUT


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


SQL_ID  aykmr2yy598f0, child number 0


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


Select channel_id,sum(amount_sold),count(*) from sales group by


channel_id


Plan hash value: 2958490228


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


| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|


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


|   0 | SELECT STATEMENT             |      |        |     3 (100)|


|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|


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


--我仅仅改变一个字母,由于这条语句第1次执行没有在shared pool.可以发现可以使用物化视图.


6.接着重复试验看看.


SQL1> update sales set amount_sold=amount_sold*1 where rownum


SQL1> commit;


SQL1> Select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


SQL> CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)


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


         2        210770739    2064200


         4        109654416     947328


         3        463002086    4322624


         9       2219410.08      16592


Elapsed: 00:00:00.00


--只要没有出现在DML的rollback/commit前,该sql语句就没有问题.


6.刷新shared pool看看.


SQL> alter system flush shared_pool;


System altered.


Elapsed: 00:00:00.23


SQL> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;


CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)


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


         2        210770739    2064200


         4        109654416     947328


         3        463002086    4322624


         9       2219410.08      16592


Elapsed: 00:00:00.14


SQL> @dpc


PLAN_TABLE_OUTPUT


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


SQL_ID  crucrsdqdrm0b, child number 0


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


select channel_id,sum(amount_sold),count(*) from sales group by


channel_id


Plan hash value: 2958490228


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


| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|


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


|   0 | SELECT STATEMENT             |      |        |     3 (100)|


|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|


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


Note


-----


   - Warning: basic plan statistics not available. These are only collected when:


       * hint 'gather_plan_statistics' is used for the statement or


       * parameter 'statistics_level' is set to 'ALL', at session or system level




20 rows selected.


Elapsed: 00:00:00.34


SQL> select sql_id,child_number,sql_text from v$sql where sql_id='crucrsdqdrm0b';


SQL_ID        CHILD_NUMBER SQL_TEXT


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


crucrsdqdrm0b            0 select channel_id,sum(amount_sold),count(*) from sales group by channel_id


Elapsed: 00:00:00.01


--可以发现刷新后,一切OK!




				


				
时间： 2024-08-26 07:03:02

		
		


		


	

	
	

		


		
[20120809]学习物化视图(补充).txt的相关文章


								

		

			

                [20120808]学习物化视图.txt
			

		

		

									

				[20120808]学习物化视图.txt 从来没有使用过物化视图,今天学习看看,主要是练习,我的测试基本按照如下链接的例子: http://uhesse.com/2009/07/08/brief-introduction-into-materialized-views/ SQL> select * from v$version ; BANNER ----------------------------------------------------------------------------			

		

	

				

		

			

                [20150629]物化视图刷新atomic_refresh.txt
			

		

		

									

				[20150629]物化视图刷新atomic_refresh.txt --11G物化视图刷新有1个参数atomic_refresh. --如果为false,采用的方式是truncate,再使用/*+ append */ 提示insert.这样redo最少,但是刷新期间无法访问. --如果为true,采用的方式是delete,再insert.这样产生许多redo与undo.这样在刷新期间访问没问题,最多有点慢. --自己做一个测试: 1.建立测试环境: SCOTT@test> @ver1 PORT			

		

	

				

		

			

                [20150610]使用物化视图同步数据.txt
			

		

		

									

				[20150610]使用物化视图同步数据.txt --昨天听别人的一个需求要同步一个表的数据,要求使用golden gate有点小题大作.实际上物化事务就可以了,自己以前做过一些测试,也 --许没做记录,这次做一个记录. 1.建立测试环境: --源数据库10g  10.2.0.4.0  IP=192.168.100.89 --同步表T. create table t ( id number CONSTRAINTS pk_t primary key , name varchar2(20)); in			

		

	

				

		

			

                [20131210]11G的内部视图X$DBGALERTEXT补充.txt
			

		

		

									

				[20131210]11G的内部视图X$DBGALERTEXT补充.txt http://blog.itpub.net/267265/viewspace-775126/ 曾经写过一篇blog,以前我们看alert*.log文件,一般直接进入目录,直接查看.当然也可以通过外部表来访问alert*.log. 而11G开始引入了新的ADR自动诊断仓库特性,默认的Alert.log转换为LOG.XML的形式,默认存放在$ADR_HOME/alert&trace目录下,并且 为日志条目增加了如Level之			

		

	

				

		

			

                [20121101]物化视图与表(Materialized Views and Tables).txt
			

		

		

									

				[20121101]物化视图与表(Materialized Views and Tables).txt 1.建立测试环境: SQL> select * from v$version  where rownum BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0			

		

	

				

		

			

                [20130208]物化视图建立以及删除的问题.txt
			

		

		

									

				[20130208]物化视图建立以及删除的问题.txt SQL> select * from v$version where rownum BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production SQL> create ma			

		

	

				

		

			

                物化视图学习笔记
			

		

		

									

				物化视图 删除表后物化视图日志自动删除 SQL> CREATE MATERIALIZED VIEW LOG ON TT WITH ROWID,SEQUENCE(OBJECT_ID,OBJECT_NAME) INCLUDING NEW VALUES; Materialized view log created. SQL> EXEC DBMS_SNAPSHOT.REFRESH('MV1'); BEGIN DBMS_SNAPSHOT.REFRESH('MV1'); END; * ERROR at l			

		

	

				

		

			

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

		

		

									

				[20151010]物化视图的物理表建成索引组织表.txt --论坛有人问这个问题,自己测试看看是否可行? http://www.itpub.net/thread-1939707-1-1.html 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                        			

		

	

				

		

			

                [20160407]bbed修改文件头2(补充).txt
			

		

		

									

				[20160407]bbed修改文件头2(补充).txt --昨天被别人问一个问题,就是我的测试修改数据文件相应的CHECKPOINT_CHANGE#就ok了.偏移量是block=1的offset=484. --链接 http://blog.itpub.net/267265/viewspace-2075424/ --不过别人问的是这个时间是如何存储的.我以前也做过一些.链接: --http://blog.itpub.net/267265/viewspace-746222/ --我的感觉在11.2