Test of MV FAST refresh with Sum and group
来源:互联网 发布:巨人网络收购投哪网 编辑:程序博客网 时间:2024/04/30 12:20
DROPMATERIALIZEDVIEWLOGON SCO122.data_level_1b_supplier;
CREATEMATERIALIZEDVIEWLOGON SCO122.data_level_1b_suppliertablespace SCO122_DATA
WITHSEQUENCE,ROWID(product_item_id,org_unit_id,period,category,invoiced_code,buying_country_code,buying_org_unit_id,
reporting_currency_code,base_currency_code,quantity_period,quantity_ytd)
INCLUDINGNEWVALUES;
SELECT *FROM MLOG$_DATA_LEVEL_1B_SUPPLI;
SELECT *FROM rupd$_DATA_LEVEL_1B_SUPPLI;
select *from dba_objectswhere object_namelike'MLOG$_%';
select *from dba_objectswhere object_name='MV_DATA_LEVEL_1B_ALLEYFAST';
SELECT *FROMdata_level_1b_supplierWHEREROWNUM<10;
--'US'
UPDATEdata_level_1b_supplier
SET BUYING_COUNTRY_CODE='USnow'
WHERE DATA_LEVEL_1B_SUPPLIER_SEQ=13415822;
COMMIT;
SELECT *FROM MLOG$_DATA_LEVEL_1B_SUPPLI;
SELECT *FROMdata_level_1b_supplierWHERE DATA_LEVEL_1B_SUPPLIER_SEQ=13415822;
SELECT *FROMdata_level_1b_supplierWHERE product_item_id='180_002000018369'and period=200102;
SELECT *FROM MV_DATA_LEVEL_1B_ALLEYFASTWHERE product_item_id='180_002000018369'and period=200102;
select *from dba_objectswhere object_name='MV_DATA_LEVEL_1B_ALLEYFAST';
select *fromuser_mview_logs;
select *from user_jobswhere Whatlike'%MV_DATA_LEVEL_1B_ALLEYFAST%';
begin
dbms_job.run(3187);
end;
/
selectsysdatefrom dual;--2/29/2016 3:30:42 AM
select *from USER_MVIEWSwhere mview_name='MV_DATA_LEVEL_1B_ALLEYFAST';
DROPMATERIALIZEDVIEW SCO122.MV_DATA_LEVEL_1B_ALLEYFAST;
CREATEMATERIALIZEDVIEW SCO122.MV_DATA_LEVEL_1B_ALLEYFAST(PRODUCT_ITEM_ID,ORG_UNIT_ID,PERIOD,CATEGORY,
INVOICED_CODE,BUYING_COUNTRY_CODE,BUYING_ORG_UNIT_ID,REPORTING_CURRENCY_CODE,
BASE_CURRENCY_CODE,QTYPERIOD,QTYYTD,CNT,QTYPERIODCNT,QTYYTDCNT)
TABLESPACE SCO122_DATA
USINGINDEXTABLESPACE SCO122_DATA
REFRESHFORCE
STARTWITHsysdate
NEXTsysdate+5/(60*24)
WITHPRIMARYKEY
AS
SELECT dl.product_item_id,
dl.org_unit_id,
dl.period,
dl.category,
dl.invoiced_code,
dl.buying_country_code,
dl.buying_org_unit_id,
dl.reporting_currency_code,
dl.base_currency_code,
SUM(dl.quantity_period) QtyPeriod,
SUM(dl.quantity_ytd) QtyYTD,
count(*) cnt,
count(dl.quantity_period) QtyPeriodcnt,
count(dl.quantity_ytd) QtyYTDcnt
FROMdata_level_1b_supplier dl
GROUPBY dl.product_item_id,
dl.org_unit_id,
dl.period,
dl.category,
dl.invoiced_code,
dl.buying_country_code,
dl.buying_org_unit_id,
dl.reporting_currency_code,
dl.base_currency_code;
COMMENTONMATERIALIZEDVIEW SCO122.MV_DATA_LEVEL_1B_ALLEYFASTIS'snapshot table for snapshot SCO122.MV_DATA_LEVEL_1B_ALLEYFAST';
CREATEORREPLACEPUBLICSYNONYM MV_DATA_LEVEL_1B_ALLEYFASTFOR SCO122.MV_DATA_LEVEL_1B_ALLEYFAST;
---debug
CREATE TABLE MV_CAPABILITIES_TABLE
(STATEMENT_ID VARCHAR(30), -- Client-supplied unique statement identifier
MVOWNER VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW
MVNAME VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW
CAPABILITY_NAME VARCHAR(30), -- A descriptive name of the particular
-- capability:
-- REWRITE
-- Can do at least full text match
-- rewrite
-- REWRITE_PARTIAL_TEXT_MATCH
-- Can do at leat full and partial
-- text match rewrite
-- REWRITE_GENERAL
-- Can do all forms of rewrite
-- REFRESH
-- Can do at least complete refresh
-- REFRESH_FROM_LOG_AFTER_INSERT
-- Can do fast refresh from an mv log
-- or change capture table at least
-- when update operations are
-- restricted to INSERT
-- REFRESH_FROM_LOG_AFTER_ANY
-- can do fast refresh from an mv log
-- or change capture table after any
-- combination of updates
-- PCT
-- Can do Enhanced Update Tracking on
-- the table named in the RELATED_NAME
-- column. EUT is needed for fast
-- refresh after partitioned
-- maintenance operations on the table
-- named in the RELATED_NAME column
-- and to do non-stale tolerated
-- rewrite when the mv is partially
-- stale with respect to the table
-- named in the RELATED_NAME column.
-- EUT can also sometimes enable fast
-- refresh of updates to the table
-- named in the RELATED_NAME column
-- when fast refresh from an mv log
-- or change capture table is not
-- possilbe.
POSSIBLE CHARACTER(1), -- T = capability is possible
-- F = capability is not possible
RELATED_TEXT VARCHAR(2000),-- Owner.table.column, alias name, etc.
-- related to this message. The
-- specific meaning of this column
-- depends on the MSGNO column. See
-- the documentation for
-- DBMS_MVIEW.EXPLAIN_MVIEW() for details
RELATED_NUM NUMBER, -- When there is a numeric value
-- associated with a row, it goes here.
-- The specific meaning of this column
-- depends on the MSGNO column. See
-- the documentation for
-- DBMS_MVIEW.EXPLAIN_MVIEW() for details
MSGNO INTEGER, -- When available, QSM message #
-- explaining why not possible or more
-- details when enabled.
MSGTXT VARCHAR(2000),-- Text associated with MSGNO.
SEQ NUMBER);
begin
DBMS_MVIEW.EXPLAIN_MVIEW('MV_DATA_LEVEL_1B_ALLEYFAST');
end;
/
truncatetable mv_capabilities_table;
SELECT *
FROM mv_capabilities_table
WHERE mvname='MV_DATA_LEVEL_1B_ALLEYFAST';
/*
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT Y
*/
This is my script to demonstrated about fast refresh on materialiazed view. I create this script refer from your answer.drop table sales;drop table dept;create table dept(deptno varchar2(10),location varchar2(30),constraints dept$pk primary key(deptno));create materialized view log on dept with rowid (deptno, location)including new values;analyze table dept compute statisticsfor table for all indexes for all indexed columns;create table sales(nobukti varchar2(10), tanggal date, deptno varchar2(10), qty number default 0,constraints sales$fk foreign key(deptno)references dept(deptno));create materialized view log on saleswith rowid(nobukti,tanggal,deptno,qty)including new values;analyze table sales compute statisticsfor table for all indexes for all indexed columns;drop materialized view sales_mv;create materialized view sales_mvbuild immediaterefresh fastenable query rewriteas select a.location, count(*) as cnt,count (b.qty) as cnt_qty, sum(b.qty) as sum_qtyfrom dept a, sales bwhere a.deptno = b.deptnogroup by a.location;analyze table sales_mv compute statisticsfor table for all indexes for all indexed columns;alter session set query_rewrite_enabled = true;alter session set query_rewrite_integrity = enforced;I test this script in Oracle 817. I look that fast refresh does not work in oracle 817.I test again, again and again for 3 days before.I frustation why my materialized view cannot refresh using fast refresh method ?And then, I try to test in Oracle 901...beautifull..My materialized view can fast refresh successfully :)
Materialized Views with Joins and AggregatesIn data warehouses, materialized views would normally contain one of theaggregates shown in Example 2 below. For fast refresh to be possible, the SELECTlist must contain all of the GROUP BY columns (if present), and may contain one or more aggregate functions. The aggregate function must be one of: SUM, COUNT(x), COUNT(*),COUNT(DISTINCT x), AVG, VARIANCE, STDDEV, MIN, and MAX,and the expression to be aggregated can be any SQL value expression.If a materialized view contains joins and aggregates, then it cannot be fast refreshed using a materialized view log. Therefore, for a fast refresh to be possible, only new data can be added to the detail tables and it must be loaded using the direct path method.In 9i the same documentation says:Fast refresh for a materialized view containing joins and aggregates is possible after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE). It can be defined to be refreshed ON COMMIT or ON DEMAND. A REFRESH ON COMMIT, materialized view will be refreshed automatically when a transaction that does DML to one of the materialized views commits. The time taken to complete the commit may be slightly longer than usual when this method is chosen. This is because the refresh operation is performed as part of the commit process. Therefore, this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.so, what you are seeing is to be expected for a JOIN mv with Aggregates -- no fast refresh in 8i (unless you direct path the data), fast refesh = true in 9i.It is a new feature
- Test of MV FAST refresh with Sum and group
- Group and sum array of hashes by date
- How to SUM and GROUP BY of JSON data?
- JavaScript SUM and GROUP BY of JSON data
- create the mview with fast refresh - the lazy way
- Using TUNE_MVIEW and EXPLAIN_REWRITE to enable us to tune MV Fast Refreshes and Query Rewrites
- Test-Driven C# Improve the Design and Flexibility of Your Project with Extreme Programming Techniques
- Test-Driven C# Improve the Design and Flexibility of Your Project with Extreme Programming Techniques
- Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tdb_
- refresh group cmd
- Test of scanf and printf
- Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP
- SPUser with person and group field
- aaa using radius, localdb, with group mapping and default group.
- fast Update, sum operation of 2D matrix
- //使用hibernate,实现group by and sum and count
- How to speed up the MV refresh
- Ubuntu 下配置Nginx + PHP5 (With fast-cgi And xcache)
- C++中虚函数工作原理和(虚)继承类的内存占用大小计算
- PHP 中操作 Redis
- Python中的self和__init()__
- Android笔记(二)SpannableString显示表情
- Android中的静态系统广播和动态系统广播
- Test of MV FAST refresh with Sum and group
- linux命令
- 应用上传不成功
- poj2411Mondriaan's Dream(状态压缩dp)
- 算法集合
- c调用c++函数(包括类成员函数)的方法
- ApplePay申请MerchantID及证书配置
- redis-cli 命令总结
- 双击鼠标HOOK学习