oracle物化视图快速更新
发布时间:2020-12-12 14:33:36 所属栏目:百科 来源:网络整理
导读:建立测试表 createtableT_tablenologgingasselect*fromdba_objects;createtablet_table_1nologgingasselect*fromdba_tables; --建立日志记录 creatematerializedviewlogonT_tablewithrowid,sequence(object_name,object_type,owner)includingnewvalues;creat
建立测试表 createtableT_tablenologgingasselect*fromdba_objects; createtablet_table_1nologgingasselect*fromdba_tables; --建立日志记录 creatematerializedviewlogonT_tablewithrowid,sequence(object_name,object_type,owner)includingnewvalues; creatematerializedviewlogont_table_1withrowid,sequence(table_name)includingnewvalues; --建立物化视图 creatematerializedviewmv_t_tablenologging refreshfastondemand withrowid STARTWITHTO_DATE('21-08-201710:09:08','DD-MM-YYYYHH24:MI:SS')NEXTSYSDATE+1/(24*60) asselectcount(*),a.object_type,a.ownerfromT_tablea,t_table_1bwherea.object_name=b.table_name groupbya.object_type,a.owner --测试 select*frommv_t_tablewhereowner='SYSTEM'; deletefromt_tablewhereowner='SYSTEM'; insertintot_tableselect*fromdba_objectswhereowner='SYSTEM'; --查看日志生成量 selecta.name,b.valuefromv$statnamea,v$mystatbwherea.statistic#=b.statistic#anda.name='redosize'; --查看刷新日志 SELECTowner,mview_name,last_refresh_scn,last_refresh_date,query,REVISIONFROMdba_mview_analysisWHEREowner='UTF32'; --查看日志记录表,MLOG$为日志记录表,一旦更新完成,此表的内容会被清掉; select*fromMLOG$_T_TABLE select*fromMLOG$_T_TABLE_1; 官方说明参见:http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#REPLN265 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |