Test of MV FAST refresh with Sum and group

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 statistics
for 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 sales
with rowid(nobukti,tanggal,deptno,qty)
including new values;

analyze table sales compute statistics
for table for all indexes for all indexed columns;

drop materialized view sales_mv;

create materialized view sales_mv
build immediate
refresh fast
enable query rewrite
as 
select a.location, count(*) as cnt,
count (b.qty) as cnt_qty, sum(b.qty) as sum_qty
from dept a, sales b
where a.deptno = b.deptno
group by a.location;

analyze table sales_mv compute statistics
for 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 Aggregates

In data warehouses, materialized views would normally contain one of the
aggregates shown in Example 2 below. For fast refresh to be possible, the SELECT
list 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