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)
);

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

 

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?...

            
 

SQL> col force_logging format a20
SQL> select force_logging from v$database;

FORCE_LOGGING
--------------------
NO


 does some init parameter matter this?? 

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
 

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

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 ????????????


    

    

     
 

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,

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

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 ;)

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

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



 

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?

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?

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.











 

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.

From,

</code> http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10736/advmv.htm#sthref576 <code>

<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.

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

test

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

I forgot to include the error I was getting :

ORA-12060: shape of prebuilt table does not match definition query

-raman

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 

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

Yes, Tom

It would exactly match datatype,columns etc.

Tom,

There was a primary key missing in my script which I haven't noticed...

Now it's working...

regards,
-raman

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

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.

hi,

mview on prebuilt partitioned table + truncate/refresh partition + "alter mview consider fresh" is an interesting idea. i will try this.

regards,
markus

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

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

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

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

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

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  

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

 

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. 

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.
 

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

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.

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??

I am glad to say that I was satisfied after reading this review.There are really helpful parts in it.
Best Regards,

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

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?

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;

:) .. But I want to use parallel degree during refresh, is this option available...

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);

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

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)?

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,'Testing',null,null,null);

commit;

alter session set sql_trace = true;

exec dbms_mview.refresh('test');

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?)

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;

-

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.

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 </code> http://www.gplivna.eu/papers/mat_views_search.htm <code>
Look under the heading "Alternate refresh process of materialized views".
</shameless plug>

Gints

Tom,

Going back to your original example (from 2004..) I’m 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.. let’s 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..

--let’s 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. Let’s see if there’s 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
 

> 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) ?

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

>> 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?

</code> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#sthref6783 <code>

<< 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.

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

I guess we could also do the insert append ourselves - and then an alter mv consider fresh. Hmmm.

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. I was forgetting that the truncate moved the HW mark anyway.

Slinks away looking embarrassed.

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

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 

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 

Tom

Any thoughts on my question above regarding losing space during a parallel direct path insert?

Regards

Ian

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

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.

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

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

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

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

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

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

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

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.

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.

HI TOM,

HOW TO CREATE FAST REFRESH ON THE FOLLOWING QUERY

SELECT distinct deptno,dname,loc FROM dept

AWAITING...

thanks,

What if I have to use distinct in the select query and use the fast refresh ? Is it possible?


Regards

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.

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

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 ?

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

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

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

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.

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

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

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

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,

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

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

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

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

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

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.

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?

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');

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.

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

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;

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.

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?

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.

"<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?

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.

I am on 10.2.0.4 and MVIEW recreation fails at below error.

Could yoy please help :

===
SQL> CREATE   MATERIALIZED VIEW XXADSMV.XXADS_PIPELINE_TEST_27_MV
  2    ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  3    STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  5    TABLESPACE "XXADS_DATA"
  6    BUILD IMMEDIATE
  7    USING INDEX
  8    REFRESH FORCE ON DEMAND
  9    USING DEFAULT LOCAL ROLLBACK SEGMENT
 10    DISABLE QUERY REWRITE
 11  as with ItemInfo as (
 12    select
 13      msi.organization_id,
 14      msi.inventory_item_id,
 15      msi.segment1 as ItemNumber,
 16      msi.primary_unit_of_measure,
 17      mic.category_id,
 18      mc.structure_id,
 19      mc.segment1 as ProductType,
 20      mc.segment2 as ProductLine,
 21      mc.segment3 as ProductSize,
 22      mc.segment4 as ProductSeries,
 23      mc.segment5 as ProductBrand
 24    from apps.mtl_system_items msi
 25    join apps.mtl_item_categories mic
 26    on msi.organization_id = mic.organization_id
 27    and msi.inventory_item_id = mic.inventory_item_id
 28    join apps.mtl_categories mc
 29    on mic.category_id = mc.category_id
 30    join apps.mtl_category_sets mcs
 31    on mc.structure_id = mcs.structure_id
 32    where mcs.category_set_name = 'Inventory'
 33  ),
 34  jrs as (
 35    select salesrep_id, name, salesrep_number, org_id
 36    from apps.JTF_RS_SALESREPS
 37  ),
 38  OrderDetail as (
 39    select ooha.attribute1 as ooha_attribute1, ooha.order_number as ooha_order_number, oola.Line_Number as oola_Line_Number, ooha.transactional_curr_code as ooha_transactional_curr_code, ooha.booked_date as ooha_booked_date,
 40      ooha.price_list_id as ooha_price_list_id, ooha.header_id as ooha_header_id,
 41  ooha.order_type_id as ooha_order_type_id, ooha.sold_to_org_id as ooha_sold_to_org_id,
 42      ooha.org_id as ooha_org_id, oola.ship_from_org_id as oola_ship_from_org_id,trunc(oola.creation_date) as Entry_dt, oola.schedule_ship_date as ship_dt, oola.
 43  ordered_item as Product,    oola.line_category_code as oola_line_category_code, oola.ordered_quantity as
 44   oola_ordered_quantity, oola.inventory_item_id as oola_inventory_item_id, oola.unit_selling_price as oola_unit_selling_price,
 45      oola.sold_to_org_id as oola_sold_to_org_id, oola.ship_to_org_id as oola_ship_to_org_id,
 46      oola.order_quantity_uom as oola_order_quantity_uom, oola.unit_list_price as
 47  oola_unit_list_price, qp.name as qp_name, ottt.name as ottt_name, otta.end_date_active as otta_end_date_active,
 48      otta.attribute10 as otta_attribute10, muom.unit_of_measure as muom_unit_of_measure, ItemInfo.ItemNumber as IntemInfo_ItemNumber, ItemInfo.primary_unit_of_measure,
 49      ItemInfo.category_id as ItemInfo_category_id, ItemInfo.ProductType as ItemInfo_ProductType, ItemInfo.ProductLine ItemInfo_ProductLine, ItemInfo.ProductSize
 50  as ItemInfo_ProductSize,ItemInfo.ProductSeries as ItemInfo_ProductSeries,ItemInfo.ProductBrand as ItemInfo_ProductBrand,cic.item_cost as Unit_Cost, rc.customer_name, gdr.from_currency, gdr.to_currency,
 51      gdr.conversion_rate, jrs.salesrep_id, jrs.name as Salesperson, jrs.salesrep_number
 52    from apps.oe_order_headers_all ooha
 53    join apps.qp_price_lists_v qp
 54    on ooha.price_list_id = qp.price_list_id
 55    join apps.oe_order_lines_all oola
 56    on ooha.header_id = oola.header_id
 57    join apps.oe_transaction_types_tl ottt
 58    on ooha.order_type_id = ottt.transaction_type_id
 59    join apps.oe_transaction_types_all otta
 60    on ottt.transaction_type_id = otta.transaction_type_id
 61    and oola.org_id = otta.org_id
 62    join apps.mtl_units_of_measure muom
 63    on oola.order_quantity_uom = muom.uom_code
 64    join ItemInfo
 65    on oola.ship_from_org_id = ItemInfo.organization_id
 66    and oola.inventory_item_id = ItemInfo.inventory_item_id
 67    join apps.cst_item_costs cic
 68    on oola.inventory_item_id = cic.inventory_item_id
 69    and oola.ship_from_org_id = cic.organization_id
 70    and cic.cost_type_id = 1
 71    join apps.ra_customers rc
 72    on ooha.sold_to_org_id = rc.customer_id
 73    left outer join apps.gl_daily_rates gdr
 74    on trunc(ooha.booked_date) = gdr.conversion_date
 75    and ooha.transactional_curr_code = gdr.from_currency
 76    and gdr.to_currency = 'USD' -- all money reported in USD
 77    left outer join jrs
 78    on oola.salesrep_id = jrs.salesrep_id
 79    and oola.org_id = jrs.org_id
 80    --
 81    where qp.name <> 'Conversion Pricelist' -- exclude converted orders
 82    and ooha.org_id in ('132','133') -- US/Canada only
 83    and oola.shipment_number = 1 -- original transaction only - not line splits
 84    and trunc(ooha.booked_date) is not null -- orders must be at least booked to be reported
 85    -- last business day: line created OR header booked
 86    and (
 87      (
 88        trunc(ooha.booked_date) = '27-AUG-09'
 89      ) OR (
 90        trunc(oola.creation_date) = '27-AUG-09' and trunc(oola.creation_date) > trunc(ooha.booked_date)
 91      )
 92    )
 93    -- no entered or cancelled lines
 94    and oola.flow_status_code <> 'ENTERED'
 95    and oola.flow_status_code <> 'CANCELLED'
 96    -- exclude interplant orders
 97    and ottt.name not like '%Internal%'
 98    -- exclude credit memos
 99  --zzz  and nvl(otta.end_date_active,sysdate+1) > sysdate
100    and nvl(otta.attribute10,'N') = 'Y' -- do not return credit memos
101  ),
102  OrderDetailSamDayShip as (
103    select ooha.order_number as ooha_order_number,
104      ooha.booked_date as ooha_booked_date,
105      ooha.header_id as ooha_header_id,
106      oola.Line_Number as oola_Line_Number,
107      trunc(oola.creation_date) as Entry_dt,
108      SUM(oola.ordered_quantity) as oola_ordered_quantity
109    from apps.oe_order_headers_all ooha
110    join apps.qp_price_lists_v qp
111    on ooha.price_list_id = qp.price_list_id
112    join apps.oe_order_lines_all oola
113    on ooha.header_id = oola.header_id
114    join apps.oe_transaction_types_tl ottt
115    on ooha.order_type_id = ottt.transaction_type_id
116    join apps.oe_transaction_types_all otta
117    on ottt.transaction_type_id = otta.transaction_type_id
118    and oola.org_id = otta.org_id
119    join ItemInfo
120    on oola.ship_from_org_id = ItemInfo.organization_id
121    and oola.inventory_item_id = ItemInfo.inventory_item_id
122    inner join OrderDetail OD
123    on oola.header_id = OD.ooha_Header_id
124    and oola.Line_Number = oola_Line_Number
125    --
126    where qp.name <> 'Conversion Pricelist' -- exclude converted orders
127    and ooha.org_id in ('132','133') -- US/Canada only
128    and oola.shipment_number > 1 -- all shipments other than the original booked order line (shipment 1)
129    and trunc(ooha.booked_date) is not null -- orders must be at least booked to be reported
130    -- last business day: line created OR header booked ----should be the same as the date of the original booked order transaction date
  and OD.Entry_dt = trunc(oola.creation_date)
131  132    -- no entered or cancelled lines
133    and oola.flow_status_code <> 'ENTERED'
134    and oola.flow_status_code <> 'CANCELLED'
135    -- exclude interplant orders
136    and ottt.name not like '%Internal%'
137    -- exclude credit memos
138  --zzz  and nvl(otta.end_date_active,sysdate+1) > sysdate
139    and nvl(otta.attribute10,'N') = 'Y' -- do not return credit memos
140  Group BY ooha.order_number,
141      ooha.booked_date,
142      ooha.header_id,
143      oola.Line_Number,
144      trunc(oola.creation_date)
145  ),
146  OrderDetailCalcs as (
147    select
148      OD.ooha_booked_date,
149      apps.XXADS_COMMON_UTILS_INT.Get_Region_From_Shipto(
150        OD.oola_ship_to_org_id,
151        OD.ooha_attribute1,
152        OD.oola_sold_to_org_id,
153        decode(OD.oola_ship_from_org_id, 648, 'NON-US', 'US')
154      ) as Rgn,
155      OD.ItemInfo_ProductType as Product_Type,
156      OD.ooha_order_number as order_number,
157      OD.ItemInfo_ProductLine as Line,
158      OD.customer_name,
159      OD.salesperson,
160      OD.salesrep_number,
161      OD.Entry_dt,
162      OD.Ship_dt,
163      OD.Product,
164      (OD.oola_ordered_quantity + CASE
165                                    When ODSDS.oola_ordered_quantity IS NULL Then
166  0
167                                    Else ODSDS.oola_ordered_quantity
168                                  End) as qty_ord,
    CASE
169  170        When ODSDS.oola_ordered_quantity IS NULL Then 0
171        Else ODSDS.oola_ordered_quantity
172      End as Qyt_Ord_ShippedSameDay,
173      OD.unit_cost,
174      OD.oola_unit_selling_price as unit_selling_price,
175      OD.oola_unit_list_price as unit_list_price,
176      OD.oola_order_quantity_uom as ordered_quantity_uom,
177      case OD.oola_line_category_code
178        when 'RETURN' then -1
179        else 1
180      end as qty_rtn_factor,
181      case OD.ItemInfo_ProductType
182        when 'Misc' then 0
183        else apps.XXADS_COMMON_UTILS_INT.get_lb_conversion_rate(
184          OD.muom_unit_of_measure,
185          OD.primary_unit_of_measure,
186          OD.oola_inventory_item_id
187        )
188      end as lbs_factor,
189      case OD.ooha_transactional_curr_code
190        when 'USD' then 1 -- USD-to-USD conversion lookup not defined
191        else OD.conversion_rate
192      end as currency_factor
193    from OrderDetail OD
194    Left Outer Join OrderDetailSamDayShip ODSDS
195      ON OD.ooha_Header_Id = ODSDS.ooha_header_id
196      AND OD.oola_Line_Number = ODSDS.oola_Line_Number
197  )
198  select
199    Rgn,
200    product_type,
201    order_number,
202    line,
203    customer_name,
204    salesperson,
205    salesrep_number,
206    entry_dt,
207    ooha_booked_date,
208    ship_dt,
209    product,
210    qty_ord * qty_rtn_factor as qty_ord,
211    qty_ord * qty_rtn_factor * lbs_factor as qty_pounds,
212    unit_selling_price * currency_factor as unit_price,
213    qty_ord * qty_rtn_factor * unit_selling_price * currency_factor as qty_ord_x_unit_price,
214    ordered_quantity_uom,
215    case
216      when product_type = 'Misc' then 0
217      when lbs_factor is null then null
218      when lbs_factor = 0 then null
219      else unit_selling_price * currency_factor * qty_rtn_factor / lbs_factor
220    end as yield,
221    unit_list_price * currency_factor as list_price,
222    case
223      when unit_list_price is null then 0
224      when unit_list_price = 0 then 0
225      else (unit_list_price - unit_selling_price) / unit_list_price * 100.0
226    end as disc_price,
227    unit_cost,
228    qty_ord * qty_rtn_factor * unit_cost as ext_cost
229  from OrderDetailCalcs
230  ;
as ItemInfo_ProductSize,ItemInfo.ProductSeries as ItemInfo_ProductSeries,ItemInfo.ProductBrand as ItemInfo_ProductBrand,cic.item_cost as Unit_Cost, rc.customer_name, gdr.from_currency, gdr.to_currency,
                                                                                                               *
ERROR at line 50:
ORA-32036: unsupported case for inlining of query name in WITH clause

=====

$ oerr ora 32036
32036, 00000, "unsupported case for inlining of query name in WITH clause"
// *Cause:  There is at least one query name which is inlined more than once
//          because it's definition query is too simple and references another
//          query name. This is currently unsupported yet.
// *Action: remove such query name and retry

Hi tom,

i need some help regarding materialized view refresh.

I create a materialized view on emp table, which is available in any database.

create materialized view log on emp;
and then i created the materialied view 
create materialized view emp_mv refresh fast as select * from emp;

now when i issue the command

truncate table emp_mv;

the data from materialized view is truncated. 

now when i try to 

SQL> select * from emp_mv;
no rows selected

why is that the materialied view is empty even though i created the materialized view with refresh fast option.


my question is when i create the materialized view with refresh 

tom

sincerely i did not understand what you tried to convey by saying

"tell me first why you think a segment you just truncated should have data??!?!?!?! "

am sorry for that

my question was why is the materialized view empty and how do i refresh it.

a small question
how can i include your recent comments when i try to reply

Why did you truncate it?
i truncated to learn about materialized views

Why did you think that after truncating it you would see anything in it?
because i read that refresh fast will refresh the data

How do you think this could be incrementally maintained after truncating it?
hmm i was testing the refresh fast keyword.

tom, is it a must to create a materialized view log when we want the data in the materialized view be refreshed or when we create a materialized view with refresh fast option. is yes what is the data in the materialized view contains aggregrate functions.

i have succeeded in re populating the materialized view by issuing

SQL>begin
dbms_mview.refresh('emp_mv');
end;
/

thank you

Hi tom,

Earlier materiliazed view took 6 hr to create.
Now its taking more than 24 hr...

Please suggest ...

i am using "REFRESH FORCE ON DEMAND" while creating materilaized view

Manish

refresh on demand means rebuild the mview if there is any dml issued on the master tables. so its quite obvious that the data on the master tables is not getting changed

Tom,
I have a single table with 30 columns an 200 million records.
The data in the table will be used to present on the web application for viewing.

My approach is creating a Materlialized view with all the aggregations and have the application build on materlialized view(MV).

On a daily frequency nearly 1 million records are received from ODS.
If I utilize Fast Refresh option to refresh the data incrementally on the Materialzed, do you think the performance degrades while selecting(view only)the data by the application.

Do you suggest any other better way or creating the MV with optimal refresh option.

Thanks

Tom,
Thanks for the response

I will appreciate if you can clarify
a) build mv's
-- I will build mv's based on the detail table
b) app queries details
-- The table is populated daily
c) we rewrite query against mv for you
**** The queries are only required to should the aggregate information not details anytime.
So If I point the application to the details tables (b), the Query Rewrite functionality will point to the materialized view (a) ????

Also, I will apprecaite if you could clarify
Is the 'fast fresh option' in Materialized view(MV) only updates the incremental data in the MV or it refreshes the entire MV.

Response time is very important,The application dispalys the data for only one record.
Which means the search criteria is the ACOUNT_NO, so the data related to ONE account number is displayed.

Thanks again

Thanks a lot.

Hi Tom,
Here a question about concurrent MVIEWS refresh using locks (for update cursor).
We are using Oracle 10g EE 10.2.0.4.0 and we have two materialized views MV1 and MV2 refreshed (complete on demand) by a stored procedure (given below).

This procedure is called by different sessions/transactions (at the same time), that’s why we are using a for update cursor 'lock' (mylock_MV1).
The MV1 refresh takes about 100 secondes.

