MV view
February 20, 2004 - 2pm Central time zone
Reviewer: Jegan from Chennai
Tuning this query...
I need you suggestion and your great input in tuning this query.
Question on tuning this query...
Count of records per table.
---------------------------
CCRT_CALENDAR_DIM - 7304
PORTFOLIOS_DIM - 17
ACCOUNT_MONTHLY_FACT - 39691552 PARTITIONED BY MONTH (has 38 partitions)
This create mv script (attached below) takes 2 hrs 30 minutes and I am doin a COMPLETE REFRESH
since I am using ANALYTICAL FUNCTIONS, I cannot use FAST REFRESH option.
Can you guys throw me some light? Guide me wht are all the parameters that I should look into.
Wht type of indexes will do the trick, How to re-write the sql in a much betterway.
This is test data only. The actual size of ACCOUNT_MONTHLY_FACT is 520 millions rows.
I got 70 more mv to tune. (I got to do this before March 3)
I need your guidance. How to start and proceed from here...
My guts says I am in trouble. I need you guys to help me.
CREATE MATERIALIZED VIEW MV_CREDIT_MEASURES AS
SELECT
B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
(CASE WHEN GROUPING_ID
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_QTR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK) = 0 THEN
TRIM(D.CAL_QTR_STR)
WHEN GROUPING_ID
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_YEAR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK) = 0 THEN
TRIM(D.CAL_YEAR_STR)
ELSE
TRIM(D.CAL_MONTH_STR)
END) VINTAGE_PERIOD,
TRIM(C.CAL_MONTH_STR) REPORT_PERIOD,
A.MONTHS_ON_BOOK,
SUM(INT_AND_FEES) INT_AND_FEES,
SUM(OPENING_BAL) OPENING_BAL,
SUM(CLOSING_BAL) CLOSING_BAL,
SUM(NUM_ACCOUNTS_OPEN) NUM_ACCOUNTS_OPEN,
SUM(NUM_ACCOUNTS_VOLUNTARY_ATTR) NUM_ACCOUNTS_VOLUNTARY_ATTR,
SUM(NUM_ACCOUNTS_INVOLUNTARY_ATTR) NUM_ACCOUNTS_INVOLUNTARY_ATTR,
SUM(PRINCIPAL_CHGOFF) PRINCIPAL_CHGOFF,
SUM(GROSS_CHGOFF) GROSS_CHGOFF,
SUM(NET_CHGOFF) NET_CHGOFF,
SUM(TWO_PLUS_CLOSING_BAL) TWO_PLUS_CLOSING_BAL,
SUM(THREE_PLUS_CLOSING_BAL) THREE_PLUS_CLOSING_BAL,
SUM(FOUR_PLUS_CLOSING_BAL) FOUR_PLUS_CLOSING_BAL
FROM (SELECT
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
A.MONTHS_ON_BOOK,
A.VINTAGE_DATE,
A.MONTH_END_DATE,
(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN
NVL(A.PURCHASE_FIN_CHG, 0) + NVL(A.CASH_ADV_FIN_CHG,0) + NVL(A.ANNUAL_FEE, 0) +
NVL(A.FRONT_END_FEE ,0) + NVL(A.INSURANCE_FEE, 0) + NVL(A.INTERCHANGE_FEE, 0) +
NVL(A.LATE_FEE, 0) + NVL(A.OTHER_FEE, 0) + NVL(A.OVERLIMIT_FEE, 0)
ELSE 0
END) INT_AND_FEES,
LAG(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN NVL(A.MTD_CLOSING_BAL,0) ELSE 0 END)
OVER (PARTITION BY
A.ACCOUNT_ID
ORDER BY
A.MONTH_END_DATE)
OPENING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) CLOSING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN
1
ELSE 0
END) NUM_ACCOUNTS_OPEN,
(CASE WHEN A.ACCOUNT_STATUS_ID = 1 THEN
1
ELSE 0
END) NUM_ACCOUNTS_VOLUNTARY_ATTR,
(CASE WHEN A.ACCOUNT_STATUS_ID = 2 THEN
1
ELSE 0
END) NUM_ACCOUNTS_INVOLUNTARY_ATTR,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 THEN
NVL(A.PURCHASE_CHGOFF, 0) + NVL(A.CASH_ADV_CHGOFF, 0)
ELSE 0
END) PRINCIPAL_CHGOFF,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 THEN
NVL(A.TOTAL_CHGOFF, 0)
ELSE 0
END) GROSS_CHGOFF,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 THEN
NVL(A.TOTAL_CHGOFF, 0) - NVL(A.TOTAL_RECOVERY_AMT, 0) -
(NVL(A.PURCHASE_FIN_CHG_CHGOFF, 0) + NVL(A.CASH_ADV_FIN_CHG_CHGOFF, 0) +
NVL(A.MISC_CHGOFF, 0))
ELSE 0
END) NET_CHGOFF,
(CASE WHEN A.DELINQUENCY_STATUS_ID NOT IN (0, 1, 10) THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) TWO_PLUS_CLOSING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID NOT IN (0, 1, 2, 10) THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) THREE_PLUS_CLOSING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID NOT IN (0, 1, 2, 3, 10) THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) FOUR_PLUS_CLOSING_BAL
FROM ACCOUNT_MONTHLY_FACT A) A,
PORTFOLIOS_DIM B,
CCRT_CALENDAR_DIM C,
CCRT_CALENDAR_DIM D
WHERE A.PORTFOLIO_ID = B.PORTFOLIO_ID
AND A.MONTH_END_DATE = C.CAL_DAY_DT
AND A.VINTAGE_DATE = D.CAL_DAY_DT
GROUP BY GROUPING SETS (
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_MONTH_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK),
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_QTR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK),
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_YEAR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK)
);
Does something else matter redo?
February 22, 2004 - 3am Central time zone
Reviewer: steven from BeiJing . China
but it seems that the redo after nologging table is still mass
SQL> select count(*) from t;
COUNT(*)
----------
28992
SQL> create materialized view tmv as select * from t;
Materialized view created.
SQL> column value new_val V
SQL> set verify off
SQL> select a.name,b.value from v$statname a ,v$mystat b where a.statistic#=b.st
atistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 590228
SQL> exec dbms_mview.refresh( 'TMV', 'C' );
PL/SQL procedure successfully completed.
SQL> select a.name,b.value,TO_char(b.value-&v,'999,999,999,999') diff from v$sta
tname a ,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
DIFF
--------------------------------------------------------------------------------
redo size 2366496
1,776,268
SQL> alter table tmv nologging;
Table altered.
SQL> exec dbms_mview.refresh( 'TMV', 'C' );
PL/SQL procedure successfully completed.
SQL> select a.name,b.value,TO_char(b.value-&v,'999,999,999,999') diff from v$sta
tname a ,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
DIFF
--------------------------------------------------------------------------------
redo size 4148956
1,782,460
SQL> alter table tmv nologging;
table altered
SQL> exec dbms_mview.refresh( 'TMV', 'C' );
PL/SQL procedure successfully completed.
SQL> select a.name,b.value,TO_char(b.value-&v,'999,999,999,999') diff from v$sta
tname a ,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
DIFF
--------------------------------------------------------------------------------
redo size 5899420
1,750,464
Followup February 22, 2004 - 9am Central time zone:
1.7 meg "massive"???
try with a big result set like I did please. there is some overhead that is just going to be
there. show us what happens when you do a non-trivial workload like I did (and use column name
format a15, etc to make the output "fit", its lots easier to read that way)
nologging still does not effect
February 23, 2004 - 4am Central time zone
Reviewer: steven from BeiJing. China
Thank you very much for reply and sorry for obscurity format.
i load more data and repeat again. but it still could see redo increased corresponding with amount
of data;confused about it.do I make something wrong?
sorry for disturb again.
SQL> select count(*) from big_table;
COUNT(*)
----------
463872
SQL> create materialized view big_table_mv as select * from
2 big_table;
Materialized view created.
SQL> col name format a15
SQL> column value new_val V
SQL> set verify off
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.sta
tistic# and a.name='redo size';
NAME VALUE
--------------- ----------
redo size 8375876
SQL> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
PL/SQL procedure successfully completed.
SQL> col diff format a20
SQL> select a.name,b.value,to_char(b.value-&V,'999,999,999,999') diff from v$sta
tname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE DIFF
--------------- ---------- --------------------
redo size 36881836 28,505,960
SQL> alter table big_table_mv nologging;
Table altered.
SQL> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
PL/SQL procedure successfully completed.
SQL> select a.name,b.value,to_char(b.value-&V,'999,999,999,999') diff from v$sta
tname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE DIFF
--------------- ---------- --------------------
redo size 65400332 28,518,496
LOAD MORE ....
SQL> select count(*)from big_table;
COUNT(*)
----------
927744
SQL> drop materialized view big_table_mv;
Materialized view dropped.
SQL> create materialized view big_table_mv as select * from
2 big_table;
Materialized view created.
SQL>
SQL> col name format a15
SQL> column value new_val V
SQL> set verify off
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.sta
tistic# and a.name='redo size';
NAME VALUE
--------------- ----------
redo size 320433004
SQL> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
PL/SQL procedure successfully completed.
SQL>
SQL> select a.name,b.value,to_char(b.value-&V,'999,999,999,999') diff from v$sta
tname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE DIFF
--------------- ---------- --------------------
redo size 377704252 57,271,248
SQL> alter table big_table_mv nologging;
Table altered.
SQL> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
PL/SQL procedure successfully completed.
SQL>
SQL> select a.name,b.value,to_char(b.value-&V,'999,999,999,999') diff from v$sta
tname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE DIFF
--------------- ---------- --------------------
redo size 434843812 57,139,560
does workload is trivial?...
Followup February 23, 2004 - 7am Central time zone:
select force_logging from v$database;
what is the output of that.
it's not for standby
February 23, 2004 - 7pm Central time zone
Reviewer: steven from BeiJing China
SQL> col force_logging format a20
SQL> select force_logging from v$database;
FORCE_LOGGING
--------------------
NO
does some init parameter matter this??
Followup February 24, 2004 - 6am Central time zone:
use sql_trace=true and verify that the refresh is being done via TRUNCATE + INSERT /*+ APPEND */
what version are we looking at here on what platform
feel embarrass
February 25, 2004 - 1am Central time zone
Reviewer: steven
after desc big_table,i discover that there is pk on it.THe Index make mass redo when /*+ append */.
sorry for disturb you because of my carelessness.
SQL> select count(*)from big_table;
COUNT(*)
----------
927744
SQL> select index_name,table_name from user_indexes where table_name='BIG_TABLE_
MV';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------
SYS_C008606 BIG_TABLE_MV
SQL> alter table BIG_TABLE_MV drop primary key;
SQL> select index_name,table_name from user_indexes where table_name='BIG_TABLE_MV';
no rows selected
SQL> alter table BIG_TABLE_MV nologging;
table altered
SQL> column value new_val V
SQL> set verify off
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'redo size'
5 /
NAME VALUE
-----------------------
redo size 16512
SQL> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
SQL> select a.name, b.value, to_char( b.value-&V,
2 '999,999,999,999' ) diff
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and a.name = 'redo size'
6 /
NAME VALUE DIFF
-----------------------------------
redo size 326452 309,940
You are right!Thank you for patience;
I'll never make stupid again
Materialized Views and Statistics
May 10, 2004 - 2pm Central time zone
Reviewer: Lisa Spory from Fairfax, VA
Tom,
I have a number of materialized views that will need to be refreshed following the bi-monthly ETL
to my data warehouse. Many of the materialized views have indexes as well.
Does my ETL script need to explicitly gather statistics for the materialized views and indexes
after executing dbms_mview.refesh()?
Regards,
Lisa
Followup May 10, 2004 - 3pm Central time zone:
I would alter table monitoring and gather stale -- so only the mv's that actually need it get it,
but yes, you need to do it (or not), it is not part of the refresh.
refresh is slow
May 28, 2004 - 9am Central time zone
Reviewer: friend from india
Dear Tom
following are the stats from my system and i am not concluding anything as
looking for your conclusion about mview refresh rate
SQL> select count(*) from mlog$_ab;
COUNT(*)
----------
163127
SELECT aobject_name,a.object_type,tablespace_name,SUM(bytes)
OVER(partition by object_name)/(1024*1024) objsize FROM all_objects
a,user_segments WHERE segment_name=object_name AND a.object_name like
UPPER('MLOG$_ab') AND object_type<>'SYNONYM' ORDER BY
tablespace_name,objsize desc,object_name;
OBJECT_NAME OBJSIZE
---------- ----------
MLOG$_ab 20
Job Name Last Start Last End ST Run Pri/Xit
____________________________ ________________ ________________ __ _______ ___
gca_p_b_refresh_person_vw 05/27/2004 22:48 05/27/2004 23:17 SU 43051591/1
refresh_vw 05/27/2004 22:48 05/27/2004 23:17 SU 43051591/1
select count(*) from mlog$_ab;
COUNT(*)
----------
1989
Hence the mview refresh rate -:
= 162k/29 min = 5.6k/min
it it feasible that 350k record in 15 minutes ????????????
Followup May 28, 2004 - 11am Central time zone:
please
a) post once, in one place.
b) look there for the answser
ORA-12034: materialized view log on "string"."string" younger than last refresh
May 28, 2004 - 11am Central time zone
Reviewer: mvsucks from VA
Tom,
The MV(m) is a join of two based tables(t1, t2), while loading one base table(t1), the error in the
title line occurs. But it says MV log on t2 is younger than last refresh. Could you elabrate a
example of how this happens and how to resolve it.
Best,
Followup May 28, 2004 - 12pm Central time zone:
no example, really "nice name" (very professional, how long did you think about that one?)....
given that the error would occur on a refresh, not a load -- well, it all goes back to "give me an
example"
MView versus permanent table
June 3, 2004 - 8am Central time zone
Reviewer: john from uk
Dear Tom
Please provide few words on
" If wehave MVIew and table ( e.g table xyz based base table ABC and query is same as mview)with
same query then whic one will work better"
Thanx
Followup June 3, 2004 - 8am Central time zone:
a few words...
I don't understand the text in quotes. It is not a complete sentence.
if we have a materialized view and a table (ok).
the table is XYZ (ok).
based base table ABC (not ok, what does a 'base table ABC' have to do with anything? where did it
come from)
and query is same as mview (not ok, what mview, on what?)
if you have a table that contains the same data as a materialized view -- what you have are two
identical tables as a materialized view is a table under the covers. They would be "the same"
Mview versus table
June 3, 2004 - 8am Central time zone
Reviewer: friend from india
Dear Tom i get john words
i think he asked..
1. A table ABC ( comprises same query as mview query)
2. A mview
XYZ i think its a table on which he is creatin mview and from where its creation ABC
Now he is firing queries on table and mview
which will work faster????
Please provde update and answer ;)
Followup June 3, 2004 - 1pm Central time zone:
I'm not willing to be so obliging as to "guess". John would need to clarify really.
your point 1) still doesn't make logical sense to me.
A mview is a table.
If a mview contains the same data as some other table, then what you have are two tables with the
same data -- hence why would either or work faster than the other.
mview versus table
June 3, 2004 - 8am Central time zone
Reviewer: friend from india
Sorry as writting again
1. suppose ABC is a table CTAS from xyz.
2. Mview MV1 created on XYZ with same query used in above statement.
Then which will work better wrt to query performance
mview is fast refresh...
but we can afford to load batch data in ABC also.
What will u recommend?
Regards
Followup June 3, 2004 - 1pm Central time zone:
they are the *same*.
they are both tables.
with the same data.
June 4, 2004 - 1am Central time zone
Reviewer: friend from india
SQL> create materialized view mv1 as select * from emp;
Materialized view created.
SQL> CREATE TABLE PT1 AS SELECT * FROM EMP;
Table created.
SQL> select table_name from user_tables where table_name IN ('MV1','PT1');
TABLE_NAME
------------------------------
MV1
PT1
SQL>
SQL> set lines 300
SQL> set pages 300
SQL> select * from user_tables where table_name IN ('MV1','PT1');
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
IOT_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS
BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ------------------------------
------------------------------ ---------- ---------- ---------- ---------- --------------
----------- ----------- ----------- ------------ ---------- --------------- --- - ----------
---------- ------------
AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE
INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE
DURATION SKIP_COR MON CLUSTER_OWNER DEPENDEN
---------- ---------- ----------- ------------------------- ------------------- ----------
---------- ----- -------- ----------- --------- --- ------------ - - --- ------- -------- --- ---
--------------- -------- --- ------------------------------ --------
MV1 USERS
10 1 255 65536
1 2147483645 YES N
1
1 N ENABLED NO N N NO DEFAULT DISABLED NO NO
DISABLED NO DISABLED
PT1 USERS
10 1 255 65536
1 2147483645 YES N
1
1 N ENABLED NO N N NO DEFAULT DISABLED NO NO
DISABLED NO DISABLED
i Understand this ..
Ok It means if we can afford batch loading then can go for permanent table i,e Pt1
As query performance will be almost same on PT1 as well as MV1 ..
Please confirm
Followup June 4, 2004 - 7am Central time zone:
they are *the same*.
they are *both* tables.
the major difference is that
"select * from emp" could be rewritten by the server as select * from mv1 IF the server thought
that was "cheaper" (it won't in this case -- as emp, mv1, pt1 are all equivalent)
Thanx Sir
June 4, 2004 - 7am Central time zone
Reviewer: friend
Thanx Sir
June 4, 2004 - 7am Central time zone
Reviewer: friend
dbms_mview.refresh gives errors
July 15, 2004 - 8am Central time zone
Reviewer: Varun
I created mv from a view that is based on two different tables. mv created and status is valid.
when i use dbms_mvieew.refresh('VIEW_NAME','C') it gives out error : table or view does not exist.
Any clue what could be going on?
Followup July 15, 2004 - 12pm Central time zone:
need an example.
mview
July 17, 2004 - 8am Central time zone
Reviewer: singa
Hi Tom
I created 2 mviews without any mlog.
as 2 queries were too complex and with lot of joins.
Now what happen?
I need inputs on creation of log , i mean shuould i create logs for all tables involved in
queries.
as no log so no refresh right?
fast refresh materialized view
August 2, 2004 - 1pm Central time zone
Reviewer: Linda Ding from Cherry Hiill, NJ, USA
1. I am trying to create a fast refreshable materialized view to precalcualte cost joins (Join
View) for my data warehousing project.
MLOG was created for each under line tables using the same format as below:
drop materialized view log on DW_MONTHLY_CAL_DIM;
create materialized view log on DW_MONTHLY_CAL_DIM
with rowid(MONTHLY_CAL_KEY,FISCAL_YEAR,FISCAL_MONTH,LM_QUARTER,GOV_YEAR,GOV_QUAR
TER,MONTH_DESCR)
including new values;
....
This Materialized View has 1 Fact table join with 6 Dimension table, the MV definition is as below:
CEATE MATERIALIZED VIEW MV_GL_CUBE_2003_FAST
TABLESPACE GL_MV_D01
NOLOGGING
PARALLEL BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
AS
--select /*+ FIRST ROWS */
select
DW_MONTHLY_CAL_DIM.rowid,
DW_COMPANY_DIM.rowid,
DW_NATURAL_ACCT_DIM.rowid,
DW_FINANCIAL_FACT.rowid,
DW_FY_DIM.rowid,
DW_WBS_DIM.rowid,
DW_CONTRACT_DIM.rowid,
(DW_MONTHLY_CAL_DIM.FISCAL_YEAR*10000+DW_MONTHLY_CAL_DIM.FISCAL_MONTH*100)+1 Per
iod,
DW_COMPANY_DIM.FQA_LVL10_COMPANY_ID FQA_LVL10_COMPANY_ID,
DW_NATURAL_ACCT_DIM.FQA_LVL10_NAT_ACCT_ID FQA_LVL10_NAT_ACCT_ID,
DW_FINANCIAL_FACT.SEGMENT_TYPE_KEY SEGMENT_TYPE_KEY,
DW_MONTHLY_CAL_DIM.GOV_YEAR GOV_YEAR,
DW_MONTHLY_CAL_DIM
.......
from DW_MONTHLY_CAL_DIM DW_MONTHLY_CAL_DIM,
DW_COMPANY_DIM DW_COMPANY_DIM,
DW_NATURAL_ACCT_DIM DW_NATURAL_ACCT_DIM,
DW_FINANCIAL_FACT DW_FINANCIAL_FACT,
DW_FY_DIM DW_FY_DIM,
DW_WBS_DIM DW_WBS_DIM,
DW_CONTRACT_DIM DW_CONTRACT_DIM
where DW_FINANCIAL_FACT.POSTED_GL_YEAR>=2003
and DW_MONTHLY_CAL_DIM.MONTHLY_CAL_KEY=DW_FINANCIAL_FACT.MONTHLY_CAL_KEY
and DW_COMPANY_DIM.COMPANY_KEY=DW_FINANCIAL_FACT.COMPANY_KEY
and DW_WBS_DIM.WBS_KEY=DW_FINANCIAL_FACT.WBS_KEY
and DW_FY_DIM.FISCAL_YEAR_KEY=DW_FINANCIAL_FACT.FISCAL_YEAR_KEY
and DW_CONTRACT_DIM.CONTRACT_KEY=DW_FINANCIAL_FACT.CONTRACT_KEY
and DW_NATURAL_ACCT_DIM.NATURAL_ACCT_KEY=DW_FINANCIAL_FACT.NATURAL_ACCT_KEY
During creation of the MV, I got the following error:
SQL> start Test_Fast.sql
66 /
and "DW_WBS_DIM"."WBS_KEY"="DW_FINANCIAL_FACT"."WBS_KY"
*
ERROR at line 62:
ORA-00904: "ROWID": invalid identifier
I don't know why it complains rowid?
I looked at the data wareshoue guide, it says:
A materialized view containing only joins can be defined to be refreshed ON COMMIT or ON DEMAND. If
it is ON COMMIT, the refresh is performed at commit time of the transaction that does DML on the
materialized view's detail table. Oracle does not allow self-joins in materialized join views.
If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure
that fast refresh can be performed if any of the detail tables change. These additional checks are:
A materialized view log must be present for each detail table.
The rowids of all the detail tables must appear in the SELECT list of the materialized view
query definition.
If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.
However, if there are outer joins, the WHERE clause cannot have any selections. Further, if there
are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
If there are outer joins, unique constraints must exist on the join columns of the inner
table. For example, if you are joining the fact table and a dimension table and the join is an
outer join with the fact table being the outer table, there must exist unique constraints on the
join columns of the dimension table.
I don't have a outer join in the MV definition at all,could you give some hint?
2. I created another MV using the same underline query using complete refresh method, the output
for the following query is:
select owner, name, refresh_method from dba_snapshots
DW_OWNER MV_GL_CUBE_2003 JOIN VIEW
This indicate my Materialized View contains join only right? I man it is not a Aggregate MV or a
Complex MV right?
Thanks a lot for your help.
Followup August 2, 2004 - 2pm Central time zone:
can you provide a scaled back, yet 100% complelete example -- lots of cut and pasting going on
there and I'm not sure what I'm looking at.
PCT
August 3, 2004 - 7pm Central time zone
Reviewer: reader
I was trying to understand Partition Change Tracking. My understanding is that if you have a MV
created on a table that is paritioned and if I performed DDL (Truncate, drop)on base table
partitions, corresponding rows in the MV will be deleted next time when the MV is refreshed. Is
this correct? Thanks.
Followup August 3, 2004 - 8pm Central time zone:
pct does two things
a) limits the rows that go "stale" in the mv. if only rows in a partition ranged on a date between
1-jan-2000 and 31-dec-2000 were modified -- a query against other date ranges on the MV are not
"stale". so it can allow for an mv to be used even if part of it is stale.
b) allows for truncate/drops to happen against the partition and still permit fast refreshes.
so it is about "stale/fresh" and "fast refresh"
10g MV refresh
August 4, 2004 - 11am Central time zone
Reviewer: reader
From,
http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10736/advmv.htm#sthref576
<quote>When a materialized view is partitioned on the partitioning key column or join dependent
expressions of the detail table, it is more efficient to use a TRUNCATE PARTITION statement to
remove one or more partitions of the materialized view during refresh and then repopulate the
partition with new data.<quote>
The above statement confuses me! Suppose the refresh process just needs to insert or update or
delete just a couple of rows in the corresponding MV partition, does oracle do truncate of the MV
partition rather than doing DMLs? Truncate is ok if one has truncated the entire detail table
partition and the next MV refresh would truncate the corresponding MV partition. Any thoughts?
Thanks.
Followup August 4, 2004 - 1pm Central time zone:
i believe they are talking about a complete refresh here -- rather than rebuild the entire MV, just
a single partition of it need be rebuilt.
Materialized view log
August 15, 2004 - 4am Central time zone
Reviewer: A reader
Hi Tom,
I need to create more than two materlaized views on the same table with different results. The
column access to base table is different in both MVs. How do I achieve this ie., use all columns in
one materialized view logs or create more than one materlialized view logs?
Thanks
Followup August 15, 2004 - 9am Central time zone:
No you don't -- you need a single log that merges the needs of both.
Thanks a lot!
August 15, 2004 - 4pm Central time zone
Reviewer: A reader
test
October 28, 2004 - 5pm Central time zone
Reviewer: raman from tn,usa
test
How can I create a materialized view through a Procedure ??
October 28, 2004 - 6pm Central time zone
Reviewer: raman from TN, USA
Hello Tom,
I have a procedure which will accept few parameters to create a base table and materialized view.
When I execute the procedure, base table gets created. But MV will not get created:
Here is the code:
CREATE OR REPLACE PROCEDURE "GISTEAM"."CREATE_MV" (mv_table varchar2, sourcetable varchar2 )
as
Begin
EXECUTE IMMEDIATE ('DROP MATERIALIZED VIEW '||mv_table);
EXECUTE IMMEDIATE ('DROP TABLE '||mv_table||' CASCADE CONSTRAINTS');
EXECUTE IMMEDIATE ('CREATE TABLE '||mv_table||' AS SELECT *FROM TABLE@ANOTHER_DATABASE');
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW TRM_RP_GEOMETRICS_MV
ON PREBUILT TABLE
REFRESH FAST
ON DEMAND
WITH PRIMARY KEY
FOR UPDATE
AS
SELECT * FROM TRIMS_UP.'||sourcetable||'@ANOTHER_DATABASE');
End;
Instead of '*', if I mention column names the MV gets created. But I don't want to use it ...
Please advice me on how can I create a generic procedure to create an MV ?
regards,
-raman
Followup October 28, 2004 - 7pm Central time zone:
care to share what happens -- beyond "it does not work"
How can I create a materialized view through a Procedure ??
October 28, 2004 - 6pm Central time zone
Reviewer: raman from TN,USA
I forgot to include the error I was getting :
ORA-12060: shape of prebuilt table does not match definition query
-raman
Followup October 28, 2004 - 8pm Central time zone:
how about what does the other table look like.
October 29, 2004 - 1pm Central time zone
Reviewer: raman from TN,USA
The table looks like this, it's a spatial table:
SQL> DESC GEOMETRICS@PGIS
Name Null? Type
----------------------------------------- -------- ----------------------------
NBR_TENN_CNTY NOT NULL NUMBER(2)
NBR_RTE NOT NULL VARCHAR2(5)
SPCL_CSE NOT NULL VARCHAR2(1)
CNTY_SEQ NOT NULL NUMBER(2)
RD_BEG_LOG_MLE NOT NULL NUMBER(5,3)
RD_END_LOG_MLE NOT NULL NUMBER(5,3)
ROW_RGHT NUMBER(4)
ROW_LFT NUMBER(3)
ACCESS_CTRL VARCHAR2(1)
DRCT_ONE_WAY NUMBER(1)
TY_TERRAIN VARCHAR2(1)
LAND_USE VARCHAR2(1)
ILLUM VARCHAR2(1)
SPD_LMT NUMBER(3)
SCHL_ZNE VARCHAR2(1)
SPD_LMT_SCHL NUMBER(2)
NBR_LANES NUMBER(2)
UPDT_BY NOT NULL VARCHAR2(20)
UPDT_ON NOT NULL DATE
MSLINK NOT NULL NUMBER(10)
MAPID NOT NULL NUMBER(10)
ID_NUMBER VARCHAR2(10)
THRU_LANES NUMBER(2)
GEOMETRY MDSYS.SDO_GEOMETRY
OBJECTID NOT NULL NUMBER(38)
regards,
-raman
Followup October 29, 2004 - 4pm Central time zone:
and if you CTAS as select * from it -- does the resulting table match the types perfectly .
October 29, 2004 - 2pm Central time zone
Reviewer: raman from TN, USA
Sorry for the typos:
CREATE OR REPLACE PROCEDURE "GISTEAM"."CREATE_MV" (mv_table varchar2,
sourcetable varchar2 )
as
Begin
EXECUTE IMMEDIATE ('DROP MATERIALIZED VIEW '||mv_table);
EXECUTE IMMEDIATE ('DROP TABLE '||mv_table||' CASCADE CONSTRAINTS');
EXECUTE IMMEDIATE ('CREATE TABLE '||mv_table||' AS SELECT *FROM TRIMS_UP.'||
sourcetable||'@ANOTHER_DATABASE');
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW TRM_RP_GEOMETRICS_MV
ON PREBUILT TABLE
REFRESH FAST
ON DEMAND
WITH PRIMARY KEY
FOR UPDATE
AS
SELECT * FROM
TRIMS_UP.'||sourcetable||'@ANOTHER_DATABASE');
End;
regards,
-raman
November 1, 2004 - 5pm Central time zone
Reviewer: raman from tn,usa
Yes, Tom
It would exactly match datatype,columns etc.
November 1, 2004 - 5pm Central time zone
Reviewer: raman from tn,usa
Tom,
There was a primary key missing in my script which I haven't noticed...
Now it's working...
regards,
-raman
big sum mview
November 3, 2004 - 11am Central time zone
Reviewer: markus from austria
hi tom,
we need to build a summery table on top of a range partitioned table (by year and month) which
contains more than 500 million of rows (around 20 mio. per range).
i think, we don't want to use only one partitioned mview because a complete refresh - if needed for
any reason - would last for years propably (4 processor sun v880). afaik, there is no way to
complete refresh just a subset of the mview partitions.
what would you suggest in that case? building multiple mviews by another range and build a simple
union-all view on top of them?
thanks,
markus
Followup November 4, 2004 - 1am Central time zone:
why wouldn't you use a partitioned mview (500 million records -- taking years, I think you are
"overestimating" slightly)
and if you needed to "completely refresh" the mview, you would have the need to completely refresh
the union all views you created as well and they would not be "any faster"
you always have the choice when faced with the complete refresh to:
a) create the summary using any technique you desire -- make it as fast as you want
b) drop the existing mview
c) create the mview on the prebuilt table
To Markus
November 4, 2004 - 7am Central time zone
Reviewer: A reader
On our DW we have partitioned MVs of around this size.
Firstly "complete reresh" is COMPLETE refresh the whole view is replaced - it sounds like what you
want to do is to replace an individual partition.
I would go with:
Build a partitioned summary table (I would also align the partitioning with the parent fact table)
and populate using PL/SQL or whatever. Finally, create the MV on this pre-built table.
To completely refresh a partition you will need to write your own code to truncate the partition
and insert the replacement data. This will need to be followed by a "consider fresh" on the MV.
WARNINGS -
You will need to do a CONSIDER FRESH on the MV after loading it for the first time or you will not
be able to modify any partitions.
If you intend to use FAST refresh on the view to perform routine updates you must do the fast
refresh before you do the partition replacement as CONSIDER FRESH will remove all pending changes
from the logs.
good idea.
November 5, 2004 - 11am Central time zone
Reviewer: markus
hi,
mview on prebuilt partitioned table + truncate/refresh partition + "alter mview consider fresh" is
an interesting idea. i will try this.
regards,
markus
question
February 3, 2005 - 3am Central time zone
Reviewer: arundhuti from Kolkata, WB INDIA
where can i find the redo log files.what is the path.can you give me the syntax for creating log
files for materialized view which uses complete refresh
Followup February 3, 2005 - 1pm Central time zone:
i think you have concepts mixed up.
redo logs are not materialized view logs.
Mview
February 7, 2005 - 12am Central time zone
Reviewer: Arundhuti from Kolkata,W.B India
Yes i mistakenly wrote that,but can you tell me while complete refresh is there any possibility of
creating log files?if yes where can i create that?
regards
Arundhuti
Followup February 7, 2005 - 4am Central time zone:
creating what kind of log files?
Refresh complete every 2 hours for materialized views
February 7, 2005 - 10am Central time zone
Reviewer: sonali from waltham,ma
We use these materialized views for online reports and cubes. So they need to be refreshed every 2
hours. We have about 15 to 20 MV's. Some of them have lots of data ( milltions of rows). Some are
very small.
When we refresh them all in a stored procedure ( using schedule job) with moderate data. It takes
anywhere between 7 to 10 minutes.
Here is a small sample stored procedure
CREATE OR REPLACE Procedure pr_GetActualDL_refresh
IS
strStale varchar2(30);
BEGIN
select STALENESS into strStale from user_mviews where MVIEW_NAME ='MV_MWEBWORK';
If strStale <> 'FRESH' then
dbms_mview.refresh('MV_MWEBWORK','C');
end if;
select STALENESS into strStale from user_mviews where MVIEW_NAME ='MV_PLANNEDHRS';
If strStale <> 'FRESH' then
dbms_mview.refresh('MV_PLANNEDHRS','C');
end if;
COMMIT;
End pr_GetActualDL_refresh;
/
'MV_PLANNEDHRS' is based on 'MV_MWEBWORK'. Where as 'MV_MWEBWORK' only uses actual tables.
They all are created -
CREATE MATERIALIZED VIEW mv_mwebwork
BUILD IMMEDIATE
REFRESH complete
WITH ROWID
DISABLE QUERY REWRITE
AS
using above syntax.. I could not use fast refresh on anyof those because of complex joins.
Is there any other better way to design this so that all views do not need to get refreshed every 2
hours ?? Especially if there is no data change.
Thanks
Sonali
Followup February 7, 2005 - 11am Central time zone:
you are already doing what you asked -- to only refresh them when stale? did I miss something?
Sorry did not phrase my question correctly
February 7, 2005 - 1pm Central time zone
Reviewer: sonali from waltham, ma
I used this check for staleness to reduce the number of views getting complete refreshed. But still
it takes 10 minutes for refresh for moderate data. Some people here have come back to me saying
that these MV's are not good as far as performance goes and we should just write stored procedure,
create database tables and update them instead as these MV's all are COMPLETE refresh, it any ways
does the truncate MV and insert /*append*/.
I feel that MV's are better and I want to make them more faster if I can. I have tuned all the
queries.
I read that there are advantages in partitioning the MV's or using PREBUILT tables. Will it help my
situation ?
If so what are the advantages of using ON PREBUILT TABLE option and when should one use it ?
Thanks
Followup February 7, 2005 - 4pm Central time zone:
partitioning and prebuilt won't do anything for speed of refresh in this case.
prebuilt is good when you have the data already. You don't have the data.
it doesn't have to be truncate/append, and can be delete+insert. You just need to build refresh
groups
Sorry did not phrase my question correctly
February 7, 2005 - 1pm Central time zone
Reviewer: sonali from waltham, ma
I used this check for staleness to reduce the number of views getting complete refreshed. But still
it takes 10 minutes for refresh for moderate data. Some people here have come back to me saying
that these MV's are not good as far as performance goes and we should just write stored procedure,
create database tables and update them instead as these MV's all are COMPLETE refresh, it any ways
does the truncate MV and insert /*append*/.
I feel that MV's are better and I want to make them more faster if I can. I have tuned all the
queries.
I read that there are advantages in partitioning the MV's or using PREBUILT tables. Will it help my
situation ?
If so what are the advantages of using ON PREBUILT TABLE option and when should one use it ?
Thanks
Is this expected behavior?
March 16, 2005 - 3pm Central time zone
Reviewer: Rich from Montreal
Hi Tom,
I was testing the ideas you pointed to me at the very beginning of that question.
1- Diasble the indexes
2- Call Refresh
3- Rebuild indexes with no logging..
Here's the result:
SQL> create materialized view test_mv
2 as select deptno, count(*) cnt from scott.emp
3 where deptno is not null
4 group by deptno;
Materialized view created.
SQL> select index_name from user_indexes where table_name = 'TEST_MV';
INDEX_NAME
------------------------------
I_SNAP$_TEST_MV
SQL> alter index I_SNAP$_TEST_MV disable;
Index altered.
SQL> exec dbms_mview.refresh( 'TEST_MV', 'C' );
BEGIN dbms_mview.refresh( 'TEST_MV', 'C' ); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-30554: function-based index SCOTT.I_SNAP$_TEST_MV is disabled
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
Is this normal behavior? Is there any way to workaround that. We need to refresh all our
Materialized Views with as little archive files generated as possible. Materialized Views are in
nologging mode.
Rich
Followup March 17, 2005 - 8am Central time zone:
well, a complete refresh is going to be "truncate+insert /*+ APPEND */". it'll generate the least
amount of redo.
You'll find that index to be a unique index, they cannot be "skipped"
When a index is created?
March 21, 2005 - 8am Central time zone
Reviewer: Rich from Montreal
Hi Tom,
Can you explain what is the logic underneath index creation for materialized views i.e. when does
Oracle creates one on a materialized view?
Rich
SQL> create materialized view test_mv
2 as select deptno, count(*) cnt from scott.emp
3 where deptno is not null
4 group by deptno;
Materialized view created.
SQL> select index_name from user_indexes where table_name = 'TEST_MV';
INDEX_NAME
------------------------------
I_SNAP$_TEST_MV
Followup March 21, 2005 - 10am Central time zone:
upon creation that one is created.
I think I have the same issue as "rich"
April 7, 2005 - 12pm Central time zone
Reviewer: Mike from England
Hi Tom,
We are using MV's with Oracle streams in a Mobile computing environment (9ir2). The capture
process needs to read all redo generated in order to see if it needs to "capture" those changes.
The problem is that when we refresh our MV's we are generating around 200 - 300 MB of redo, now I
know that isn't a lot in most circumstances, but a notebook computer, single disk, single CPU takes
over an hour to trawl through that redo.
We are of the opinion that as the issue is caused by the redo generated, and that redo does not
need to be "captured" (We tag it so that it is not) if we could avoid generating as much redo as
possible during the refresh of the MV's then this would have a positive knock on effect to the
Streams capture process.
So, I tried some of the postings here, the MV is built "with rowid" and I get a "I_SNAP$_" index on
the MV, however when I try to disable it I get a
SQL> alter index I_SNAP$_TABLE_A disable;
alter index I_SNAP$_TABLE_A disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
which differs to the prior posters. Is there a way around this? perhaps more to the point can we
really, really reduce the amount of redo generated by a refresh to a "pifling" amount?
Thanks as always,
Mike.
Followup April 7, 2005 - 12pm Central time zone:
Even though I cannot reproduce:
ops$tkyte@ORA9IR2> create table t as select * from scott.emp;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view mv
2 as
3 select job, count(*) cnt from t group by job;
Materialized view created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index i_snap$_mv disable;
Index altered.
it doesn't really matter as the refresh would *fail*.
a FULL refresh is the only way I know to reduce it as low as it goes. That should be done with a
a) truncate (no redo)
b) insert /*+ append */ (with nologging, minimal redo)
and if you do a "do it yourself full refresh", you can get it very near zero.
Materialized view approach
April 12, 2005 - 5am Central time zone
Reviewer: Bob from UK
Dear Tom,
I have a design question on the best approach to a problem.
We have a database that is OLTP and used by more than one location.
Each location performs a batch process at the end of the working day - the locations are in
different time zones so the batch processes can start and end at different times.
Following each batch process, a set of complex reports are run.
Currently the reports use a set of complex views:
VIEW_A uses TAB_1, TAB_2
VIEW_B uses TAB_3, TAB_4, VIEW_A (performs unions)
VIEW_C uses TAB_5, TAB_6
VIEW_D uses TAB_7, TAB_8, TAB_9 (performs unions)
VIEW_E uses VIEW_B, VIEW_C, TAB_10
VIEW_F uses VIEW_D, VIEW_E, TAB_11
The reports are currently taking too long to complete.
A solution has been proposed to use materialized views with REFRESH ON DEMAND. Create materialized
views of all the views in question. At the end of each batch process, refresh the materialized
views, run the reports.
However, what would be the impact of say:
Location 1 finishes its batch. Refreshes the materialized views. Starts the report process.
Location 2 finishes before location 1 finishes the its reports. Location 2 refreshes the
materialized views for its reports...
We've considered creating materialized views for each location and then using dynamic sql for the
reports to query the correct materialized view. But this approach seems overly complex, difficult
to maintain and not particularly elegant.
Do you have any suggestions on a best approach.
Many thanks,
Bob
Followup April 12, 2005 - 7am Central time zone:
have you tuned the SQL, really looked at it? Verified that you need expensive "UNION" and not the
much cheaper "UNION ALL" and so on?
Refresh Time
April 21, 2005 - 10am Central time zone
Reviewer: A reader
Hi Tom,
Suppose I'm creating a MV with joining 4-5 tables each with around 2-3 M rows. The MV itself will
result into max 0.05 M rows. The query to create MV takes around 45 minutes.
I want to refresh this MV on a daily basis. & there are a few hundreds row added in the base table
on daily basis.
So my question is does this refresh on demand of MV will take 45 minutes daily or it will just see
any incremental changes made to the table?? & thus will take less time?
Thanks as always.
Followup April 22, 2005 - 8am Central time zone:
depends on how you created the MV whether it does changes only or FULL refreshes.
Materialized views
April 22, 2005 - 4am Central time zone
Reviewer: Anubhuti Varshney from India
ONLY/FULL which is recommended??
April 25, 2005 - 2am Central time zone
Reviewer: A reader
Tom,
As I described you the requirement. that the changes that are expected are only a few hundreds
daily in the base table, ehich refresh is recommended?
Do I need to do the FULL refresh??
Followup April 25, 2005 - 7am Central time zone:
only you can truly answer this.
You say "a few hundreds" in the base table. That sounds "small", it could mean incremental (fast)
refresh.
then again, there might be only 10 rows in the table and then a full would be recommended perhaps.
So, as a number of rows in this table, how many change and as importantly -- how many do not (and
I'm assuming a simple single table materialized view here right)
Got my answer! Thanks !!
April 25, 2005 - 9am Central time zone
Reviewer: A reader
I was satisfied after reading this review
December 9, 2005 - 4am Central time zone
Reviewer: Maria Mundrova from Bulgaria - Sofia
I am glad to say that I was satisfied after reading this review.There are really helpful parts in
it.
Best Regards,
question, truncate MVs
May 24, 2006 - 4am Central time zone
Reviewer: A reader
Hi
I was wondering if we can truncate the MV manually?
Let me explain why I would like to do so. Basically I have two situations, DDL at origin tables and
rowid changes, I have 10 MVs which use rowid replication. (NO PK)
I have a Refresh Group which replicates (read only) a schema in production database to a query only
database. The group contains 140 tables. Since this application is quite fres there are still quite
a few development going which means sometimes I have to add columns to the tables I am replciating.
Since I am using read only replication DDL are not replicated so I should recreate the MV and MV
LOG and readd to the refresh group again so far so good. There is a small problem though, even if I
dont recreate the MV with new columns the refresh process doesnt give errors!
With rowid changes I have a problem, if my MVs are ROWID based whenever rowid in the origin changes
I must perform a complete refresh.
With both situations same question, when I have to perform complete refresh how can I enforce
referential integrity? Since refresh groups cant be used it gives this error:
BEGIN DBMS_REFRESH.REFRESH('COMPANY'); END;
*
ERROR at line 1:
ORA-12057: materialized view "SCOTT"."MV_EMP" is INVALID and must complete refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
So I am forced to perform refresh using dbms_mview which does not guarantee referential integrity.
Thanks
Pinguman
Followup May 24, 2006 - 7am Central time zone:
did you try alter materialized view MV refresh complete?
June 12, 2006 - 10am Central time zone
Reviewer: AJB from UK
I understand how you can take steps to reduce the amount of redo when refreshing materialized views
but don't understand the following:
If I refresh using dbms_refresh.refresh(<group>) then 1.5Gb of redo is generated.
If I schedule the refresh so that a job executes dbms_refresh.refresh(<group>), when I use
dbms_job.run(<job>) then 2.5Gb of redo is generated.
This happens consistently, why does using run_job generate so much more redo?
Followup June 13, 2006 - 10am Central time zone:
I'd need a way to reproduce, but why don't you simply trace it and see what is possibly different.
ways to decrease refresh timing of a Mat view !!
August 16, 2006 - 7am Central time zone
Reviewer: Prashant from UK
Hi Tom,
How can we decrease refresh timings for a Mat view. I have a mat view which takes 2 Hrs to create
and 25 Min to fast refresh, If you could explain ways to decrease the timings would be a great
help.
Following scripts used to create mat view: Total Record:5 Mil
create materialized view TEST refresh fast on demand with rowid as Select /*+ PARALLEL (TEST, 4) */
* from TEST@usdb;
Followup August 16, 2006 - 8am Central time zone:
refresh more frequently :)
it is purely a function of the amount of change made to the base table.
ways to decrease refresh timing of a Mat view !!
August 16, 2006 - 9am Central time zone
Reviewer: A reader
:) .. But I want to use parallel degree during refresh, is this option available...
Followup August 16, 2006 - 9am Central time zone:
complete - sure.
slow by slow (the fast refresh should be called incremental) - it is a process.
ways to decrease refresh timing of a Mat view !!
August 16, 2006 - 9am Central time zone
Reviewer: A reader
Can you please describe below , if possible and which is the parallel degree ?
exec dbms_mview.refresh('TEST','C', '', TRUE, FALSE,4,4,4,FALSE);
Followup August 16, 2006 - 10am Central time zone:
are you asking me to document the parameters to a function call? If so, that is documented
already....
I_SNAP$ index
August 16, 2006 - 9am Central time zone
Reviewer: Jay from New York, NY
I created a table and populated it with the query I used to define a materialized view. Then I
created a materialized view defined on this prebuilt table. Afterward I was able to use this new
materialized view and was able to refresh it by calling dbms_mview.refresh. However, I didn't see
the I_SNAP$ index on this new mview while it exists on my original directly created mview. Can you
tell me why there's I_SNAP$ index in one case but not the other, and what's the purpose of this
index?
Here are my sample scripts:
jz@o10gr2> create materialized view foo_date as
jz@o10gr2> select month_start_date, count(*) days from date_dim
jz@o10gr2> group by month_start_date;
Materialized view created.
jz@o10gr2> select count(*) from foo_date;
COUNT(*)
----------
312
1 row selected.
jz@o10gr2> select * from foo_date where rownum < 2;
MONTH_START_DA DAYS
-------------- ----------
19981001 00:00 31
1 row selected.
jz@o10gr2> select index_name from ind where table_name = 'FOO_DATE';
INDEX_NAME
------------------------------
I_SNAP$_FOO_DATE
1 row selected.
jz@o10gr2> create table foo_date_t as select month_start_date, count(*) days from date_dim
jz@o10gr2> group by month_start_date;
Table created.
jz@o10gr2> create materialized view foo_date_t on prebuilt table
jz@o10gr2> as
jz@o10gr2> select month_start_date, count(*) days from date_dim
jz@o10gr2> group by month_start_date;
Materialized view created.
jz@o10gr2> select count(*) from foo_date_t;
COUNT(*)
----------
312
1 row selected.
jz@o10gr2> select index_name from ind where table_name = 'FOO_DATE_T';
no rows selected
Followup August 16, 2006 - 10am Central time zone:
if you have us instantiate the data, we set up reasonable indexes for our use.
If you do not, we do not.
Normally YOU would index it (by primary key), we copied the primary key over from your date_dim
table when we created the objects.
Re: I_SNAP$ index
August 16, 2006 - 10am Central time zone
Reviewer: Jay from New York, NY
Thanks Tom. The I_SNAP$_FOO_DATE is on SYS_OP_MAP_NONNULL("MONTH_START_DATE"), which is a logical
primary key on foo_date, but month_start_date is not part of the primary key of date_dim (PK column
is date_id), so I don't think Oracle copied it from the detail table.
Are I_SNAP$ indexes used in refresh (either fast or complete) at all? As there is no I_SNAP$ index
on the mview created on prebuilt table, does Oracle use a different way to refresh the prebuilt
mviews (foo_date_t) than with Oracle instantiated mviews (foo_date)?
Followup August 16, 2006 - 11am Central time zone:
trace it if you are really really curious.
but bear in mind, it can, will and probably has changed from release to release.
Efficient mview refresh in 9i?
August 18, 2006 - 12pm Central time zone
Reviewer: Andy Bell from Nottingham,England
Tom,
I am trying to find the most efficient way to incrementally refresh a join-only MVIEW based on a
schema perhaps best described as an 'amputated snowflake', i.e. there is a hierarchy of base tables
with PK and FK constraints, also some 'master' tables that really should be part of the detail
(fact) table. What I am finding is that when a new fact record is inserted, the MVIEW select
statement gets called by the refresh many times, once for each base table. Is is possible to write
a custom refresh that is only triggered by changes to the fact table so that the select is only
called once? (I run into problems with 'DML not legal' etc)
I'm not really interested in capturing changes in master (dimension) tables unless the fact table
is also changed.
Also, how can I make sure that the MVIEW is always available for query rewrite, even during DML
operations in the refresh?
Example follows:
create table detail
nologging
as
select * from all_objects;
alter table detail modify (status varchar2(7) not null);
alter table detail modify (object_type varchar2(18) not null);
create table master_big
nologging
as
select distinct object_name,'0'||object_name as object_name2
from detail;
alter table master_big
add constraint mpk1 primary key(object_name);
create table master_small
nologging
as
select distinct status
from detail;
alter table master_small
add constraint mpk2 primary key(status);
create table master_level1
nologging
as
select distinct object_type,owner
from detail;
alter table master_level1
add constraint mpk3 primary key(object_type,owner);
create table master_level2
nologging
as
select distinct owner
from master_level1;
alter table master_level2
add constraint mpk4 primary key(owner);
alter table detail
add constraint dpk1 primary key(object_id);
alter table detail
add constraint dfk1 foreign key(object_name) references master_big(object_name);
alter table detail
add constraint dfk2 foreign key(status) references master_small(status);
alter table detail
add constraint dfk3 foreign key(object_type,owner) references master_level1(object_type,owner);
alter table master_level1
add constraint mfk1 foreign key(owner) references master_level2(owner);
analyze table detail compute statistics;
analyze table master_big compute statistics;
analyze table master_small compute statistics;
analyze table master_level1 compute statistics;
analyze table master_level2 compute statistics;
create materialized view log on detail
with rowid (object_id,object_name,owner,object_type,status)
including new values;
create materialized view log on master_big
with rowid (object_name,object_name2)
including new values;
create materialized view log on master_small
with rowid (status)
including new values;
create materialized view log on master_level1
with rowid (object_type,owner)
including new values;
create materialized view log on master_level2
with rowid (owner)
including new values;
create materialized view test
nologging
refresh fast with rowid
as
select d.rowid as rowid1,m1.rowid as rowid2,m2.rowid as rowid3,m3.rowid as rowid4,m4.rowid as
rowid5,
d.object_name as object_name0,m1.object_name as object_name1,object_name2,
object_id,m2.status as status,m3.object_type,m4.owner as owner
from detail d,master_big m1,master_small m2,master_level1 m3,master_level2 m4
where d.object_name = m1.object_name
and d.status = m2.status
and d.object_type = m3.object_type
and d.owner = m3.owner
and m3.owner = m4.owner;
analyze table test compute statistics;
-- test master_big insert
insert into master_big
values('Test','This is a test');
update detail set object_id = 40001
where object_id = 30001;
commit;
alter session set sql_trace = true;
exec dbms_mview.refresh('test');
-- test master_level2 insert
insert into master_level2
values('Test');
insert into master_level1
values('Test2','Test');
insert into detail
values('Test','Test3',null,40002,null,'Test0',to_date('18-AUG-06'),to_date('18-AUG-06'),null,'Testin
g',null,null,null);
commit;
alter session set sql_trace = true;
exec dbms_mview.refresh('test');
Followup August 18, 2006 - 4pm Central time zone:
I ran the example, but I'm not really sure what I'm looking for precisely.
But in any case - no, there is no such thing as a "custom refresh". You would have to be using a
technology such as streams in place of the materialized view in order to do that (but then it is
not a materialized view and wouldn't be subject to being used in a query rewrite)
Have you looked at the query rewrite integrity level for the rewrite issue?
MVIEW rewrite issue
August 21, 2006 - 12pm Central time zone
Reviewer: Andy Bell from Nottingham, England
Tom,
Thanks for your prompt reply regarding mview refresh. I am finding an issue with query rewrite in
9iR2, as follows:
During refresh, whether complete or incremental, a query that would normally be re-written is using
the base tables, presumably because rewrite is unavailable. Even when the refresh has finished, the
query continues to be run on the base tables until I flush the shared pool or re-start the
database. This is in spite of using 'STALE_TOLERATED' integrity.
I have tested the refresh scenario using 'dbms_mview.refresh', and a background job submitted as
follows:
CREATE OR REPLACE PROCEDURE test_mview
IS
v_sql VARCHAR2(400);
BEGIN
v_sql := 'ALTER SESSION SET SQL_TRACE=TRUE';
EXECUTE IMMEDIATE v_sql;
EXECUTE IMMEDIATE 'SELECT /*+ REWRITE */ PCID FROM PROPERTY WHERE PID = 1000';
END;
declare j number;
begin
dbms_job.submit(j,'test_mview;',trunc(sysdate) + 13/24,'sysdate + 1/1440');
commit;
end;
/
If I also trace the refresh session, I find that both incremental and complete refreshes use
'delete ...' followed by 'insert ...'. Can you please explain why query rewrite is 'lost' during
the refresh?
(By the way, I'd like to send an example, but I'm struggling a bit with a nice way to generate a
suitable DML load for the refresh. Any ideas?)
Refreshing Materialized view (Snap-Clone Technology)
August 30, 2006 - 12pm Central time zone
Reviewer: Elahe Faghihi from Oakville, ON, Canada
Hi Tom,
In order to get better performance in our CRM data mart, I am going to use Materialized view.
the current ETL process runs at 3:00 am every night in staging server and then Production
server gets refreshed using a Snap-Clone technology. I am not sure by using Snap-Clone, how the
refreshment
process work . All I know is it will drop the current schema and tablespace
and recreate them based on staging server.
My question would be, how I should refresh the Materialized view in production server.
As you see, there are more that one master table in the query. Do I need to refresh the
Materialized view right after refreshing all the master tables in Stating server first? How I can
refresh
it after refreshing master tables in production server
What are my options to update the data in materialized view?
I would appreciate your help in advance.
select
fct.COMPANY,
fct.CRM_PERIOD,
fct.CUSTOMER_ID,
fct.POSITION_ID,
fct.Product_code,
fct.PLAN_DETAILS,
fct.SUGGESTED_DETAILS,
fct.YTD_DETAILS,
fct_corp.segment_code,
fct_corp.segment_type,
fct_corp.sub_segment,
fct_corp.period_date,
fct_corp.Co_Promote_Indicator,
fct_corp.BI_Partner_Indicator,
---
fct_corp.BI_Target_Indicator,
---
fct_corp.segment_brand,
null as employee_id,
null as no_of_details
from
dw_crm_fact_cust_det_summary fct,
(select fct_corp.customer_id,
fct_corp.crm_period,
fct_corp.product_code,
fct_corp.DIVISION_ID,
(CASE WHEN (fct_corp.BICL_TRGT = 'Y' or fct_corp.PARTNER1_TRGT <> 4 ) then ( 'Co-Promote' ) else
( 'Non-Target' ) END) Co_Promote_Indicator,
(CASE WHEN (fct_corp.BICL_TRGT = 'N' and fct_corp.PARTNER1_TRGT = 4 ) then ( 'Non-Target' ) else
(CASE WHEN (fct_corp.BICL_TRGT = 'Y' and fct_corp.PARTNER1_TRGT <> 4 ) then ( 'BI + Partner Target'
) else (CASE WHEN (fct_corp.BICL_TRGT = 'Y' ) then ( 'BI Only Target' ) else ( 'Partner Only
Target' ) END) END) END) BI_Partner_Indicator,
----
fct_corp.BICL_TRGT BI_Target_Indicator,
----
dte.monthly_date period_date,
seg.segment_code,
seg.segment_type,
seg.sub_segment,
seg.SIEBEL_PRODUCT_CODE segment_brand,
fct_corp.no_of_details corporate_details
from
dw_crm_dim_monthly dte,
dw_crm_dim_segmentation seg,
dw_crm_fact_plan_details fct_corp
where
fct_corp.segment_id = seg.segment_id
and fct_corp.crm_period = dte.crm_period
and fct_corp.PLANNED_ACTIVITY_TYPE = 'Corporate'
and fct_corp.record_status = 'Y'
and fct_corp.division_id = 1
--and fct_corp.CRM_PERIOD = '2006'
) fct_corp
where
fct.customer_id = fct_corp.customer_id (+)
and fct.CRM_PERIOD = fct_corp.crm_period (+)
and fct.product_code = fct_corp.product_code (+)
and fct.position_id is null
--and fct.crm_period = '2006'
union
--DETAIL level facts
select
fct.COMPANY,
fct.CRM_PERIOD,
fct.CUSTOMER_ID,
fct.POSITION_ID,
fct.Product_code,
fct.PLAN_DETAILS,
fct.SUGGESTED_DETAILS,
fct.YTD_DETAILS,
fct_det.segment_code,
fct_det.segment_type,
fct_det.sub_segment,
fct_det.period_date,
fct_det.Co_Promote_Indicator,
fct_det.BI_Partner_Indicator,
---
fct_det.BI_Target_Indicator,
---
fct_det.segment_brand,
fct_det.employee_id,
fct_det.no_of_details
from
dw_crm_fact_cust_det_summary fct,
(select fct_det.crm_period,
fct_det.position_id,
fct_det.customer_id,
fct_det.product_code,
fct_det.company,
(CASE WHEN (fct_det.NO_OF_CORP_DETAILS = 0 and fct_det.NO_OF_PARTNER_DETAILS = 0 ) then (
'Non-Target' ) else ( 'Co-Promote' ) end) Co_Promote_Indicator,
(CASE WHEN (fct_det.NO_OF_CORP_DETAILS = 0 and fct_det.NO_OF_PARTNER_DETAILS = 0 ) then (
'Non-Target' ) else ( case when (fct_det.NO_OF_CORP_DETAILS = 1 and not
(fct_det.NO_OF_PARTNER_DETAILS = 0 )) then ( 'BI + Partner Target' ) else (case when ( not
(fct_det.NO_OF_PARTNER_DETAILS = 0 ) ) then ( 'Partner Only Target' ) else ( 'BI Only Target' )
end) end) end) BI_Partner_Indicator,
-----
(CASE WHEN (fct_det.NO_OF_CORP_DETAILS = 1) THEN ('Y') ELSE ('N') END) BI_Target_Indicator,
-----
emp.employee_id,
emp.employee_code,
seg.segment_code,
seg.segment_type,
seg.sub_segment,
seg.SIEBEL_PRODUCT_CODE segment_brand,
fct_det.period_date,
fct_det.no_of_details
from
dw_crm_fact_details fct_det,
dw_crm_dim_employee emp,
dw_crm_dim_segmentation seg
where
fct_det.employee_id = emp.employee_id
and fct_det.segment_id = seg.segment_id
--and fct_det.crm_period = '2006'
and activity_type = 'Professional Call'
and activity_detail_type = 'Detail'
and fct_det.division_id = 1
) fct_det
where
fct.customer_id = fct_det.customer_id (+)
and fct.CRM_PERIOD = fct_det.crm_period (+)
and fct.position_id = fct_det.position_id (+)
and fct.PRODUCT_CODE = fct_det.product_code (+)
and fct.company = fct_det.company (+)
--and fct.crm_period = '2006'
and fct.ytd_details > 0
union
--POSITION level facts
select
fct.COMPANY,
fct.CRM_PERIOD,
fct.CUSTOMER_ID,
fct.POSITION_ID,
fct.Product_code,
fct.PLAN_DETAILS,
fct.SUGGESTED_DETAILS,
fct.YTD_DETAILS,
fct_plan.segment_code,
fct_plan.segment_type,
fct_plan.sub_segment,
fct_plan.period_date,
fct_plan.Co_Promote_Indicator,
fct_plan.BI_Partner_Indicator,
---
fct_plan.BI_Target_Indicator,
---
fct_plan.segment_brand,
null as employee_id,
null as no_of_details
from
dw_crm_fact_cust_det_summary fct,
(select fct_plan.customer_id,
fct_plan.crm_period,
fct_plan.product_code,
fct_plan.position_id,
fct_plan.DIVISION_ID,
(CASE WHEN (fct_plan.BICL_TRGT = 'Y' or fct_plan.PARTNER1_TRGT <> 4 ) then ( 'Co-Promote' ) else
( 'Non-Target' ) END) Co_Promote_Indicator,
(CASE WHEN (fct_plan.BICL_TRGT = 'N' and fct_plan.PARTNER1_TRGT = 4 ) then ( 'Non-Target' ) else
(CASE WHEN (fct_plan.BICL_TRGT = 'Y' and fct_plan.PARTNER1_TRGT <> 4 ) then ( 'BI + Partner Target'
) else (CASE WHEN (fct_plan.BICL_TRGT = 'Y' ) then ( 'BI Only Target' ) else ( 'Partner Only
Target' ) END) END) END) BI_Partner_Indicator,
---
fct_plan.BICL_TRGT BI_Target_Indicator,
---
dte.monthly_date period_date,
seg.segment_code,
seg.segment_type,
seg.sub_segment,
seg.SIEBEL_PRODUCT_CODE segment_brand,
fct_plan.no_of_details planned_details
from dw_crm_dim_monthly dte,
dw_crm_dim_segmentation seg,
dw_crm_fact_plan_details fct_plan
where
fct_plan.segment_id = seg.segment_id
and fct_plan.crm_period = dte.crm_period
and fct_plan.PLANNED_ACTIVITY_TYPE = 'Position'
and fct_plan.record_status = 'Y'
and fct_plan.division_id = 1
--and fct_plan.crm_period = '2006'
) fct_plan
where
fct.customer_id = fct_plan.customer_id (+)
and fct.positioN_id = fct_plan.position_id (+)
and fct.CRM_PERIOD = fct_plan.crm_period (+)
and fct.product_code = fct_plan.product_code (+)
--and fct.crm_period = '2006'
and fct.ytd_details = 0
and fct.plan_details > 0;
-
Followup August 30, 2006 - 5pm Central time zone:
we'd need details on this "snap clone, drop tablespace, zap data in" process.
but it sounds like it would need a complete refresh since the materialized view logs would
obviously not be maintained.
August 31, 2006 - 4pm Central time zone
Reviewer: Elahe Faghihi from Oakville, ON Canada
Hi Tom,
Thanks for your propmt reply. I asked about Snap-Clone process from David who is Network Systems
Analyst. Here is David's explanation about Snap-Clone:
The formal name of the product we are using is Snapview. It is an EMC product that is part of their
Navisphere suite of storage management software. It operates completely within the storage
processors in our Clariion disk array. It sees storage at a block level only; it has no concept of
oracle Tablespaces or operating system datafiles. It sees two storage containers LUNs : One on
the source host the backbox and its clone on the destination host.
In order to refresh the data marts we use two main ingredients Oracle Transportable Tablespace
and Snapview. When the time comes to refresh the data marts here is what happens:
1. the tablespaces and their contents are dropped from the destination host.
2. transportable tablespaces are created on the source host and the metadata export file
resides on the source LUN.
3. The clone LUN on the is stopped and refreshed from the source host and restarted this
the snapview process.
4. The metadata file from the source host is now available on the destination and the
transportable tablespaces are imported into the destination database.
My recommendation is to build the materialized views on the destination host AFTER the snapview
process and transportable tables import are complete.
To Andy Bell, ensuring query rewrite always happen
September 5, 2006 - 10am Central time zone
Reviewer: Gints Plivna from Riga, Latvia
You can do that with two simultaneous materialized views and playing around with statistics on
them.
<shameless plug>
I've explained it rather detailed in my website in article Effective search in a normalized
application that can be found here http://www.gplivna.eu/papers/mat_views_search.htm Look under the heading "Alternate refresh process of materialized views".
</shameless plug>
Gints
Tom, how did you create an mview without an index on it?
October 10, 2006 - 4am Central time zone
Reviewer: Bartek from Poland
Tom,
Going back to your original example (from 2004..) Im not really sure how you created the
materialized view without an index (to minimize redo during complete refreshes). Oracle
documentation says that the mview will be created with one or more index on it:
When you create a materialized view, Oracle Database creates one internal table and at least one
index, and may create one view, all in the schema of the materialized view (from the 10g SQL
Reference Guide)
Regardless whether I create an mview WITH ROWID or WITH PRIMARY KEY, it always ends up having an
index on it.
So, the question is: how to create an mview without an index on it (to minimize redo on refreshes)
or how to disable the one that is automatically created?
Thanks
Bartek
Scenario follows:
SQL> drop table big_table;
Table dropped.
SQL> drop materialized view mv_big_table;
Materialized view dropped.
SQL> create table big_table nologging
SQL> as select * from all_objects;
Table created.
SQL> create materialized view mv_big_table
SQL> REFRESH COMPLETE WITH ROWID
SQL> as select * from big_table;
Materialized view created.
SQL> select table_name, index_name from user_indexes where index_name like '%BIG%';
MV_BIG_TABLE I_SNAP$_MV_BIG_TABLE
--oops, we got an index for free on our mview.. lets try WITH PK
SQL> drop materialized view mv_big_table;
Materialized view dropped.
SQL> alter table big_table
SQL> add constraint big_table_pk primary key(object_id);
Table altered.
SQL> create materialized view mv_big_table
SQL> REFRESH COMPLETE WITH PRIMARY KEY
SQL> as select * from big_table;
Materialized view created.
SQL> select table_name, index_name from user_indexes where index_name like '%BIG%';
BIG_TABLE BIG_TABLE_PK
MV_BIG_TABLE BIG_TABLE_PK1
-- and again we have an index..
--lets try to drop it
SQL> drop index BIG_TABLE_PK1;
SQL> drop index BIG_TABLE_PK1
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
--oh well. Lets see if theres a difference between redo size
--for complete refreshes with the default index
--and with an additional one, like in your original example
SQL> column value new_val V
SQL> select a.name, b.value
SQL> from v$statname a, v$mystat b
SQL> where a.statistic# = b.statistic#
SQL> and a.name = 'redo size';
redo size 4592239152
SQL> exec dbms_mview.refresh('MV_BIG_TABLE','C');
PL/SQL procedure successfully completed.
SQL> column value new_val V
SQL> select a.name, b.value, to_char( b.value-&V, '999,999,999,999' ) diff
SQL> from v$statname a, v$mystat b
SQL> where a.statistic# = b.statistic#
SQL> and a.name = 'redo size';
redo size 4653128296 60,889,144
SQL> create index big_table_idx on big_table(object_name);
Index created.
SQL> exec dbms_mview.refresh('MV_BIG_TABLE','C');
PL/SQL procedure successfully completed.
SQL> column value new_val V
SQL> select a.name, b.value, to_char( b.value-&V, '999,999,999,999' ) diff
SQL> from v$statname a, v$mystat b
SQL> where a.statistic# = b.statistic#
SQL> and a.name = 'redo size';
redo size 4716740760 63,612,464
--about the same as with the default index, which tells me that the default one is a real index
indeed
Followup October 10, 2006 - 8am Central time zone:
actually, things change over time - today in current software, it will do a delete + insert without
the append - so the data doesn't "disappear" during the refresh.
so, today, you won't see the append happening - depending on version.
the infrastructure objects (indexes) should be left "as is" for the correct operation of the
materialized view.
October 10, 2006 - 8am Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
> today in current software, it will do a
> delete + insert without the append
Is it possible to get back to the old behaviour (without setting events) ?
Followup October 10, 2006 - 11am Central time zone:
not that I'm aware of, haven't researched it too deeply.
Any alternative approach to data replication?
October 10, 2006 - 8am Central time zone
Reviewer: Bartek from Poland
Wow, that was a fast answer :D
Well, if these days Oracle will always create an index for me (I'm using 10gR2), regrettably this
makes mviews useless for the data replication purposes.
While I appreciate the new query rewrite capabilities of mviews, the automatic index is causing
havoc for the data replication. The case I'm struggling with is a table with 25 million rows and a
simple mview that does 'select * from..' to create monthly snapshot of that table.
Now, the 'create materialized view build immediate' takes 5 minutes. Refresh complete takes > 2
hours and never finishes (unable to extend the undo tablespace), refresh fast takes about 1 hour.
What is the current best approach to the data replication, if using materialized views is so much
worse these days than CTAS?
Thanks
Bartek
Followup October 10, 2006 - 12pm Central time zone:
I fail to see how the word useless applies at all.
If you just want a copy, you must be using a dblink (which isn't going to be doing the mview
rewrite). No reason you cannot just schedule your own job that
a) truncates
b) insert /*+ APPEND */
into your own table? You are not really using any of the materialized view features here...
October 10, 2006 - 2pm Central time zone
Reviewer: David Aldridge from Colorado Springs, CO
>> Well, if these days Oracle will always create an index for me (I'm using 10gR2), regrettably
this makes mviews useless for the data replication purposes.
Have you tried including the "USING NO INDEX" clause when you create the MV?
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#sthref6783
Followup October 10, 2006 - 8pm Central time zone:
well, there is that of course :)
November 3, 2006 - 5pm Central time zone
Reviewer: A reader
<< actually, things change over time - today in current software, it will do a
delete + insert without the append - so the data doesn't "disappear" during the
refresh.
>>
Tom,
if we refresh using MV in 10g, which deletes on very huge tables which is in millions and insert,
Is it not a slow apporach on mv..
I understand the benefit of "data availability during refresh".. but it comes at the cost of undo
tablespace space problem and performance of mv completion etc.
Whats your comment on this.
Followup November 4, 2006 - 12pm Central time zone:
you are accurate in your observation.
A possible workaround for 10g refresh complete performance?
November 8, 2006 - 10am Central time zone
Reviewer: Ian from Amsterdam
Tom
Very worried about the "new improved" refresh complete method in 10g (delete + insert as opposed to
truncate + insert append). I just benchmarked it and the 9i method is 25% faster and generates 1%
as much redo as the 10g method. But as 88% of the redo comes from the delete could we not just do
truncate table <mv_table> purge materialized view log reuse storage and then do the refresh?
Can you think of any drawback to this approach.
Regards
Ian
Followup November 8, 2006 - 7pm Central time zone:
you could but why have an mv log?
In fact...
November 8, 2006 - 10am Central time zone
Reviewer: Ian from Amsterdam
I guess we could also do the insert append ourselves - and then an alter mv consider fresh. Hmmm.
Better not forget...
November 8, 2006 - 10am Central time zone
Reviewer: Ian from Amsterdam
Better not forget to change the truncate to drop storage. Otherwise the table could get fairly
large. :-)
BTW - fast refresh is not an option for us.
Regards
Ian
Please ignore my last comment.
November 8, 2006 - 11am Central time zone
Reviewer: Ian from Amsterdam
Please ignore my last comment. I was forgetting that the truncate moved the HW mark anyway.
Slinks away looking embarrassed.
you could but why have an mv log?
November 9, 2006 - 6am Central time zone
Reviewer: Ian from Amsterdam
Tom
You said "you could but why have an mv log?". I just put that in for completeness. As it happens we
are an Apps shop so we can't use MV logs anyway. Hence the obsession with the speed of refresh
completes.
But it turns out my test got a bit mixed up - I was using an MV on a prebuilt table - and when I
tested my insert append I failed to notice I had dropped the MV! With the MV in place you cannot do
the insert.
So now you would be looking at (In 10g for a quicker refresh complete) create MV on prebuilt table
and then to refresh - a truncate, drop the MV, insert append, re-create the MV. So you lose most of
the benefits of the MV apart from the query rewrite. But it does generate even less redo than 9i.
On another note - just out of interest - how come deletes generate so much more redo than inserts?
I know the undo for a delete is far bigger than for an insert - and that undo generates it's own
redo - but I would have thought that would be offset by the actual delete statement being smaller
in the redo log than the insert?
Could you explain this? Am I missing something?
Thanks and Regards
Ian
Followup November 9, 2006 - 8am Central time zone:
it is likely all about index maintenance.
No indexes
November 9, 2006 - 12pm Central time zone
Reviewer: Ian from Amsterdam
Tom
There are no indexes on this table. The table was pre-created and then the MV created with:
create materialized view my_ra_cust_trx_mv
on prebuilt table with reduced precision
refresh complete on demand
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> l
1 select container_name
2 from dba_mviews
3 where owner = 'APPS'
4* and mview_name = 'MY_RA_CUST_TRX_MV'
SQL> /
CONTAINER_NAME
------------------------------
MY_RA_CUST_TRX_MV
SQL> l
1 select owner, table_name
2 from dba_tables
3 where owner = 'APPS'
4* and table_name = 'MY_RA_CUST_TRX_MV'
SQL> /
OWNER TABLE_NAME
------------------------------ -----------------------------
APPS MY_RA_CUST_TRX_MV
SQL> l
1 select *
2 from dba_indexes
3 where table_owner = 'APPS'
4* and table_name = 'MY_RA_CUST_TRX_MV'
SQL> /
no rows selected
So still a bit odd. Started with 1,000,000 rows. Deleted them then commit then re-inserted the same
1,000,000 rows.
The delete generated 232Mb of redo - the insert generated 45Mb of redo. Production figures would be
magnitudes higher.
So I still can't figure out why a delete generates x times as much redo as the corresponding
insert.
SQL> create table my_dba_objects
2 as select *
3 from dba_objects;
Table created.
SQL> select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 203620
SQL> delete
2 from my_dba_objects;
210554 rows deleted.
SQL> commit;
Commit complete.
SQL> column name format a24
SQL> select a.name, b.value, to_char( trunc((b.value-203620)/1024/1024,2), '999,999,999,999.99' )
diff_mb
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';
NAME VALUE DIFF_MB
------------------------ ---------- -------------------
redo size 74618684 70.96
SQL> insert into my_dba_objects
2 select *
3 from dba_objects;
210554 rows created.
SQL> commit;
Commit complete.
SQL> select a.name, b.value, to_char( trunc((b.value-74618684)/1024/1024,2), '999,999,999,999.99' )
diff_mb
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';
NAME VALUE DIFF_MB
------------------------ ---------- -------------------
redo size 97118300 21.45
Regards
Ian
Followup November 9, 2006 - 2pm Central time zone:
delete = take row off of block (redo = row), put row into undo (redo=row)
insert = put row on block (redo = row), put "delete rowid" into undo (redo = rowid)
Better not forget - revisited.
November 15, 2006 - 9am Central time zone
Reviewer: Ian from Amsterdam
Tom
Turns out I was not so daft with my comment "Better not forget to change the truncate to drop
storage. Otherwise the table could get fairly large."
Turns out if you do a truncate reuse storage followed by an insert append in parallel it appends
above the previously used storage.
So in my example below 2790 blocks seem to be "lost" from the tablespace.
SQL*Plus: Release 9.2.0.6.0 - Production on Wed Nov 15 14:05:52 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> alter session enable parallel dml;
Session altered.
SQL>
SQL> create table my_objects
2 tablespace applsysx
3 as select *
4 from dba_objects;
Table created.
SQL> create table my_objects_par
2 tablespace applsysx
3 as select *
4 from my_objects;
Table created.
SQL> begin
2 dbms_stats.gather_table_stats(ownname => 'apps'
3 ,tabname => 'my_objects_par'
4 ,estimate_percent => dbms_stats.auto_sample_size
5 ,cascade => true
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select blocks
2 from dba_tables
3 where owner = 'APPS'
4 and table_name = 'MY_OBJECTS_PAR';
BLOCKS
----------
2790
SQL> select to_char(sum(bytes),'99999999999') bytes
2 from dba_free_space
3 where tablespace_name = 'APPLSYSX';
BYTES
------------
38010511360
SQL> truncate table my_objects_par reuse storage;
Table truncated.
SQL> explain plan for
2 insert /*+ append parallel(a) */
3 into my_objects_par a
4 select /*+ parallel(b) */
5 *
6 from my_objects;
Explained.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 210K| 18M| 425 | | | |
| 1 | LOAD AS SELECT | | | | | 15,01 | P->S | QC (RAND) |
| 2 | TABLE ACCESS FULL | MY_OBJECTS | 210K| 18M| 425 | 15,00 | S->P | RND-ROBIN |
-------------------------------------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.
SQL> insert /*+ append parallel(a) */
2 into my_objects_par a
3 select /*+ parallel(b) */
4 *
5 from my_objects;
210562 rows created.
SQL> commit;
Commit complete.
SQL> begin
2 dbms_stats.gather_table_stats(ownname => 'apps'
3 ,tabname => 'my_objects_par'
4 ,estimate_percent => dbms_stats.auto_sample_size
5 ,cascade => true
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks
2 from dba_tables
3 where owner = 'APPS'
4 and table_name = 'MY_OBJECTS_PAR';
BLOCKS
----------
2909
SQL>
1 select trunc((38010511360-sum(bytes))/1024/8) blocks_used
2 from dba_free_space
3* where tablespace_name = 'APPLSYSX'
SQL> /
BLOCKS_USED
-----------
2880
Is this a bug? Or expected behaviour?
Regards
Ian
Any comments
November 23, 2006 - 5am Central time zone
Reviewer: Ian from Amsterdam
Tom
Any thoughts on my question above regarding losing space during a parallel direct path insert?
Regards
Ian
It's OK
November 24, 2006 - 2pm Central time zone
Reviewer: Ian from Amsterdam
Tom
It's OK - I figured out it's expected behavior. Somewhat unexpected expected behavior I will admit
- but now I have read up more on how parallel direct path insert works at the segment level it
makes perfect sense.
But a great "Gotcha" if you were unaware of it.
Regards
Ian
MV complete refresh (9i vs 10g)
December 17, 2006 - 3am Central time zone
Reviewer: Pavel Ruzicka from PRG
I was disapointed to find this change in behaviour of complete refresh between 9i and 10g as well.
9i: TRUNCATE->INSERT direct path [INSERT /*+ APPEND */]
10g: DELETE->standard INSERT [INSERT /*+ BYPASS_RECURSIVE_CHECK */
The side-effects: performance deterioration, excessive redo log information being generaged even
for NOLOGGING MVs, data blocks not being compressed etc..
I see this undocumented change in behaviour is "nasty" from more than one point of view (database
upgrade point of view, documentation..)
Anyway, I can confirm that there is a way to revert to 9i logic of complete MV refresh. Option
ATOMIC_REFRESH=>false drives the refresh to run in TRUNCATE->direct path INSERT [INSERT /*+
BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */].
We are going to log SR for documentation bug or enhancement request for "PL/SQL Packages and Types
Reference". It currently says:
= atomic_refresh =
If this parameter is set to true, then the list of materialized views is refreshed in a single
transaction. All of the refreshed materialized views are updated to a single point in time. If the
refresh fails for any of the materialized views, none of the materialized views are updated.
If this parameter is set to false, then each of the materialized views is refreshed in a separate
transaction.
I believe this does not describe entirely and completely purpose of this argument and details of
its implementation (especially in the context of this change in behaviour between 9i and 10g).
BTW - Once we observed pitiful performance of complte MV refresh on 10g, we reverted to "manual,
roll-your-own" refresh based on TRUNCATE->INSERT direct path->CREATE MV on prebuilt table. We can
outperform the complete refresh in both ATOMIC_REFRESH=>TRUE and FALSE. The reason for this is that
complex MV refresh (the actual INSERT) gets rewritten in rather unfortunate way (using number of
virtual subqueries like "from$_subquery$_005"..). I am not going to insert the whole trace but it
is worth to trace your MV complete refresh to see what is going on.
Mv refresh time
February 15, 2007 - 5am Central time zone
Reviewer: Lakshmi Narayana JV from India
Hi tom ,
I created one MV ,with syssate and next refrsh time for every one hour i.e sysdate+1/24;
But my intention is how can we find the refresh time betwen these intervals(i.e how much time it will takes to refresh the MV).
IS THERE any table which says the refresh time exactly .
Help me
Followup February 15, 2007 - 11am Central time zone:
create your own job (eg: make the snapshot not auto refresh)
you could use dbms_job to submit:
begin
call the refresh routine you wanted to call;
insert into your_log_table( whatever, you, want, to, log);
commit;
end;
/
Monitor MV Refresh
March 26, 2007 - 9am Central time zone
Reviewer: Jignesh from London
Hi Tom,
I've got two materiallized views.
1. One materialized view is based on tables which is refreshed daily morning - Fast Refresh Method
(start with sysdate next (trunc(sysdate)+1) + 7/24)
2. Second materialized view is based on above materialized view which is refreshed on monthly - Complete Refresh Method
(start with sysdate next add_months(trunc(sysdate,'mm'),1) + 9/24).
Requirement:
On 1st day of month,If first MV refresh is going on, I should wait until 1st MV is successfully refreshed and then start complete refresh of second MV.
Limitations:
I am not allowed to write any stored procedure as we are going to put these two MVs in other's schema.
Can Materialized View Group work in this case? or do we have any other method of achiving above?
Thanks
Materialized view not being used
May 20, 2008 - 7am Central time zone
Reviewer: Jayadevan from India
Tom,
We have a table with a couple of millions of records. Approximately 20% of the data is really used.
Rest are records which have undergone soft deletion (i.e. a column eff_to_dtm_utc is set to
sysdate at deletion time). Most of our reports need only valid records (WHERE eff_to_dtm_utc =
to_date('31/12/2075','DD/MM/YYYY'). Since actual deletion of 'soft deleted' records would mean a
lot of changes in the application code, we created a materialized view with the most-used columns
and filter (WHERE eff_to_dtm_utc = to_date('31/12/2075','DD/MM/YYYY'). The column eff_to_dtm_utc
is not part of the MV, since that column is not necessary for the reports now -we have already
filtered on the valid value. We are using the materialized view directly now. It is a FAST REFRESH
ON COMMIT MV. When we run a query against the base table, with only the columns used in the
materialized view, with a filter WHERE eff_to_dtm_utc = to_date('31/12/2075','DD/MM/YYYY'), the
query does not use the MV. What could be the reason? QUERY REWRITE is set to TRUE.
Jay
Followup May 20, 2008 - 11am Central time zone:
... WHERE eff_to_dtm_utc = to_date('31/12/2075','DD/MM/YYYY') ...
you cannot being to imagine my distaste for that approach. One wonders why one did not use NULL.
you did not give me an example to work with, so I will not comment further.
Here would have been a simple way to do it - if you had used a reasonable value for the end date:
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 enable row movement
8 PARTITION BY RANGE (dt)
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('01-jan-9999','dd-mon-yyyy')) ,
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where dt is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 39 | 2 (0)| 00:00:01 | 2 | 2 |
|* 2 | TABLE ACCESS FULL | T | 1 | 39 | 2 (0)| 00:00:01 | 2 | 2 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DT" IS NULL)
Note
-----
- dynamic sampling used for this statement
ops$tkyte%ORA10GR2> set autotrace off
see how the "is null" makes it hit partition 2 only - as you update the partition key from NULL to some value, it would move it into the other partition.
You would have 80% of the data in partition 1, 20% of the active data in partition 2 and no one would be the wiser.
You can do this with your thing as well - put everything less than 2075 into part1, 2075 data in part2 - and you'll be done. No materialized view, no materialized view logs, no additional storage...
Follow up on partitions Vs materialized views
May 20, 2008 - 11pm Central time zone
Reviewer: Jayadevan from India
Hi Tom,
The original table has 52 columns. Out of these, only 6 columns are used by the reports which have
performance issues. In this case, wouldn't using materialized view be better? We are dramatically
reducing the amount of data to be read/fetched, is it not?
Partitioning was the approach we tried first. Even though it did improve performance, we could not
meet the performance expectations. With materialized view approach, we are able to do that (if we
query materialized views directly).
Thanks,
Jay
Followup May 21, 2008 - 8am Central time zone:
depends.
IT ALWAYS DEPENDS.
If you use primarily index access, no, I would not expect much difference.
Querying materialized view logs
June 5, 2008 - 2am Central time zone
Reviewer: Jayadevan from India
Hi Tom,
If I query materialized view logs, that seem to be taking forever to fetch results, even when the
log has 0 records. I tried querying regular tables and it is pretty fast. Snap show the query
against the log as the max elapsed per second query.
The database is Oracle 10g R2.
12:09:06 SQL> select count(*) from pers_all;
COUNT(*)
----------
0
Elapsed: 00:00:00.17
12:13:52 SQL> select count(*) from MLOG$_PERS_ALL;
COUNT(*)
----------
0
Elapsed: 00:01:40.06
Followup June 5, 2008 - 9am Central time zone:
that just means the mv log WAS big and now it empty. It is called the "high water mark", the table got big, we processed it, it is empty but when you full scan it (I'm assuming you are using the old RBO which will full scan for a count like that), we have to read all of the blocks.
either
o use the cbo, we'll use a primary key index and it'll be 'fast'
o stop counting rows, it is our table....
Materialized views
June 5, 2008 - 11pm Central time zone
Reviewer: Jayadevan from India
Hi Tom,
Thanks for the reply. I have not changed any parameters. Since it is 10gR2, by default, it will use
CBO, right?
When/How will the HWM be reset for MV logs? I wanted to query it because initially, I was
refreshing only one of the 3 MVs based on this table. As a result, the log grew really huge, and
all transactions on the base table were taking time. I wanted to ensure we are not doing anything
like that again.
Regards,
Jayadevan
Followup June 9, 2008 - 9am Central time zone:
by default, there are no indexes on this log.
If it is in an ASSM (automatic segment space management) tablespace, you can shrink it
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2> set echo on
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x char(2000) primary key );
Table created.
ops$tkyte%ORA10GR2> create materialized view log on t;
Materialized view log created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec show_space( 'MLOG$_T' );
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 0
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 8,641
Last Used Block......................... 3
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> insert into t select rownum from all_objects where rownum <= 1000;
1000 rows created.
ops$tkyte%ORA10GR2> rollback;
Rollback complete.
ops$tkyte%ORA10GR2> exec show_space( 'MLOG$_T' );
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 1,000
Full Blocks ..................... 0
Total Blocks............................ 1,024
Total Bytes............................. 8,388,608
Total MBytes............................ 8
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 12,425
Last Used Block......................... 128
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> alter table mlog$_t enable row movement;
Table altered.
ops$tkyte%ORA10GR2> alter table mlog$_t shrink space compact;
Table altered.
ops$tkyte%ORA10GR2> alter table mlog$_t shrink space ;
Table altered.
ops$tkyte%ORA10GR2> exec show_space( 'MLOG$_T' );
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 1
Full Blocks ..................... 0
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 4
Unused Bytes............................ 32,768
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 8,641
Last Used Block......................... 4
PL/SQL procedure successfully completed.
that is "online", you can move it (offline operation, locks table for entire process)
ops$tkyte%ORA10GR2> exec show_space( 'MLOG$_T' );
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 1,000
Full Blocks ..................... 0
Total Blocks............................ 1,024
Total Bytes............................. 8,388,608
Total MBytes............................ 8
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 13,193
Last Used Block......................... 128
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> alter table mlog$_t move;
Table altered.
ops$tkyte%ORA10GR2> exec show_space( 'MLOG$_T' );
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 0
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 11,833
Last Used Block......................... 3
PL/SQL procedure successfully completed.
HOW TO CREATE FAST REFRESH
June 16, 2008 - 9pm Central time zone
Reviewer: Pravin Baldawa from India
HI TOM,
HOW TO CREATE FAST REFRESH ON THE FOLLOWING QUERY
SELECT distinct deptno,dname,loc FROM dept
AWAITING...
thanks,
Followup June 17, 2008 - 9am Central time zone:
remove distinct, it is not necessary - deptno is unique, the data set is already distinct by definition.
Thanks for the response..
June 18, 2008 - 11am Central time zone
Reviewer: Pravin Baldawa from India
What if I have to use distinct in the select query and use the fast refresh ? Is it possible?
Regards
Followup June 18, 2008 - 3pm Central time zone:
ops$tkyte%ORA10GR2> create table t ( x int primary key, y int, z int );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view log on t with rowid (y,z) including new values;
Materialized view log created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view mv
2 refresh fast
3 as
4 select y, z
5 from t
6 group by y, z;
Materialized view created.
that would be equivalent.
MV Invalid afer Schema refresh
June 25, 2008 - 9pm Central time zone
Reviewer: Bhakti Dash from India
After refreshing the schema from production to development, apps developers complaining that the
MV's are invalid. Can you please shed some light on this?
Its 9204 database.
Regards,
Bhakti
Followup June 26, 2008 - 3pm Central time zone:
there is entirely insufficient data to say a thing about anything here.
no idea what a 'refresh' entails for you, what it means to you.
no idea where the mv's are invalid - production or development
no idea what the mv's entail, distributed, single database
there is nothing comment-able (I made that word up) here.
July 1, 2008 - 8am Central time zone
Reviewer: A reader
Hi- We have a view which has about 690,000 rows and we are trying to create a materialized view for
it. The create is taking a very long time (more than an hr). How can we speed this up. Even when
the create is done, and when we try to refresh it, the refresh is going very slow. Most of the time
we end up killing the refresh. Is there any tips for speeding up operations on the MV ?
July 11, 2008 - 7am Central time zone
Reviewer: Tomas Solar from Prague, CZ
Hello,
we have MV refreshed each hour. This job running 15min and generates 5GB during the time.
I tried to alter MV to nologging
ALTER MATERIALIZED VIEW DOTTK_ADM.CHS_REPLICA1_MV NOLOGGING;
I tried alter index on it
alter index DOTTK_ADM.I_SNAP$_CHS_REPLICA1_MV rebuild nologging;
I tried to set nocompress
ALTER MATERIALIZED VIEW DOTTK_ADM.CHS_REPLICA1_MV NOCOMPRESS;
even i update refresh process
exec DBMS_MVIEW.REFRESH('DOTTK_ADM.CHS_REPLICA1_MV','C',ATOMIC_REFRESH=>FALSE);
but nothing help me.
I really dont know what to do. One thing what i should try is drop MV and recreate but with which parameter to avoid the same situation?
Regards,Tom
Followup July 11, 2008 - 8am Central time zone:
well, indexes are always going to be logged.
why would nocompress be something you tried?
anyway, you give almost no information here.
are you archivelog mode
did dba force logging
is the view refreshed complete or incrementally (if the latter, then nologging is not an option)
July 11, 2008 - 9am Central time zone
Reviewer: Tomas Solar from Prague, CZ
Thank you for an answer. I dont hope that samebody answer me.
It was recommended me from product support from oracle ( logged SR) so i tried it but no effect.
yes, i am in archive log
it is force refresh from remote table. Remote table has 2642502 rows
I drop MV and indexes and recreate it again with this commands:
MV:
===
CREATE MATERIALIZED VIEW VHS_REPLICA1_MV
USING INDEX TABLESPACE "DOTTK_MAIN"
REFRESH FORCE WITH ROWID ON DEMAND
AS
select hit_date, banner_id,hit_count
from VIEW_HITS_STAT@REPLICA1;
ALTER MATERIALIZED VIEW DOTTK_ADM.VHS_REPLICA1_MV NOLOGGING
index:
====
CREATE UNIQUE INDEX "DOTTK_ADM"."PK_BANID_HDATE_R1" ON "DOTTK_ADM"."VHS_REPLICA1_MV" ("HIT_DATE", "BANNER_ID")
TABLESPACE "DOTTK_MAIN" PCTFREE 5 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1024K BUFFER_POOL DEFAULT)
ALTER INDEX "DOTTK_ADM"."I_SNAP$_VHS_REPLICA1_MV" NOLOGGING
than ompute statistics:
==============
exec DBMS_STATS.GATHER_TABLE_STATS('DOTTK_ADM','VHS_REPLICA1_MV',NULL,DBMS_STATS.AUTO_SAMPLE_SIZE);
and setup refresh:
============
BEGIN
DBMS_REFRESH.MAKE(
name => '"DOTTK_ADM"."VIEW_AND_CLICKS"',
list => '',
next_date => to_date('07-11-2008 13:29:52', 'MM-DD-YYYY HH24:MI:SS'),
interval => '/*1:Hrs*/ sysdate + 1/24',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => FALSE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
Regards,
Tomas
Followup July 11, 2008 - 10am Central time zone:
if you do a complete refresh - we will truncate and insert /*+ APPEND */ (so do a complete refresh if you want to minimize redo)
UNDO for the index and the index writes will always be logged - always.
the only way to not have them logged would be to disable/drop the index and rebuild/create it later.
July 11, 2008 - 12pm Central time zone
Reviewer: Tomas Solar from Prague, CZ
thank you but it was worst
I set ALTER MATERIALIZED VIEW DOTTK_ADM.VHS_REPLICA1_MV REFRESH COMPLETE
I did refresh and now this refresh job generate 4x more redo size.
col sid format 9999;
SQL> col username format a15;
SQL> col program format a30;
SQL> col value format 99999999;
SQL> SELECT st.sid, se.username, se.program, (value/1048576) VALUE
2 FROM gv$sesstat st, gv$statname sn, gv$session se
3 WHERE sn.name = 'redo size' and se.username = 'DOTTK_ADM'
4 AND sn.statistic# = st.statistic#
5 AND st.sid = se.sid
6 AND value <> 0
7 ORDER BY 4 desc;
SID USERNAME PROGRAM VALUE
----- --------------- ------------------------------ ---------
2124 DOTTK_ADM oracle@tnode (J000) 7918
So i try to drop MV and recreate is again with clause NOLOGGING.
CREATE MATERIALIZED VIEW VHS_REPLICA1_MV
NOLOGGING
USING INDEX TABLESPACE "DOTTK_MAIN"
REFRESH FORCE WITH ROWID ON DEMAND
AS
select hit_date, banner_id,hit_count
from VIEW_HITS_STAT@REPLICA1;
CREATE UNIQUE INDEX "DOTTK_ADM"."PK_BANID_HDATE_R1" ON
"DOTTK_ADM"."VHS_REPLICA1_MV" ("HIT_DATE", "BANNER_ID")
TABLESPACE "DOTTK_MAIN" PCTFREE 5 INITRANS 2 MAXTRANS 255
NOLOGGING
STORAGE (
INITIAL 1024K BUFFER_POOL DEFAULT)
Regards,
Tom
Followup July 11, 2008 - 1pm Central time zone:
then you were doing an increment refresh before and there is no way to skip undo and redo generation for that - none.
I told you - regardless, the index will be maintained.
The only ZERO redo option would be
a) disable or drop indexes
b) complete refresh into nologging table
c) rebuild/create of index with no logging
nothing else will get you there
if you refresh incrementally - then all changes are logged and you cannot stop that.
if you refresh complete - the move of the data from temp into the index will be logged (the table need not be)
those are your choices.
Complete refresh does a delete instead of truncate
July 11, 2008 - 12pm Central time zone
Reviewer: A reader
Hi Tom,
I am having a similar problem. I use a MV to enforce declarative integrity, but I ran into the
increasing logging problem (@18 MB per transaction). Archive log sizes for a day increased to @5 GB
from @0.7 GB.
Metalink Note 365157.1 says that in 10g, a complete refresh will do a delete instead of a truncate
and that this is expected behavior. To force the refresh to do a truncate instead of a delete, the
ATOMIC_REFRESH parameter needs to be set to FALSE.
I converted the MV from REFRESH ON COMMIT to REFRESH ON DEMAND and set the ATOMIC_REFRESH to FALSE
in the DBMS_MVIEW.REFRESH procedure. This brought down the logging to almost negligible levels, but
it commits data to the base table before the check constraint on the MV fires.
Followup July 11, 2008 - 1pm Central time zone:
what are you doing that you do a complete refresh???!?!?! for integrity???
I don't get it - you would be using a refresh fast on commit materialized view.
July 11, 2008 - 1pm Central time zone
Reviewer: Tomas Solar from Prague, CZ
I recreated MV as mentioned above but no effect.
So i will try my "last" test
1)MV with complete refresh
2) exec DBMS_MVIEW.REFRESH('DOTTK_ADM.VHS_REPLICA1_MV','C',ATOMIC_REFRESH=>FALSE);
Tom
Followup July 11, 2008 - 1pm Central time zone:
see above, I listed your options....
Nested MV's as a workaround for fast refreshes ?
July 21, 2008 - 9am Central time zone
Reviewer: Gokul from Brussels
Hi Tom,
We are trying to create a fast refreshable MV on a complex query. As documented, it is not
possible. To make is work we are looking at options to create nested materialized views.
For the data below, we are interested in the most recent M row for a given X, Y. Since, aggregates
are not allowed in fast refreshable MV's we cannot use this directly in the MV view definition.
Is it a good idea to create a MV with all the interested keys, and then join this MV with the main
table ?
X Y M N
1000 1 20070101 20070101
1000 1 20070101 20080601
1000 1 20080101 20080101
1000 1 20080301 20080101
I am also facing problems, in querying the data for the most latest row. Is this possible with
simple sql (without aggregations) ?
Here is the scripts I used for the same,
create table t10 (x int, y int, m int, n int);
alter table t10 add constraint t10_pk primary key (x,y,m,n);
insert into t10 (X, Y, M, N)
values ('1000', '1', '20070101', '20070101');
insert into t10 (X, Y, M, N)
values ('1000', '1', '20070101', '20080601');
insert into t10 (X, Y, M, N)
values ('1000', '1', '20080101', '20080101');
insert into t10 (X, Y, M, N)
values ('1000', '1', '20080301', '20080101');
Rgds,
Gokul
Followup July 22, 2008 - 10am Central time zone:
... Since, aggregates are not allowed in fast refreshable MV's we cannot use
this directly in the MV view definition.
...
that is not true in general. However...
... Is it a good idea to create a MV with all the interested keys, and then join
this MV with the main table ?
...
it could be - it depends. what type of queries do you actually use against this base table??? we don't know what questions you ask.
Other than "we need the most current row by X,Y from this table"
if you
select ... from T where x = ? and y = ? .....
that is, you supply X,Y - I would not consider a materialized view - just an index on X,Y,the_date_field and query
select * from
(select * from t where x = ? and y = ? order by x desc, y desc, the_date_field DESC )
where rownum = 1;
with an index on x,y,the_date_field. we'd read the index backwards, get the first row, stop.
if you needed the most current row for EVERY x,y - and x,y typically had one or two records for each - then I'd skip the MV again and just use the base table.
X,Y would need many rows for each X,Y and we'd need to get ALL or MOST of the x,y values before a MV would be considered - and this table would have to be huge. And whether we'd fast (which should be called incremental, not fast, fast could be really really slow) refresh or complete refresh would depend on many factors as well.
Nested MV's as a workaround for fast refreshes ?
July 22, 2008 - 6pm Central time zone
Reviewer: Gokul from Brussels
Hi Tom,
To give a bit of background, we were looking at the MV option because the application has to fetch
data from a remote database. Currently, the data is abstracted from the remote database base tables
and presented to the application as views. But, since the datamodels of the remote database and the
application database is too different joins on some of the remote base tables is taking a lot of
time and probably will not meet the response time requirements.
One of the queries looks like
select x.a,x.b,x.c,x.d,x.e,y.a
from x x1, y y1, z z1
where
x1.a = y1.a
and x1.b = y.b
and x1.c = (select max(x2.c) from x x2
where x1.a = x2.a
and x1.b = x2.b
)
and x1.a = z1.a
and x1.b = z1.b
and z1.c = (select max(z2.c) from z z2
where z1.a = z2.a
and z1.b = z2.b
)
and z1.d = (select max(z3.d) from z z3
where z1.a = z3.a
and z1.b = z3.b
and z1.c = z3.c
)
x is 17 million rows
y is 54 million rows
z is 80 million rows
We were looking at the nested MV option to solve the subquery aggregations.
We will not be using any rewrite features of MV, but just
select * from <final MV> where "x.a" = <user input>
or
select * from <final MV> where "y.a" = <user input>
DBAs strongly suggest to write our own refresh code and not use nested MVs as it will heavily
impact the remote database and we are exploring the batch option now.
Just checking. Do you see any possibility of creating an incremental refresh MV for the example
query with having to created intermediate MVs ?
Rgds,
Gokul
Followup July 22, 2008 - 6pm Central time zone:
...
DBAs strongly suggest to write our own refresh code and not use nested MVs as
it will heavily impact the remote database and we are exploring the batch
option now.
...
wow, your dba's must trust you a lot? I mean, why would what you have to do be better than what the builtin stuff can do?
this really looks like "we meant to have a single database" - why would you want to replicate this volume of data - why wouldn't this process that needs this data be part of the database that has this large amount of data in the first place? (I hate, despise, reject whenever possible - any form of replication (DR - disaster recovery - is not replication, replication is not DR, by the way...)
Is two-way syncing possible with MV
July 28, 2008 - 8pm Central time zone
Reviewer: DG from Singapore
Hi Tom,
Thanks for all the valuable advice.
Wanted to check with you if we could do two-way syncing with MVs? I know the changes in master
table will be reflected in MV (using fast or complete Refresh), but how can we reflect the changes
made to updatable MVs in master table?
Thanks,
Followup July 29, 2008 - 3pm Central time zone:
by creating them as updateable materialized views?
that is exactly what an updateable materialized view does - check out the documentation again, that is the difference between a normal materialized view and an updatable one - you can update the latter and the changes will be reflected in the master, you cannot update the former at all
Fast refresh and redo
November 28, 2008 - 12pm Central time zone
Reviewer: Jyo
Hi tom,
In the following materialized view, even if there are no data in table, it's generating redo on
fast refresh. is there any option to disable the redo on refresh.
CREATE TABLE TABLE1
(
IN_ID NUMBER(20),
APPLN_ID NUMBER(20),
DTL_ID NUMBER(20),
M_TYPE VARCHAR2(20 BYTE),
STATUS VARCHAR2(50 BYTE),
APPLN_CODE VARCHAR2(10 BYTE),
M_SRC VARCHAR2(20 BYTE),
M_FLAG VARCHAR2(3 BYTE),
FILE_NAME VARCHAR2(100 BYTE),
M_NO VARCHAR2(1000 BYTE),
ARCHIVE VARCHAR2(1 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
ALTER TABLE TABLE1 ADD (
CONSTRAINT PK_TABLE1
PRIMARY KEY
(IN_ID));
CREATE MATERIALIZED VIEW LOG ON TABLE1
WITH ROWID, PRIMARY KEY, SEQUENCE
(M_SRC, APPLN_CODE, M_TYPE, STATUS, ARCHIVE)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW MV_TABLE1
ENABLE ROW MOVEMENT
BUILD IMMEDIATE
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+1/48
WITH PRIMARY KEY
AS
SELECT APPLN_CODE, M_TYPE, STATUS, COUNT(*) COUNT
FROM TABLE1
WHERE (ARCHIVE IS NULL OR ARCHIVE <> 'Y')
GROUP BY APPLN_CODE, M_TYPE, STATUS;
ALTER MATERIALIZED VIEW LOG ON TABLE1 NOLOGGING;
ALTER MATERIALIZED VIEW MV_TABLE1 NOLOGGING;
regards
jyo
Followup November 28, 2008 - 5pm Central time zone:
fast refresh is the same as saying "incremental refresh"
the redo generated would be the result of removing the old rows, maintaining any indexes and so on.
Even a complete refresh would generate some redo.
How much redo is it generating and why do you believe that it is "too much"
materialized view to show count
December 9, 2008 - 10am Central time zone
Reviewer: jyo
it's generating small amount of redo log even if for the empty table in the above case. So on
periodic refresh, more log files will be generated on tables with data. in my case 8 similar
marerialized views are there.
In my scenario, i want to show the output of the above materialized view query as dashboard in the
application. So i have created materialized view with periodic refresh. querying normal view based
on this query is taking more time.
materialized view query from earlier post. status collumn value will change. according to that
count should change
SELECT APPLN_CODE, M_TYPE, STATUS, COUNT(*) COUNT
FROM TABLE1
WHERE (ARCHIVE IS NULL OR ARCHIVE <> 'Y')
GROUP BY APPLN_CODE, M_TYPE, STATUS;
few options i have
1. Direct execution of the query
2. Query from normal view
3. using materialized view
4. create a summary table, and use MERGE to update this
table periodically with the above query
i am looking for fast response in getting the count. any other options. which of the above option
will be better. any performance issues with tables that may have millions of records over a period
of time
Thanks
Followup December 9, 2008 - 2pm Central time zone:
sounds like a materialized view to me?
not sure what the question was really....
why do you care about "a small amount of redo" - it is going to generate redo, everything you do pretty much will
MV Refresh
March 22, 2009 - 7pm Central time zone
Reviewer: Arvind Mishra from India
Hello Tom,
We require truncate and insert load behavior of MV instead of delete and insert behavior. We are
using Oracle 10g and wants to complete refresh the MV.
a) What is the best way to get it.
b) Someone told me that if we use ATOMIC_REFRESH => false while using dbms_mview.refresh then we
will get truncate and load. Is it true?
c) How can I check that which statement is used by an mv to refresh itself? Please give me an
example.
Thanks,
Arvind
Followup March 24, 2009 - 11am Central time zone:
use atomic_refresh => FALSE as a parameter to the refresh.
do not know what you mean by c)
either it is run as a job (select what from dba_jobs...) or you run it yourself (in which case... well, you coded it)
MV Refresh
March 22, 2009 - 7pm Central time zone
Reviewer: Arvind Mishra from India
Hello Tom,
We require truncate and insert load behavior of MV instead of delete and insert behavior. We are
using Oracle 10g and wants to complete refresh the MV.
a) What is the best way to get it.
b) Someone told me that if we use ATOMIC_REFRESH => false while using dbms_mview.refresh then we
will get truncate and load. Is it true?
c) How can I check that which statement is used by an mv to refresh itself? Please give me an
example.
Thanks,
Arvind
dbms_mview
March 30, 2009 - 4pm Central time zone
Reviewer: Arvind Mishra from Pune, India
Thanks Tom.
Sorry for posting same question twice. Actually my internet connection was not working properly.
By c) I mean that how can I see that if materialized veiw is using truncate and load or delete and
load to refresh itself.
Regards,
Arvind
parallelism in MV refresh
April 20, 2009 - 3pm Central time zone
Reviewer: Srini from Denver, CO USA
I want to use parallelism for complete refresh and no parallel for fast refresh (10g DB). I was
looking at the documentation and I am not clear what the process is for this. The reason I ask is
99% of time time we run a fash refresh with no parallel and once in while we do a lot of changes on
the base table (30% of the data) and we want to do a complete refresh with parallelism. Below are
specific questions I had to enable parallelism
1. MV: This should be set to parallel? How about MV indexes?
2. Base table: This should be set to parallel? This is a non partitioned table.
3. MV Log: This should be set to no parallel as it is used in fast refresh.
4. MV Refresh package: Can we set the parallelism in the MV refresh procedure (dbms_mview.refresh)
and Oracle would do a parallel build even if the underlying MV and base table are not set as
parallel? If so than I dont have to worry about MV and base table setup and just use this
parameter.
Snapshot recreation after master table's data change?
April 21, 2009 - 1pm Central time zone
Reviewer: Suren from Mongolia
Hi Tom i have an crucial issue.
Here is what i did:
1. i have 3 sites: A -> master site, B,C -> MV sites
2. There is very big table (7Gb) in A, named BOOK_IMAGE
3. BOOK_IMAGE table has MV log.
4. Then i created fast refreshable snapshots from BOOK_IMAGE at B and C site, both named REMOTE_BOOK_IMAGE and refreshed.
5. After that i accidentally dropped REMOTE_BOOK_IMAGE snapshot from B
6. While i have dropped REMOTE_BOOK_IMAGE there are many changes (INSERT,UPDATE,DELETE) made to BOOK_IMAGE. Changed rows are approximately 8000.
7. I recreated fast refreshable snapshot REMOTE_BOOK_IMAGE at B and refreshed. But these 8000 records are didn't come to B site.
I can't do complete refresh because of BOOK_IMAGE's huge size.
What should i do to get that changed rows?
Followup April 21, 2009 - 3pm Central time zone:
describe how you did step #7.
#7 detail
April 21, 2009 - 9pm Central time zone
Reviewer: Suren from Mongolia
create materialized view REMOTE_BOOK_IMAGE
on prebuilt table
refresh force on demand as
select * from BOOK_IMAGE@A;
dbms_mview.refresh('REMOTE_BOOK_IMAGE');
Followup April 23, 2009 - 11am Central time zone:
so, what was in the prebuilt table?
do you know how to do an offline instantiation? That is what you are trying to do, did you do the steps necessary to do an offline instantiation? Have you read about it?
http://www.oracle.com/pls/db102/search?remark=quick_search&word=offline+instantiation&tab_id=&format
=ranked
(think about the synchronization issues here, unless you follow a process - which will include bringing over the current data - getting the data CURRENT yourself - how could we make it "current"? We don't know what changes need or do not need to be applied, you have to do this via a process...)
Scenario for Snap Shot Plan
April 22, 2009 - 1pm Central time zone
Reviewer: A reader
Tom,
Scenario: I got a table say T with prices for products, I need to take a snap shot at the begining
of each quarter. However, the user needs a history of these snap shots whenever they need. Here is
my plan: 1. create a table of "historical_data_for_snapshot", 2. create and refresh(after first
creation of course) MV at the begining of each quarter, 3. immediately push the MV data to the
table of "historical_data_for_snapshot" after fresh the MV. Does it sound good or have some major
flaw here? Thanks in advance for your input.
Followup April 24, 2009 - 4pm Central time zone:
I don't see the usefulness of the materialized view here at all.
Sounds more like you want to run
insert into history
select to_date( :bind_that_has_the_qtr_in_it, 'format') qtr, t.*
from t;
at the beginning of each quarter.
Need MVIEW REFRESH or NOT
April 26, 2009 - 8am Central time zone
Reviewer: Parag from Bangalore,India
Hi Tom,
Thanks for your valuable inputs.
I have just done reorg of a complete tablespace(alter table move,alter index move) to a new
tablespace name.
My question is -
Q1.)Whether refresh of the materialized views is required or not in that tablespace?
Q2.) If required,whether refresh required of all MVIEWS in that tablespace or only particular
MVIEWS?
DB - 9.2.0.6
My MVIEW details after reorg are as below -
MVIEW_NAME REFRESH_METHOD REFRESH_MODE STALENESS
TO_CHAR(LAST_REFRESH LAST_REF FAST_REFRESHABLE
------------------------------ -------------------- -------------------- -------------------
-------------------- -------- ------------------
A COMPLETE DEMAND FRESH 31-MAR-2009
11:35:10 COMPLETE DIRLOAD_LIMITEDDML
B COMPLETE DEMAND FRESH 31-MAR-2009
11:35:36 COMPLETE DIRLOAD_LIMITEDDML
C COMPLETE DEMAND UNKNOWN 26-APR-2009
01:30:41 COMPLETE NO
D COMPLETE DEMAND STALE 03-APR-2009
06:02:50 COMPLETE NO
E COMPLETE DEMAND UNKNOWN 26-APR-2009
13:11:01 COMPLETE NO
F FORCE DEMAND UNDEFINED 10-MAY-2007
22:55:09 COMPLETE NO
G COMPLETE DEMAND STALE 03-APR-2009
06:00:37 COMPLETE NO
H COMPLETE DEMAND STALE
03-APR-2009 06:00:13 COMPLETE NO
I FAST COMMIT FRESH 24-APR-2009
16:06:13 FAST DIRLOAD_DML
Regards,
Parag
Followup April 27, 2009 - 1pm Central time zone:
the fresh ones do not, the stale ones do - but they were not made stale by the move - they were already stale.
You didn't actually change the data, there is nothing to synchronize
On Commit Materialized View
May 7, 2009 - 7pm Central time zone
Reviewer: A reader
Hi Tom, I'm puzzled by the following scenario.
Here's a test case that's reproducible every time on Oracle 10.2.0.4.
CREATE TABLE m (
mID NUMBER NOT NULL PRIMARY KEY,
col2 NUMBER NOT NULL,
col3 NUMBER NOT NULL,
col4 NUMBER NOT NULL,
col5 NUMBER NOT NULL,
col6 VARCHAR2(50) NOT NULL
);
CREATE TABLE i (
iID NUMBER NOT NULL PRIMARY KEY,
mID NUMBER NOT NULL REFERENCES m(mID),
col2 NUMBER NOT NULL,
col3 VARCHAR2(10) NOT NULL
);
CREATE SEQUENCE i_seq;
CREATE INDEX m_idx1 ON m(col2);
CREATE INDEX i_idx1 ON i(mID);
CREATE MATERIALIZED VIEW LOG ON I WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON M WITH ROWID;
CREATE MATERIALIZED VIEW m_i_MV
BUILD IMMEDIATE
REFRESH FAST WITH ROWID
ON COMMIT
AS
SELECT i.iID,
m.mID,
i.col2,
i.col3,
m.col2 m_col2,
m.rowid m_rowid,
i.rowid i_rowid
FROM i,
m
WHERE i.mID = m.mID;
-- --------------------------------------------------------------------------------
-- Session #1 - start populating the tables.
-- --------------------------------------------------------------------------------
BEGIN
FOR i IN 1..20000 LOOP
INSERT INTO m VALUES (i, i, i, i, i, 'xxxxxxxxxxxxxxxxxxxxxxxxx');
FOR j IN 1..3 LOOP
INSERT INTO i VALUES (i_seq.NEXTVAL, i, i, 'zzzzzzzzzz');
END LOOP;
COMMIT; -- Bad, I know, but just a simulation.
END LOOP;
END;
/
-- --------------------------------------------------------------------------------
-- Session #2 - statistics collection started on table i - Run this approximately
-- 15 seconds after session #1 started.
-- --------------------------------------------------------------------------------
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => user,
tabname => 'I',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => TRUE);
END;
/
-- --------------------------------------------------------------------------------
-- Session #3 - Session #1 is blocking session #2. Nothing moves, but no deadlock!
-- Both session 1 and 2 are stuck forever!
--
-- The show_lock_pkg comes from metalink.
-- --------------------------------------------------------------------------------
SQL> exec SHOW_LOCK_PKG.showlock;
Locking/Waiting tree Username PID Locked Object/Type-Request
------------------------- -------------- ------ ------- ---------------------
+ 1478,12122
TEST_SCHEMA
4012:1
|
Tran enqueue-Exclusive
|--->1481,2475
TEST_SCHEMA
2184:3
TEST_SCHEMA.I
-- --------------------------------------------------------------------------------
-- Session #4 - simple query, session hung.
-- --------------------------------------------------------------------------------
SELECT COUNT(*) FROM i;
Followup May 11, 2009 - 2pm Central time zone:
very good test case.
Please utilize it to open a bug with support, it definitely reproduces. The dbms_stats is blocked by the first session - but the first session itself "stops" until the dbms_stats is killed (ctl-c'ed).
Suggest you add
dbms_application_info.set_client_info(i);
to the plsql block so you can see that the insert block gets 'stuck'. Stopping the statistics gathering is sufficient to "un-freeze" everything.
On Commit Materialized View Followup
May 11, 2009 - 5pm Central time zone
Reviewer: A reader
I'll definitely open a support case for this. What would you suggest we do in the meantime before
the bug is fixed? We are a software vendor using Oracle as backend. In the upcoming release of the
software, we're planning on using on commit materialized view. It's important that the materialized
view contains real time data. The transaction rate won't be as high as shown in the test case. The
number of concurrent writers will also likely stay pretty low (3 ~ 5 at most). Killing the
statistics gathering process every time the system hangs isn't a viable solution in our case. Can
you please recommend a workaround for this situation? Thank you.
Followup May 11, 2009 - 7pm Central time zone:
lock the statistics on these tables - do not gather on them if you cannot schedule a maintenance window.
On Commit Materialized View on 11g
May 12, 2009 - 4pm Central time zone
Reviewer: A reader
I used the above test case (May 7 2009) on 11g and was not able to reproduce the issue. So it seems
that this is no longer a problem on the latest release of Oracle.
However, here are the elapsed time running the test case with and without the materialized view:
Without MV: 6.34 seconds
With MV : 18 min 36 seconds
It's obvious that it would definitely take longer to populate the base tables when there's a
materialized view. However, why does it take so much longer? Is this normal?
Followup May 13, 2009 - 11am Central time zone:
are you really crafting an on commit MV just to avoid a simple join later? Joins are really pretty efficient in the database.
think of the amount of work (generic work, MV's are very generic) you've added to the commit process.
On Commit Materialized View
May 13, 2009 - 12pm Central time zone
Reviewer: A reader
No. Not for joins. I've done enough benchmarking to know that a pre-join materiazlied view doesn't
really help that much in performance, at least in our application. The materialized view is used
mainly to address pagination performance. In order to avoid duplicate records across pages, the
pagination SQL is sorted by <the column that the user selected> + <the primary key of one of the
base tables>. Since these two columns do not always come from the same table, using a pre-join
materialized view solves this problem. My benchmark numbers suggest that performance improvement
achieved this way could be quite significant.
Do you have an alternative way that you can suggest to address this specific problem? Thanks.
Followup May 13, 2009 - 3pm Central time zone:
... <the column that the user selected> + <the primary key of one
of the base tables>. ...
change that to be
<the column that the user selected> + <the primary key of one
of the base table that column comes from>.
then you can query that table (pagination wise) and join to the rest.
May 13, 2009 - 3pm Central time zone
Reviewer: A reader
"<the column that the user selected> + <the primary key of one of the base table that column comes
from>".
This would only work if <the primary key of one of the base table that column comes from> is unique
after the join. Isn't it? Can you please provide an example of how this works?
Another consideration is that if dynamic sampling is used, wouldn't it be very helpful if all the
"selected" columns were on the same table (as in the materialized view) so cross-column cardinality
stats is available to the optimizer?
Followup May 13, 2009 - 4pm Central time zone:
if you say... This would only work if <the primary key of one of the base table that column
comes from> is unique after the join. Isn't it? Can you please provide an
example of how this works?
...
then I'll say the same for you
<the column that the user selected> + <the primary key of one of the
base tables>
that would be true of that as well? no?
or is the one of the base tables specifically the top of a hierarchy?
May 13, 2009 - 5pm Central time zone
Reviewer: A reader
Let me phrase it more precisely. The sorting should be done by <the column that the user selected> + <the primary key of the base table that would make the record unique>
Here's a test case to demonstrate this.
-- -------------------------------------------------------------------
-- Test Setup
-- -------------------------------------------------------------------
DROP TABLE i;
DROP TABLE m;
CREATE TABLE m (
mID NUMBER NOT NULL PRIMARY KEY,
col2 NUMBER NOT NULL,
col3 NUMBER NOT NULL,
col4 NUMBER NOT NULL,
col5 NUMBER NOT NULL,
mDATE DATE NOT NULL
);
CREATE TABLE i (
iID NUMBER NOT NULL PRIMARY KEY,
mID NUMBER NOT NULL REFERENCES m(mID),
status VARCHAR2(10) NOT NULL,
matchCount NUMBER NOT NULL
);
CREATE SEQUENCE i_seq;
CREATE INDEX m_n1 ON m(mDate, mID);
CREATE INDEX i_fk1 ON i(mID);
CREATE INDEX i_n1 ON i(Status, iID);
CREATE INDEX i_n2 ON i(matchCount, iID);
BEGIN
FOR i IN 1..2000 LOOP
INSERT INTO m VALUES (i, i, i, i, i, SYSDATE - MOD(i, 1000));
FOR j IN 1..30 LOOP
INSERT INTO i VALUES (i_seq.NEXTVAL, i, MOD(i, 4), i);
END LOOP;
END LOOP;
END;
/
COMMIT;
exec dbms_stats.gather_table_stats( user, 'M' );
exec dbms_stats.gather_table_stats( user, 'I' );
CREATE TABLE x_temp AS
SELECT i.mID, iID, status, MatchCount, mDate, col2, col3
FROM i, m
WHERE i.mID = m.mID AND
1 = 2;
-- -------------------------------------------------------------------
-- Sort by <the column that the user selected> +
-- <the primary key of one of the base table that column comes from>
--
-- This creates duplicate records (see the result of the next SQL).
-- -------------------------------------------------------------------
BEGIN
FOR i IN 1..1200 LOOP
INSERT INTO x_temp
SELECT mID, iID, status, MatchCount, mDate, col2, col3
FROM (
SELECT x.*, rownum rn
FROM (
SELECT i.mID, iID, status, MatchCount, mDate, col2, col3
FROM i, m
WHERE i.mID = m.mID
ORDER BY mDate, mID --> will not work
) x
WHERE rownum <= 50 * i
)
WHERE rn > (1 + (50 * (i - 1)));
END LOOP;
END;
/
SELECT COUNT(*)
FROM (
SELECT COUNT(*)
FROM x_temp
GROUP BY iID
HAVING COUNT(*) > 1
);
COUNT(*)
----------
26
-- -------------------------------------------------------------------
-- Sort by <the column that the user selected> +
-- <the primary key of the base table that would make the record unique>
--
-- No duplicate records (see the result of the next SQL).
-- -------------------------------------------------------------------
TRUNCATE TABLE x_temp;
BEGIN
FOR i IN 1..1200 LOOP
INSERT INTO x_temp
SELECT mID, iID, status, MatchCount, mDate, col2, col3
FROM (
SELECT x.*, rownum rn
FROM (
SELECT i.mID, iID, status, MatchCount, mDate, col2, col3
FROM i, m
WHERE i.mID = m.mID
ORDER BY mDate, iID --> Works well
) x
WHERE rownum <= 50 * i
)
WHERE rn > (1 + (50 * (i - 1)));
END LOOP;
END;
/
SELECT COUNT(*)
FROM (
SELECT COUNT(*)
FROM x_temp
GROUP BY iID
HAVING COUNT(*) > 1
);
COUNT(*)
----------
0
However, if I sort by <the column that the user selected> +
<the primary key of the base table that would make the record unique>, I may not always be able to create a composite index on the columns if they're not on the same tables.
Followup May 13, 2009 - 7pm Central time zone:
ok, given "The sorting should be done by <the column that the user selected> + <the primary key of the base table that would make the record unique> " - I don't really have any other inputs short of
do you need "on commit refresh", can it be "refresh fast shortly after commits" - refresh frequently, but not on commit.
|