MV1 is queried by MV2 where MV2 is the only Materialized View queried by Front Office (FO) packages. These packages have to check the MV2 lock ('FO_PKG_MV2') before querying it to be sure that the MView is not being refreshed.

During procedures test, we discover that in some case, MV2 doesn’t contain any data.

dbms_output.put_line before and after every lock show us that the for update cursor mylock_MV1 is opened at the same time (without waiting) by "concurrent procedures calls" !
In our case, a second procedure call starts the MV1 refreshing just before the first call starts the MV2 refresh.

Really starnge ! We have already used locks in this way with tables and it worked very well.

Any solution ? any idea ?
Thanks.

Procedure MV_refresh is
  cursor mylock_MV1 is
    select * from lock_table where lock_col = 'MV1_refresh' for update;
  onelock_MV1 mylock_MV1%ROWTYPE;

  cursor mylock_MV2 is
    select * from lock_table where lock_col = 'FO_PKG_MV2' for update;
  onelock_MV2 mylock_MV2%ROWTYPE;

  ct_MV1 number;

begin
  dbms_output.put_line(to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') || ' before open lock_MV1 ');
  
open mylock_MV1;
  fetch mylock_MV1
    into onelock_MV1;
  
  dbms_output.put_line(to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') || ' after open lock_MV1');

  DBMS_MVIEW.REFRESH('MV1', 'C', '', TRUE, FALSE, 0, 0, 0, FALSE);

  dbms_output.put_line(to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') || ' refresh MV1 done ');
  
  select count(*) into ct_MV1 from MV1;
  
 if (ct_MV1 != 0) then
    dbms_output.put_line(to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') || ' before open lock_MV2');

    open mylock_MV2;
    fetch mylock_MV2
      into onelock_MV2;
    dbms_output.put_line(to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') || ' after open lock_MV2');

    DBMS_MVIEW.REFRESH('MV2', 'C', '', TRUE, FALSE, 0, 0, 0, FALSE);

    -- MV2 depends on MV1 and it is the only MV called by FO packages
    dbms_output.put_line(to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') || ' refresh MV2 done');

    close mylock_MV2;

  end if;

  close mylock_MV1;

end MV_refresh;

Oh ! :) Yes, If you want but I do not think it's a design problem. Here, I've simplified the names of views, of cursors, the number of instructions ... and I understand that one can wonder about the usefulness of all this.

No matter the need. The question is not there.
But I must correct : not "Is Called" but "can be called" at the same time.
These calls are made by programs that, after insertions, call the procedure MV_REFRESH to update the views. These procedure calls can be concurrent.

Whatever the topic, I do not understand how the locks with "for update cursor" work very well when we want to handle concurrent access to tables and do not work when try to do the same thing with M-Views (using dbms_mviews.refresh) !

Or otherwise : How is it possible to open a "for update cursor" already opened on the same line!

select count(*) into ct_MV1 from MV1;
  
 if (ct_MV1 != 0) then

Did you notice that your call to DBMS_MVIEW.REFRESH:
DBMS_MVIEW.REFRESH('MV2', 'C', '', TRUE, FALSE, 0, 0, 0, FALSE);

specifies (as the 9th parameter) ATOMIC_REFRESH=FALSE?

This is what ATOMIC_REFRESH does:
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.

So, if you are trying to prevent inconsistent results when querying the MV (such as the zero rows that you mention), you may be able to accomplish that by using ATOMIC_REFRESH=TRUE instead of playing around with locks.

The refresh may take longer (with ATOMIC_REFRESH=FALSE, DBMS_MVIEW can do a TRUNCATE, but can not if TRUE), but you should not have to worry about incomplete results.

I agree the design is a problem.

I was not aiming to 'make the locks work'. My design suggestion was: don't play around with locks. DBMS_MVIEW already provides the protection desired (if you don't override the default).

Hi Michael,
Thank you for reading my procedure.
>>Did you notice that your call to DBMS_MVIEW.REFRESH:
>>DBMS_MVIEW.REFRESH('MV2', 'C', '', TRUE, FALSE, 0, 0, 0, FALSE);
>>specifies (as the 9th parameter) ATOMIC_REFRESH=FALSE?

Yes I know it, but ATOMIC_REFRESH was set to FALSE to get better performance during MV refreshing and querying. It was our first issue.

Hi Tom, thank you for your reply.
You give me the most important part of answer. I didn’t know that locks can be released by a DBMS_REFRESH !
But I am always interested in an alternative.

At the same time, I do not understand why you would like more details about the design! My question remains technical!

This is all what can I say about "design":

We are almost in real time environment (without using specefic hardware/softawre). Data should be available from their insertions. It would have been easier if we were in a batch case. :)

The MVIEW was a query requested by FO (Interface). Given the number of users, more and more important, and the size of the DB, we decided to transform this query in materialized view. This query is not really fast, we compute "history" (first use date, last use date, number of use) of all existing "items" (house, car, screw, document, file, or any other things ...) in our DB.

At the BO (Back Office) layer, the DB receive thousands of insert per day. We have a "tool" (program) for managing all these incoming data. This tool (after transforming data) call many procedures (organized in packages). After that, the same program call the MV_refresh procedure (and once again, we can not do it in batch mode (user expectations)). To accelerate this process, we launch between 10 and 20 instances of this tool.

Hence concurrent calls (of MV_refresh procedure) !!!

And for information, we have others tools (programs) which compute aggregates/OLAP cubes/reporting in batch mode. But they are unrelated to our problem.

For some reasons, I can't give more details!
So, I hope these explanations have attracted your interest.

>>select count(*) into ct_MV1 from MV1;
>>if (ct_MV1 != 0) then
>>ouch, I always dislike that construct.

I can understand , it is temporary.
Thank you.

Mohamed.

Hi,

DB Version is 10.2.0.4

Recently Noticed a MV refresh taking various time to complete. The MV refresh starts at 9pm and completes some time at 1am or 12am are sometimes very fast at 10pm. The number of records are almost same. There are no locks in the db during that time.. Opening the MV script i could see


REFRESH COMPLETE
WITH ROWID
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS



Using default local rollback segment - is that allocation of rollback segment causes this

Referred the manul

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm - where it says this clause is not valid if the DB is in automatic undo mode.

thanks,
baskar.l

I never said there was no link between the nature of the problem and the technical solution. We are faced with it every day ...


I said MY question is technical! "Why we can not put a lock with a dbms_refresh" and you've already answered. "Is a lot like dbms_view.refresh DDL." which surprised me and still amazes me ...


You haven't yet told anyone WHY you need to have this MV refreshed by many processes, at about the same time.

I invite you to reread my post.


'hahahahahah - I fell out of my chair. There is no such thing...'

I fell out my chair too. I had never seen such a response.

The quality of exchange is beyond my capabilities.
And I think I'll stop at that level.

Thank you anyway.

Tom,

I have large data (200 million records) and would like to use analytical functions to create the materilized view with FAST REFRESH option as complete refresh takes more time.
Limitation for Fast Refresh is not to use analytical functions? am I correct?
Is there any work around to aggregate the data using analytical functions with increamental refresh.
we have 11gR2

Thanks

Do any of these things sound familiar?

"We can't change the code it's too much work..."

or

"...it will cost too much..."

or

"...it will require too much testing..."

or

"...it will take too much time..."

etc?

That's what Tom's getting at. Once you develop some junk to get by to get it "working", you will be maintaining that junk for many years and require far much more of your time than doing it right in the first place.

Thanks for the info.
I agree your point about analytical function problem in Materialized view.

I am thinking of creating a summary table (200 million records) that is updated on a daily frequency with the delta records (approximately 1 million every day).
This way I can avoid materilaized view

Please let me know if my thought process is viable.

Thanks

Hi Tom
I've copy two Production databases to Test environment (using hot backup & restore) and changed their names (also the global_name is changed).
now, if I look at dba_registered_snapshots I see that production db name exists in SNAPSHOT_SITE column.
I did a fast refresh in test environment and it worked well, how come ? how snapshot logs is cleaned if the SNAPSHOT_SITE is different from the new database name ?
where do I see the relation between the SNAPSHOT_SITE and the database it related to ?

the database links have the same name as production but it now points to the new test database (also, there are no connections between test and prod environments)

thanks
Ofir

Tom,

Following is the scenerio

Table A 200 million records (representing aggregate data)
Table B Staging table 3 million records
Table C working table

Step 1: Match the data from A and B on say ACCOUNT_NUM
column
Step 2: Populate C from step 1 (Say 3 million)
Step 3: Slect data from C performing analytical functions
and update TABLE A.

Please let me know if it does not make sense

Thanks

merge into a
using (select a.columns, b.columns from a, b where a.account_num(+) = b.account_num and ... ) X
on (a.account_num = x.account_num)
when matched then update
when not matched then insert

Ref. Materialized views and analytic functions.

What might help is to create a materialized view with a structure that is optimized for answering the analytic function as quickly as possible.

For the query:

select a,b,c, row_number() over (partition by a order by b) rn
from t

... you might create a materialized view with just the a, b and c columns of t, using the ON PREBUILT TABLE option to register a prebuilt index organized table with (a,b) as the composite primary key (if they are unique, of course).

Not always do-able, but often worth considering.

An index on (a,b) and optionally on (a,b,c) might be just as good.

Hi Tom,

I found that a materialized view with a user defined function is not fast refreshable, even this funciton is delared as determistic. Is this a known issue? Any workaround?

My DB version is Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 , and the paramer compatible = '10.2.0.1.0'

Below is the detail scritps to re-produce the issue:
-----------------
create table test1 ( emp_id int primary key, emp_name varchar2(50), salary number(10) );

create materialized view log on test1 with sequence , rowid (emp_id, emp_name, salary ) including new values;

CREATE OR REPLACE function YZHANG.my_upper(s1 IN varchar2 ) return varchar2
DETERMINISTIC is
begin
return upper(s1);
end;
/

create materialized view test_mv1
refresh fast
on demand
as
select my_upper(emp_name) as emp_name,
sum(salary) as duration,
count(*) as cnt
from test1
group by my_upper(emp_name)
;

ORA-12015: cannot create a fast refresh materialized view from a complex query

If I use upper() function instead of my_upper(), the above Mview is fast refreshable.

Many thanks for your support!

merge into a
using (select a.columns, b.columns from a, b where a.account_num(+) = b.account_num and ... ) X
on (a.account_num = x.account_num)
when matched then update
when not matched then insert

How is the performan of using the above merge in big table
The table A is said to be around ~200 million records.

Thanks

Tom,

Can you control what tablespace your mv uses to refresh in? E.g. temp vs user tablespace? I want it to use temp but it is not. So I get false alarms for space when it shoots up during the refresh. I don't know which of the 20 storage parameters controls this. I read them all in the documentation but didn't see one that affects this.

Hi Tom,

Am back with another question which might sound pointless.

can i partition a Materialized View or can we create a materialized view with partitions based on range. etc

Hello Tom,
I need to create a MVIEW in a distributed DB.
Say I have famouse tables dept and emp (with all constraints , PK ,FK) in site1 DB -- db-a in schema sch-a
It also has sales table which references empno as FK.
This table is specific to each site so data in sales@site1 will be different to data in sales@site2.
stie2 has dblinks to access site1 tables.

I want to create MVIEW dept and emp in site2 DB-- db-b in schema sch-b

The requirement is one directional replication from site1 to site2

So Whenever any DML activity happens on dept or emp table in site1 DB -- db-a it should be propagated to the remote site. But rememeber there is sales@site2 which has FK empno from emp Mview in site2.( Was thinking of putting on delete cascade )

It need not be instantaneous. But when refresh of MV is done manually it should get in sync with site1

What kind MVIEW and refresh should be done for site2 DB - db-b

In site 2. Even Site2 MV will have FK constraint on emp table thru deptno.

Is it possible to make bi-directional replication ?How ?

Thanks Tom I hear you. And I agree.

Ok How would we do the MVIEW and it refresh if we assumed
There will be only inserts on the site1 on dept and emp table. No deletes , updates.

Then what would be the implementation like for one-directional refresh from site1 to site2.

I tried doing a complete refresh and it fails due to integrity constraint violation.I guess it does a delete all then insert for complete refresh.

I am new to MVs. Will incremental refresh of dept and emp MView work on site2 ? Any example on how to create incremental refresh.

I am doing this just as a test case to understand concepts of Mview, not for production implementation

I am a fresher ..learning oracle Admin...i found these Q and ans more helpfull than just studying from books and pdfs...specially i had lot many practical issues ...could solve taking help of these links ...Excellent...Thanks...

Hi Tom,

MV on three base tables - 30M, 10M and 10M rows with aggregation resulting into 2.5 M rows, with PCT refresh takes around 2.5 hours, and if we load data into work table for a partition and exchange partition with final tables, takes around 10 minutes.
If performance difference is so large, then what other factors should be considered to justify MV?
Thanks

Hi Tom,

The Script is:
(All base tables are partitioned (range) on monthkey)
CREATE MATERIALIZED VIEW MV
TABLESPACE TBS1
NOLOGGING
PARTITION BY RANGE (monthkey)
(
PARTITION mv_201001 VALUES LESS THAN (201002)
NOLOGGING
NOCOMPRESS
TABLESPACE TBS1,
PARTITION mv_201002 VALUES LESS THAN (201003)
NOLOGGING
NOCOMPRESS
TABLESPACE TBS1,
PARTITION mv_201003 VALUES LESS THAN (201004)
NOLOGGING
NOCOMPRESS
TABLESPACE TBS1,
PARTITION mv_201004 VALUES LESS THAN (201005)
NOLOGGING
NOCOMPRESS
TABLESPACE TBS1,
PARTITION mv_201005 VALUES LESS THAN (201006)
NOLOGGING
NOCOMPRESS
TABLESPACE TBS1,
PARTITION mv_201006 VALUES LESS THAN (201007)
NOLOGGING
NOCOMPRESS
TABLESPACE TBS1
)
NOCACHE
NOCOMPRESS
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
BUILD IMMEDIATE
USING INDEX
TABLESPACE TBS1
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT A.MONTHKEY MK,
B.MONTHKEY MK1,
C.MONTHKEY MK2,
A.CATEGORY,
B.CENTRE,
A.PRODUCT,
A.ACCOUNT,
A.CATCODE,
C.CUSTTYPE,
C.CUSTSUBTYPE,
SUM (A.CATAMOUNT) AVALUE,
COUNT (A.CATAMOUNT) CATCNT,
COUNT (A.MONTHKEY) AVOLUME,
COUNT ( * ) TVOLUME
FROM A,
B,
C
WHERE A.ACCOUNT = B.ACCOUNT
AND A.MONTHKEY = B.MONTHKEY
AND A.CUSTOMER = C.CUSTOMER
AND A.MONTHKEY = C.MONTHKEY
GROUP BY A.MONTHKEY,
A.CATEGORY,
B.CENTRE,
A.CATEGORY,
A.ACCOUNT,
A.CATCODE,
C.CUSTTYPE,
C.CUSTSUBTYPE,
B.MONTHKEY,
C.MONTHKEY

PCT is used for each subsequent month' data refresh in MV
Version: 10gR1
Thanks

Hi Tom,

Tried on base tables:-
1)INSERT /*+ APPEND */ INTO... and
2)Load into work tables, and then exchange partitions...
and used
BEGIN
DBMS_MVIEW.REFRESH (LIST =>'MV',
METHOD =>'P',
atomic_refresh=>FALSE, parallelism=>2);
END;
but still takes the same time.

Can you let me know about..,truncate and reload of a partition in the MV?

Regards,

Hello,

my need is to speed up a join query with high cardinality in an OLTP on two "big tables".

But, I am wondering of MV REFRESH Performances.

Endeed, for every test I do, the Refresh cost is much higher than the table size even when modifying a single line.

I tested the above example with table M and I (with refresh fast, but without "On commit" option").

I even added indexes on the mv as recommanded to improve FAST refresh :
CREATE index "m_i_mv~mrow" on m_i_mv(m_rowid);
CREATE index "m_i_mv~irow" on m_i_mv(i_rowid);
CREATE index "m_i_mv~mid" on m_i_mv(mid);
CREATE index "m_i_mv~iid" on m_i_mv(iid);

But, nothing to do, at each refresh, the cost is a lot higher than the simple size of the tables(2Mo each):
------------------------------------------------------------
EXEC | "short SQL txt" |"BGETS(Mo)"|
------------------------------------------------------------
1 | BEGIN DBMS_MVIEW.REFRESH('m_i_MV','F');| 79,227 |
1 | /* MV_REFRESH (INS) */ INSERT INTO "SYS| 6,633 |
0 | /* MV_REFRESH (INS) */ INSERT INTO "SYS| 0 |
------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 650 (100)|
| 1 | LOAD TABLE CONVENTIONAL | | | | |
| 2 | NESTED LOOPS | | | | |
| 3 | NESTED LOOPS | | 1 | 96 | 650 (1)|
| 4 | VIEW | | 1 | 58 | 649 (1)|
|* 5 | HASH JOIN RIGHT SEMI | | 1 | 162 | 649 (1)|
|* 6 | TABLE ACCESS FULL | MLOG$_I | 33 | 4554 | 399 (0)|
| 7 | TABLE ACCESS FULL | I | 60012 | 1406K| 249 (1)|
|* 8 | INDEX UNIQUE SCAN | SYS_C0015148 | 1 | | 0 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID| M | 1 | 38 | 1 (0)|
-----------------------------------------------------------------------------------

The same behaviour occurs with my applicative tables (96Mo and 32Go).

Is there something wrong with my tests or is it the "normal" way of working of the VMs?

If yes, I think I should better update the VM myself than using the refresh feature... strange!

Thanks in advance for your recommandations.

Regards.
Sylvain

Hi Tom,
I did the trace of the refresh (PCT), and found that during insert it uses the hint
1) INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO ....
2) In select statement of MV - gives error encountered: ORA-10980
3) Uses TABLE ACCESS FULL MLOG$_...

Is there any thing other happening in the trace, which will help to understand, why it is slow?
Thanks

Hi Tom,

thanks to take time to answer.

Well, my concern is really around the MV refresh, but I will explain my "need" here after.

1) -------- MV REFRESH perform very badly ---------
But first, on the MV, each time a REFRESH occurs, it causes
to do a FTBS on the "i" big table (actually 32Go and 600Go in target size), even if there are only few lines in the MLOG$ tables (see previous explain PLAN).

So, every 2 hours, I have a refresh that takes (as of now) 12 minutes. But how much in the future??? (1 hour? 2 hours?)!

That's not acceptable. That's the origin of my question...

With a colleague, we found a little clue : there is a HINT HASH_SJ on the Refresh which force the FTBS and th hash_sj to occur...

.../* MV_REFRESH (INS) */ INSERT INTO ...
SELECT /*+ HASH_SJ */ CHARTOROWID("MAS$"."M_ROW$$") RID$
FROM "SAP"."MLOG$_T_EKPO" "MAS$" WHERE "MAS$".SNAPTIME$$ > :B_ST0
)) AS OF SNAPSHOT(:B_SCN) "JV$", ...

And This is a big drawback according to our needs!

2) ------ Business case and need -----
In my real business test case, the MV is perfect from a "select" point of view.

The need is to do a "simple" report on good movements having the stock to date by division and other needs.

The problem is that the design (done by the ERP editor) is not perfect with the 2 tables having each 1 selective column (m.date, i.division).

My customer (a retailer) has 1000 shops who make around 20 documents of 100 items (materials) a day (2M lines a day).

The date is in the "header table" and the division in the "item" table.

So, when joining the two tables, we have a big cardinality. For me, the solution were the VM (as of the cost of refresh cancel all gains on the join).

And endeed, With a select on the MV versus a join on the master tables I have around 10 times less blocs!

*******************************************************************************************
**** TEST CASE ***
*******************************************************************************************
With this script, you can reproduce the case :
CREATE TABLE m (
mID NUMBER NOT NULL PRIMARY KEY,
bDT VARCHAR2(8) NOT NULL,
col3 NUMBER NOT NULL,
col4 NUMBER NOT NULL,
col5 NUMBER NOT NULL,
col6 VARCHAR2(50) NOT NULL
);
CREATE TABLE i (
mID NUMBER NOT NULL,
iID NUMBER NOT NULL, --item number
div VARCHAR2(4) NOT NULL,
mat VARCHAR2(18) NOT NULL,
qty NUMBER NOT NULL,
col6 VARCHAR2(50) NOT NULL,
PRIMARY KEY (mID,iID)
);

-- --------------------------------------------------------------------------------
-- Session # - start populating the tables (warning : can be very long)
-- Table m : 200K lines and table i 20M lines
-- --------------------------------------------------------------------------------
DECLARE
mID NUMBER;
DATEC8 VARCHAR2(8);
DIV VARCHAR2(4);
MAT VARCHAR2(18);
qty number;
BEGIN
FOR bdt IN 1..100 LOOP -- 100 dates
DATEC8 := to_char(sysdate - bdt, 'YYYYMMDD');
FOR i IN 1..20 LOOP -- 20docs/div/day
null;
FOR j IN 100..200 LOOP -- 100 MAGs
mID := i_seq.NEXTVAL;
DIV := to_char(j);
INSERT INTO m VALUES (mID, DATEC8, i, i, i, 'xxxxxxxxxxxxxxxxxxxxxxxxx');
FOR k IN 1..100 LOOP -- Materials (100)
INSERT INTO i VALUES (mID, k, DIV, k, k, 'xxxxxxxxxxxxxxxxxxxxxxxxx');
END LOOP;
END LOOP;
COMMIT;
END LOOP;
END LOOP;
END;
/

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
AS
SELECT m.mID,
m.bdt,
i.iID,
i.div,
i.mat,
i.qty,
m.rowid m_rowid,
i.rowid i_rowid
FROM i,
m
WHERE i.mID = m.mID;


*******************************************************************************************
**** INDEX ***
*******************************************************************************************
CREATE index "m_i_mv~mrow" on m_i_mv(m_rowid);
CREATE index "m_i_mv~irow" on m_i_mv(i_rowid);
CREATE index "m_i_mv~mid" on m_i_mv(mid);
CREATE index "m_i_mv~iid" on m_i_mv(iid);
create index "m~bdt" on m(bdt);
create index "i~div" on i(div);
create index "i~divbdt" on m_i_MV(bdt,div);


*******************************************************************************************
**** RESULTS ***
*******************************************************************************************

-- Jointure --
SELECT m.mID,
m.bdt,
i.iID,
i.div,
i.mat,
i.qty,
FROM i,
m
WHERE i.mID = m.mID
AND m.bdt = '20101110'
AND i.div = '100'
;

*******************************************************************************************
--> JOIN : 5547 blocs!
*******************************************************************************************
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 236K| 471 (2)| 00:00:06 |
|* 1 | HASH JOIN | | 2500 | 236K| 471 (2)| 00:00:06 |
|* 2 | TABLE ACCESS FULL | M | 3216 | 99696 | 415 (1)| 00:00:05 |
| 3 | TABLE ACCESS BY INDEX ROWID| I | 2500 | 161K| 55 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | i~div | 2500 | | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("I"."MID"="M"."MID")
2 - filter("M"."BDT"='20101110')
4 - access("I"."DIV"='100')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistiques
----------------------------------------------------------
0 recursive calls
0 db block gets
5547 consistent gets
0 physical reads
0 redo size
97592 bytes sent via SQL*Net to client
1982 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed


-- VM --
SELECT mID,
bdt,
iID,
div,
mat,
qty
FROM m_i_MV
WHERE bdt = '20101110'
AND div = '100'
;


*******************************************************************************************
--> VM : 404 blocs!!!
*******************************************************************************************
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 117K| 48 (0)| 00:00:01 |
| 1 | MAT_VIEW ACCESS BY INDEX ROWID| M_I_MV | 2000 | 117K| 48 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | i~divbdt | 2000 | | 10 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("BDT"='20101110' AND "DIV"='100')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistiques
----------------------------------------------------------
7 recursive calls
0 db block gets
404 consistent gets
33 physical reads
0 redo size
133478 bytes sent via SQL*Net to client
1982 bytes received via SQL*Net from client
135 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed


*******************************************************************************************
--> CONCLUSION :
*******************************************************************************************

SELECT on the VM in this case is very efficient.
However, on each refresh, the FTBS on the biggest table of the DB every 2H is a nightmare...


Thanks in advance for your findings.



Regards.
Sylvain

Sorry for the formatting, I didn't notice this night...

Please find below the explain plans in a more readable format.

For the join (5547 blocs):
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 236K| 471 (2)|
|* 1 | HASH JOIN | | 2500 | 236K| 471 (2)|
|* 2 | TABLE ACCESS FULL | M | 3216 | 99696 | 415 (1)|
| 3 | TABLE ACCESS BY INDEX ROWID| I | 2500 | 161K| 55 (0)|
|* 4 | INDEX RANGE SCAN | i~div | 2500 | | 8 (0)|
---------------------------------------------------------------------------
***************************************************************************
And for the VM (404 blocs):
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 117K| 48 (0)|
| 1 | MAT_VIEW ACCESS BY INDEX ROWID| M_I_MV | 2000 | 117K| 48 (0)|
|* 2 | INDEX RANGE SCAN | i~divbdt | 2000 | | 10 (0)|
--------------------------------------------------------------------------------

Hi Tom,
can you through some light on the MV refresh trace question asked above.
Regards,

Hi Tom,

thanks for the followup and sorry for the late answer.

Yes endeed, statistics have been updated.

Moreover, the behaviour is the same on my DB (11.2.01), on our Productive DB (10.2.04) and  another DB from a colleague of mine (also on 10.2.04 DB) who has noticed the same behaviour on another project.

As requested, find below the Explain with the filter predicates after adding 1 line in "m" table and 100 in "i" table.

NOTE that the DELETE is also very consuming...

alter system flush shared_pool;
EXECUTE DBMS_MVIEW.REFRESH('m_i_MV','F');
SELECT substr("SQL_TEXT",0,40) "SQL txt", EXECUTIONS, BUFFER_GETS, SQL_ID, PLAN_HASH_VALUE FROM V$SQL WHERE SQL_TEXT LIKE '%REFRESH%' ORDER BY BUFFER_GETS DESC;

SQL txt                        EXECUTIONS BUFFER_GETS SQL_ID        PLAN_HASH_VALUE
------------------------------ ---------- ----------- ------------- ---------------
BEGIN DBMS_MVIEW.REFRESH('m_i_          1      297197 ak89cuv8rs7pn               0
/* MV_REFRESH (DEL) */ DELETE           1      151265 1tu4vqnh353w3      2283497834
/* MV_REFRESH (INS) */ INSERT           1      136738 dmnuzuvk7b9ap      4255875859
/* MV_REFRESH (INS) */ INSERT           1        1711 0ay74u9bum6h9      2762657338
SELECT substr("SQL_TEXT",0,60)          1           2 fu3n1csp1r03v      2836784050
SELECT substr("SQL_TEXT",0,50)          1           2 8vscrmm0abtrk      2836784050
SELECT substr("SQL_TEXT",0,30)          2           2 b1qpayb42992k      2836784050
SELECT substr("SQL_TEXT",0,40)          1           2 473azh7618mg2      2836784050
SELECT substr("SQL_TEXT",0,60)          1           2 24anpqmw9tmkq       903671040
/* MV_REFRESH (INS) */ INSERT           0           0 dmnuzuvk7b9ap               0
/* MV_REFRESH (DEL) */ DELETE           0           0 1tu4vqnh353w3               0
/* MV_REFRESH (INS) */ INSERT           0           0 0ay74u9bum6h9               0

SQL>  SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR('1tu4vqnh353w3',1,'TYPICAL') );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  1tu4vqnh353w3, child number 1
-------------------------------------
/* MV_REFRESH (DEL) */ DELETE FROM "SYSTEM"."M_I_MV" SNA$ WHERE
"I_ROWID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ * FROM (SELECT
CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "SYSTEM"."MLOG$_I" "MAS$"
WHERE "MAS$".SNAPTIME$$ > :B_ST1 ) AS OF SNAPSHOT(:B_SCN) MAS$)

Plan hash value: 2283497834

----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |         |       |       | 41455 (100)|          |
|   1 |  DELETE                | M_I_MV  |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI |         |    41 |  6601 | 41455   (2)| 00:08:18 |
|*  3 |    TABLE ACCESS FULL   | MLOG$_I |    33 |  4554 |     2   (0)| 00:00:01 |
|   4 |    MAT_VIEW ACCESS FULL| M_I_MV  |    20M|   443M| 41310   (2)| 00:08:16 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("I_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   3 - filter("MAS$"."SNAPTIME$$">:B_ST1)

Note
-----
   - dynamic sampling used for this statement (level=2)


29 ligne(s) sÚlectionnÚe(s).

SQL>  SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('dmnuzuvk7b9ap',1,'TYPICAL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  dmnuzuvk7b9ap, child number 1
-------------------------------------
/* MV_REFRESH (INS) */ INSERT INTO "SYSTEM"."M_I_MV" SELECT /*+
NO_MERGE("JV$") */ "MAS$0"."MID","MAS$0"."BDT","JV$"."IID","JV$"."DIV","
JV$"."MAT","JV$"."QTY","MAS$0".ROWID,"JV$"."RID$" FROM ( SELECT
"MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM "SYSTEM"."I" "MAS$" WHERE
ROWID IN (SELECT  /*+ HASH_SJ */  CHARTOROWID("MAS$"."M_ROW$$") RID$
 FROM "SYSTEM"."MLOG$_I" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST1 ))
AS OF SNAPSHOT(:B_SCN) "JV$", "M" AS OF SNAPSHOT(:B_SCN)  "MAS$0" WHERE
"JV$"."MID"="MAS$0"."MID"

Plan hash value: 4255875859

-----------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |              |       |       |   137K(100)|
|   1 |  LOAD TABLE CONVENTIONAL      |              |       |       |            |
|   2 |   NESTED LOOPS                |              |       |       |            |
|   3 |    NESTED LOOPS               |              |     3 |   276 |   137K  (1)|
|   4 |     VIEW                      |              |     3 |   198 |   137K  (1)|
|*  5 |      HASH JOIN RIGHT SEMI     |              |     3 |   504 |   137K  (1)|
|*  6 |       TABLE ACCESS FULL       | MLOG$_I      |    33 |  4554 |     2   (0)|
|   7 |       TABLE ACCESS FULL       | I            |    20M|   577M|   136K  (1)|
|*  8 |     INDEX UNIQUE SCAN         | SYS_C0016059 |     1 |       |     0   (0)|
|   9 |    TABLE ACCESS BY INDEX ROWID| M            |     1 |    26 |     1   (0)|
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(ROWID=CHARTOROWID("MAS$"."M_ROW$$"))
   6 - filter("MAS$"."SNAPTIME$$">:B_ST1)
   8 - access("JV$"."MID"="MAS$0"."MID")

Note
-----
   - dynamic sampling used for this statement (level=2)

39 ligne(s) sÚlectionnÚe(s).

Hi Tom,

We need to move 2 production schemas from Oracle 9i single instance to Oracle 10G RAC.

At this stage we are thinking of migration strategy for these 2 schemas. Size of these schemas is about 120 GB in total (about 320 tables). We have business constraint to minimize the down time to transfer the schemas from Oracle 9i to Oracle 10G RAC. We considered EXP-IMP but it is slow thus we were thinking about using MVIEW if possible. I would like to take your view on below migration strategy for moving the schemas from Oracle 9i to Oracle 10G RAC.
1) Perform export of 2 schemas in Oracle 9i database without rows. Just to get the structure i.e. tables, index, grants etc
2) Configure MVIEW snapshot for schema in Oracle 9i
3) Create Database link between Oracle 9i and Oracle 10G database
4) Import 2 schemas into Oracle 10G RAC to get the structure i.e. tables, index, grants etc
5) Create MVIEW for all tables in Oracle 10G RAC with REFRESH FAST on PREBUILT TABLE.
6) refresh the MVIEWS in Oracle 10G RAC environment to get data from Oracle 9i database.
7) Organize outage for Oracle 9i database
8) Refresh MVIEWS for the final time
9) Cutover the application to Oracle 10G..

Do you think it is possible to achieve above steps? If Yes, I think it will considerably cut off downtime for the apps. I would also like to know if it is possible to configure MVIEW with fast refresh option on schema rather than per table basis.

Thanks in advance.

Cheers,

Thank you very much Tom.

Cheers,

Hi Tom,
Two queries about Materialized View Refresh
1. I have read in a few documents which mentions that Materialized View Refresh will only run in parallel if it has been created with parallel option. Is it true ?
2. What is the best way to find if Materialized View Refresh is running in parallel?

Thanks in advance

Tom,

How/Where to determine that how long materialized view took to refresh.
i.e. At time T1 materialized view refresh started.
At time T2 materialized view refresh completed.
We would like measure T2-T1.

Oracle Version: 9.2.0.8

Regards
Suresh.R

declare
   l_rowid rowid;
begin
   insert into log_table (name,start_date) values ('MYMVIEW', sysdate )
   returning rowid into l_rowid;
   dbms_mview.refresh( 'MYMVIEW' );
   update log_table set end_time = sysdate where rowid = l_rowid;
end;

Tom,
suppose the MV refresh job is schedule using dbms_job ( 9i) in production database and we dont want to schedule it on our own way then.

can we make use of dba_jobs_running to measure t2 - t1 ( i.e. MV refresh time taken) ?

Kindly suggest how to in this case?

regards

Thanks Tom,

Further,

select FULLREFRESHTIM from SYS.sum$

would give us fullrefresh time for that CONTAINERNAM (MYMVIEW)?

Hello Tom,

I am experimenting with some huge MVs and hit that problem where the fast refresh takes longer than the complete. I only have a few hundred records in my MV logs, so I followed your instructions and traced the session. Look at something weird I found: If you look at the "AND NOT EXISTS (SELECT 1" part, you will see that Oracle is considering the possibility of the ROWIDs of my regular tables to be NULL ! I was hoping the optimizer would ignore this, but if I remove the ORs, my cost goes from 5x10^9 to 5x10^5. Is this strange or what ?


INSERT INTO "KIPREV"."MV_CERTIFICADOS_PF"
SELECT /*+ NO_MERGE("JV$") */
"MAS$31"."COD_EMPRESA",
"MAS$31"."COD_CUENTA",
/*** Lots of columns here ***/
"MAS$2".ROWID,
"MAS$1".ROWID,
"MAS$0".ROWID
FROM (SELECT "MAS$"."ROWID" "RID$", "MAS$".*
FROM "KIPREV"."PERSONAS" "MAS$"
WHERE ROWID IN (SELECT /*+ HASH_SJ */
CHARTOROWID("MAS$"."M_ROW$$") RID$
FROM "KIPREV"."MLOG$_PERSONAS" "MAS$"
WHERE "MAS$".SNAPTIME$$ > :1)) "JV$",
"KIPREV"."FO_TIPSALDOS_X_CLASSIF" "MAS$0",
"KIPREV"."FO_TIPSALDOS_X_CONTA_DETA" "MAS$1",
"KIPREV"."FO_CONTRIB_X_PRODUTO" "MAS$2",
"KIPREV"."PERSONAS" "MAS$3",
"KIPREV"."FO_CONVENIO" "MAS$4",
"KIPREV"."PERSONAS" "MAS$5",
"KIPREV"."FV_OFICIALES_CARTERA" "MAS$6",
"KIPREV"."FO_CUENTAS_X_OFICIALES" "MAS$7",
"KIPREV"."PA_ENDERECOS" "MAS$8",
"KIPREV"."PA_ENDERECOS" "MAS$9",
"KIPREV"."OFICINAS_DE_ENTES" "MAS$10",
"KIPREV"."PERSONAS" "MAS$11",
"KIPREV"."ENTES_EXTERNOS" "MAS$12",
"KIPREV"."PERSONAS" "MAS$13",
"KIPREV"."FV_OFICIALES_CARTERA" "MAS$14",
"KIPREV"."PERSONAS" "MAS$15",
"KIPREV"."FV_OFICIALES_CARTERA" "MAS$16",
"KIPREV"."FV_OFICIALES_CARTERA" "MAS$18",
"KIPREV"."FO_DADOS_COMERCIAL" "MAS$19",
"KIPREV"."ID_PERSONAS" "MAS$20",
"KIPREV"."PERSONAS_FISICAS" "MAS$21",
"KIPREV"."PERSONAS" "MAS$22",
"KIPREV"."FO_PRODUCTOS" "MAS$23",
"KIPREV"."FO_CUENTAS" "MAS$24",
"KIPREV"."PA_STATUS" "MAS$25",
"KIPREV"."PA_STATUS_SUB" "MAS$26",
"KIPREV"."BE_TIPOS_COBERTURA" "MAS$27",
"KIPREV"."BE_BENEFICIOS" "MAS$28",
"KIPREV"."BENEFICIOS_X_CUENTA" "MAS$29",
"KIPREV"."FO_CONTRIBUICOES" "MAS$30",
"KIPREV"."FO_CONTRIB_X_CONTA" "MAS$31"
WHERE ("MAS$30"."COD_EMPRESA" = "MAS$31"."COD_EMPRESA" AND
"MAS$30"."COD_CONTRIB" = "MAS$31"."COD_CONTRIB" AND
"MAS$29"."COD_EMPRESA" = "MAS$31"."COD_EMPRESA" AND
"MAS$29"."COD_BENEFICIO" = "MAS$31"."COD_BENEFICIO" AND
"MAS$29"."COD_PLAN" = "MAS$31"."COD_PLAN" AND
"MAS$29"."COD_CUENTA" = "MAS$31"."COD_CUENTA" AND
"MAS$28"."COD_EMPRESA" = "MAS$29"."COD_EMPRESA" AND
"MAS$28"."COD_PLAN" = "MAS$29"."COD_PLAN" AND
"MAS$28"."COD_BENEFICIO" = "MAS$29"."COD_BENEFICIO" AND
"MAS$27"."COD_TIPCOBERTURA" = "MAS$28"."COD_TIPCOBERTURA" AND
"MAS$26"."TIP_STATUS" = "MAS$29"."TIP_STATUS" AND
"MAS$26"."COD_STATUS" = "MAS$29"."COD_ESTADO" AND
"MAS$26"."COD_SUB_STATUS" = "MAS$29"."COD_SUB_ESTADO" AND
"MAS$25"."TIP_STATUS" = "MAS$26"."TIP_STATUS" AND
"MAS$25"."COD_STATUS" = "MAS$26"."COD_STATUS" AND
"MAS$24"."COD_EMPRESA" = "MAS$29"."COD_EMPRESA" AND
"MAS$24"."COD_CUENTA" = "MAS$29"."COD_CUENTA" AND
"MAS$23"."COD_EMPRESA" = "MAS$24"."COD_EMPRESA" AND
"MAS$23"."COD_PRODUCTO" = "MAS$24"."COD_PRODUCTO" AND
"MAS$22"."COD_PERSONA" = "MAS$24"."COD_CLIENTE" AND
"MAS$21"."COD_PER_FISICA" = "MAS$22"."COD_PERSONA" AND
"MAS$20"."COD_PERSONA" = "MAS$22"."COD_PERSONA" AND
"MAS$20"."COD_TIPO_ID" = '1' AND
"MAS$19"."COD_EMPRESA" = "MAS$24"."COD_EMPRESA" AND
"MAS$19"."COD_CUENTA" = "MAS$24"."COD_CUENTA" AND
"MAS$18"."COD_EMPRESA" = "MAS$19"."COD_EMPRESA" AND
"MAS$18"."COD_OFICIAL" = "MAS$19"."COD_AGENCIADOR" AND
"JV$"."COD_PERSONA"(+) = "MAS$18"."COD_PERSONA" AND
"MAS$16"."COD_EMPRESA"(+) = "MAS$19"."COD_EMPRESA" AND
"MAS$16"."COD_OFICIAL"(+) = "MAS$19"."COD_AGENTE_VENDA" AND
"MAS$15"."COD_PERSONA"(+) = "MAS$16"."COD_PERSONA" AND
"MAS$14"."COD_EMPRESA"(+) = "MAS$19"."COD_EMPRESA" AND
"MAS$14"."COD_OFICIAL"(+) = "MAS$19"."COD_PROLABORISTA" AND
"MAS$13"."COD_PERSONA"(+) = "MAS$14"."COD_PERSONA" AND
"MAS$12"."COD_ENTE" = "MAS$19"."COD_ENTE" AND
"MAS$11"."COD_PERSONA" = "MAS$12"."COD_PERSONA" AND
"MAS$10"."COD_ENTE" = "MAS$19"."COD_ENTE" AND
"MAS$10"."COD_OFICINA" = "MAS$19"."COD_OFICINA" AND
"MAS$9"."COD_ENDERECO"(+) = "MAS$10"."COD_MUNICIPIO" AND
"MAS$9"."COD_NIVEL_ANTERIOR" = "MAS$8"."COD_NIVEL"(+) AND
"MAS$9"."NIVEL_ANTERIOR" = "MAS$8"."COD_ENDERECO"(+) AND
"MAS$9"."COD_NIVEL"(+) = 'CI' AND "MAS$8"."COD_NIVEL"(+) = 'UF' AND
"MAS$7"."COD_EMPRESA"(+) = "MAS$24"."COD_EMPRESA" AND
"MAS$7"."COD_CUENTA"(+) = "MAS$24"."COD_CUENTA" AND
"MAS$6"."COD_EMPRESA"(+) = "MAS$19"."COD_EMPRESA" AND
"MAS$6"."COD_OFICIAL"(+) = "MAS$19"."COD_AGENTE_VENDA" AND
"MAS$6"."COD_PERSONA" = "MAS$5"."COD_PERSONA" AND
"MAS$4"."ID_CONVENIO"(+) = "MAS$24"."ID_CONVENIO" AND
"MAS$3"."COD_PERSONA"(+) = "MAS$24"."COD_PERSONA_RESPFIN" AND
"MAS$2"."COD_EMPRESA" = "MAS$31"."COD_EMPRESA" AND
"MAS$2"."COD_PRODUTO" = "MAS$31"."COD_PRODUTO" AND
"MAS$2"."COD_CONTRIB" = "MAS$31"."COD_CONTRIB" AND
"MAS$1"."COD_EMPRESA" = "MAS$31"."COD_EMPRESA" AND
"MAS$1"."COD_CONTA" = "MAS$31"."COD_CUENTA" AND
"MAS$1"."COD_PLAN" = "MAS$31"."COD_PLAN" AND
"MAS$1"."COD_BENEFICIO" = "MAS$31"."COD_BENEFICIO" AND
"MAS$0"."COD_EMPRESA" = "MAS$1"."COD_EMPRESA" AND
"MAS$0"."COD_TIPSALDO" = "MAS$1"."COD_TIPSALDO" AND
("MAS$0"."COD_CLASSIFICACAO" = 'BAC' OR
"MAS$0"."COD_CLASSIFICACAO" = 'RIS') AND
"MAS$24"."TIP_CUENTA" = 'I')

/*** Below is the weird part: MAS$<N> are my regular tables ***/

AND NOT EXISTS (SELECT 1
FROM "KIPREV"."MV_CERTIFICADOS_PF" "SNA2$"
WHERE ("SNA2$"."FCC_ROWID" = "MAS$31".ROWID OR
"MAS$31".ROWID IS NULL)
AND ("SNA2$"."FCNT_ROWID" = "MAS$30".ROWID OR
"MAS$30".ROWID IS NULL)
AND ("SNA2$"."BC_ROWID" = "MAS$29".ROWID OR
"MAS$29".ROWID IS NULL)
AND ("SNA2$"."BB_ROWID" = "MAS$28".ROWID OR
"MAS$28".ROWID IS NULL)
AND ("SNA2$"."BTC_ROWID" = "MAS$27".ROWID OR
"MAS$27".ROWID IS NULL)
AND ("SNA2$"."PSS_ROWID" = "MAS$26".ROWID OR
"MAS$26".ROWID IS NULL)
AND ("SNA2$"."PS_ROWID" = "MAS$25".ROWID OR
"MAS$25".ROWID IS NULL)
AND ("SNA2$"."FC_ROWID" = "MAS$24".ROWID OR
"MAS$24".ROWID IS NULL)
AND ("SNA2$"."P_ROWID" = "MAS$23".ROWID OR
"MAS$23".ROWID IS NULL)
AND ("SNA2$"."PERCLI_ROWID" = "MAS$22".ROWID OR
"MAS$22".ROWID IS NULL)
AND ("SNA2$"."PPPFJ_ROWID" = "MAS$21".ROWID OR
"MAS$21".ROWID IS NULL)
AND ("SNA2$"."IPPFJ_ROWID" = "MAS$20".ROWID OR
"MAS$20".ROWID IS NULL)
AND ("SNA2$"."FDC_ROWID" = "MAS$19".ROWID OR
"MAS$19".ROWID IS NULL)
AND ("SNA2$"."OCAG_ROWID" = "MAS$18".ROWID)
AND ("SNA2$"."OCAV_ROWID" = "MAS$16".ROWID OR
"MAS$16".ROWID IS NULL)
AND ("SNA2$"."PERAV_ROWID" = "MAS$15".ROWID OR
"MAS$15".ROWID IS NULL)
AND ("SNA2$"."OCPL_ROWID" = "MAS$14".ROWID OR
"MAS$14".ROWID IS NULL)
AND ("SNA2$"."PERPL_ROWID" = "MAS$13".ROWID OR
"MAS$13".ROWID IS NULL)
AND ("SNA2$"."EE_ROWID" = "MAS$12".ROWID OR
"MAS$12".ROWID IS NULL)
AND ("SNA2$"."PEREE_ROWID" = "MAS$11".ROWID OR
"MAS$11".ROWID IS NULL)
AND ("SNA2$"."OE_ROWID" = "MAS$10".ROWID OR
"MAS$10".ROWID IS NULL)
AND ("SNA2$"."CIDADE_ROWID" = "MAS$9".ROWID OR
"MAS$9".ROWID IS NULL)
AND ("SNA2$"."ESTADO_ROWID" = "MAS$8".ROWID OR
"MAS$8".ROWID IS NULL)
AND ("SNA2$"."COF_ROWID" = "MAS$7".ROWID OR
"MAS$7".ROWID IS NULL)
AND ("SNA2$"."OCCOR_ROWID" = "MAS$6".ROWID OR
"MAS$6".ROWID IS NULL)
AND ("SNA2$"."PERCOR_ROWID" = "MAS$5".ROWID OR
"MAS$5".ROWID IS NULL)
AND ("SNA2$"."FCON_ROWID" = "MAS$4".ROWID OR
"MAS$4".ROWID IS NULL)
AND ("SNA2$"."PEREF_ROWID" = "MAS$3".ROWID OR
"MAS$3".ROWID IS NULL)
AND ("SNA2$"."FCPP_ROWID" = "MAS$2".ROWID OR
"MAS$2".ROWID IS NULL)
AND ("SNA2$"."TIP_SAL_DT_ROWID" =
"MAS$1".ROWID OR "MAS$1".ROWID IS NULL)
AND ("SNA2$"."TSC_ROWID" = "MAS$0".ROWID OR
"MAS$0".ROWID IS NULL)
AND "JV$".RID$ IS NULL)
AND NOT EXISTS
(SELECT 1
FROM "KIPREV"."PERSONAS" "MAS_INNER$",
"KIPREV"."FV_OFICIALES_CARTERA" "MAS_OUTER$"
WHERE "MAS$18".ROWID = "MAS_OUTER$".ROWID
AND "JV$".RID$ IS NULL
AND "MAS_INNER$"."COD_PERSONA" = "MAS_OUTER$"."COD_PERSONA")

It is just that whenever I read about the fast/incremental feature for MVs I thought of things beautiful and elegant such as the redo/undo mechanism happening underneath. After carefully building MV logs for 78 tables, choosing carefully the columns, then creating a series of MVs, struggling to keep them "fast-refresh-capable", seeing this DML command is just... sad.

Hi Tom,

Referring to Metalink ID 763823.1, I have exported/imported mviews to a test, virtual environment of 2 databases to test the base table & mview exports. After import I ran complete refreshes on the small tables with no problem. However, when I tried one of the large tables (50M records), it took 175 hours to complete! Checking it with v$active_session_history, I see huge amounts of wait_class -> user i/o and event -> db file sequential read on the insert statement. Indexes are in place on the mview, though the mview is partitioned the indexes are not partitioned.

The note says to build the mview log on the master table after the import and do a complete refresh. Why would the log be needed for a complete refresh? Would it improve the speed?

Also, why would a complete refresh do so much reading? It is a single transaction, so it would load it to memory then commit to disk at the end, wouldn't it? The disk reading would be on the master table side?

Could you please enlighten me?

Thanks for your help,
Jane

Hi Tom,
I have created a mview MV_A on a Master Table A, using the query:

CREATE MATERIALIZED VIEW MV_A
REFRESH FAST
AS SELECT * FROM A

I then created the Mview log on the master table A as:
CREATE MATERIALIZED VIEW LOG ON A;

My master table A is created as PARALLEL DEFAULT.

I then started making DML changes (INSERT) to the Master table A. I noticed that when I INSERT into the table A in the below fashion:
INSERT INTO A (SELECT * FROM B);
The MLOG$_A does not updated capture the changed records.

But an insert into master table in the below fashion is captured by the MLOG$_A.
INSERT INTO A (COL1) VALUES (2);

But when the master table is altered to NOPARALLEL, Inserts in both the fashions, are captured in the mview log.

Is this expected behaviour?

Thanks in advance

Hi Tom,

I have now moved to a mview of a larger size - 151M records with the same wait_class and event as my previous post above.

With these test mviews, I had used DataPump to export/import the metadata of the mview. The import log was: Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW. No mention of statistics.
The structure was there so I ran the complete refresh. The last_analyzed date was the date of import of an empty table not knowing it is a 'large' table. I see that I will need to investigate how to exp/imp the statistics along with metadata.

The execution plan is:
select sql_id, sql_plan_hash_value plan_hash, plan_line_id id, lpad (' ', plan_depth) || plan_operation operation , plan_options , plan_object_name , plan_cardinality card, plan_cost
from v$sql_plan_monitor
where status = 'EXECUTING'
order by key, id;


SQL_ID PLAN_HASH ID OPERATION PLAN_OPTIONS
PLAN_OBJECT_NAME CARD PLAN_COST
------------- ---------- ---- -------------------------------- -----------------
--- ------------------------ ---------- ----------
3c4dk31a1hygz 1788691278 0 INSERT STATEMENT
1 LOAD TABLE CONVENTIONAL
2 REMOTE
TABLE_A 1 2

The refresh query is select * from table_name. The only index on the mview is a 8-column primary key.

So, it might just be the lack of statistics causing this performance problem?

Thanks for your help,
Jane

At the top of this thread back in 2004, you stated "If this table is part of a refresh group, it'll be done with a DELETE (redo) plus insert (redo)." as opposed to truncate plus insert. Is this still true in 10g and 11g? I've searched the Oracle documentation and cannot find it.

I have a question related to incremental refresh of MVs, do we need to rebuild indexes after incremental refresh of MVs, please assume that we didn't disable the indexes before the refresh.

Hi Tom,

We are refreshing MV daily using complete refresh,
it use to take 3 hrs to complete. now all of sudden it is taking 5 hrs, what could be the reason,
we have done no changes.

Please let me know how ro diagnise the issue.

Regards
KNR

hi,
i have one requirement to use utl_http.By using this package i need to send one request to webserver with required parameters based on that request that webserver gives response.i need to get that response and populate those response details in my database table.so for this one please give one example.it helps a lot to me

thanks in advance
thanks & regards,
k.v.koteswara rao.

Tom:

Can you please help me why I am getting this error. While using dbms_mview.refresh_dependent I am getting this error message. But while using dbms_mview.refresh i am NOT getting this error message.

Below is the entire script for your review.

rajesh@ORA11GR2> create table t1
  2  nologging
  3  as
  4  select level as empno,
  5        dbms_random.string('A',30) as ename,
  6        sysdate as hire_date,
  7        mod(level,100) + 1 as deptno,
  8        mod(level,1000) as comm,
  9        dbms_random.value as salary
 10  from dual
 11  connect by level <=100000;

Table created.

Elapsed: 00:00:06.24
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t2
  2  nologging
  3  as
  4  select level as deptno,
  5        dbms_random.string('A',20) as dname,
  6        dbms_random.string('B',10) as loc
  7  from dual
  8  connect by level <=100;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.03
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA11GR2> alter table t1 add constraint t1_pk primary key(empno) ;

Table altered.

Elapsed: 00:00:00.14
rajesh@ORA11GR2> alter table t2 add constraint t2_pk primary key(deptno) ;

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> alter table t1 add constraint t1_fk foreign key(deptno) references t2 ;

Table altered.

Elapsed: 00:00:00.06
rajesh@ORA11GR2> alter table t1 modify deptno not null  ;

Table altered.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> create materialized view t1_mv
  2  build deferred
  3  refresh on commit
  4  enable query rewrite
  5  as
  6  select deptno,count(*) as cnt
  7  from t1
  8  group by deptno;

Materialized view created.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2> create materialized view t1_t2_mv
  2  build deferred
  3  refresh on commit
  4  enable query rewrite
  5  as
  6  select dname, t.deptno,count(t.cnt)
  7  from t2, t1_mv t
  8  where t2.deptno = t.deptno
  9  group by dname,t.deptno;

Materialized view created.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_mview.refresh_dependent(
  3     number_of_failures=>:x,
  4     list=>'T1_T2_MV',
  5     method=>null,
  6     rollback_seg=>null,
  7     refresh_after_errors=>false,
  8     atomic_refresh=>true,
  9     nested=>true);
 10  end;
 11  /
begin
*
ERROR at line 1:
ORA-23401: materialized view "RAJESH"."T1_T2_MV" does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2702
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3220
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3200
ORA-06512: at line 2


Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> select mview_name, last_refresh_date
  2  from user_mviews
  3  where mview_name = 'T1_T2_MV'
  4  /

MVIEW_NAME                     LAST_REFR
------------------------------ ---------
T1_T2_MV

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_mview.refresh(list=>'T1_T2_MV',nested=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.37
rajesh@ORA11GR2>

ops$tkyte%ORA11GR2> variable x number
ops$tkyte%ORA11GR2> l
  1  begin
  2     dbms_mview.refresh_dependent(
  3     number_of_failures=>:x,
  4     list=>'T1_MV',
  5     method=>null,
  6     rollback_seg=>null,
  7     refresh_after_errors=>false,
  8     atomic_refresh=>true,
  9     nested=>true);
 10* end;
ops$tkyte%ORA11GR2> /

PL/SQL procedure successfully completed.