Would have never thought of using CAST

Tom:

How would you modify your create statement to make the mv a snapshot of the original table. That is, if user deletes a record in emp, it will be deleted from emp_mv and it he inserts one into emp it will be inserted into emp_mv and if he updates a record ino emp it will be updated in emp_mv.

Works like a charm. Thanks a million.

Tom:

I tried it just as you have it but it did not update the view after I update emp_test table.

When I do this in an 8i database:


SQL>create materialized view emp_test_mv
    refresh complete on commit
    as
    select  * from emp_test;
select  * from emp_test
               *
ERROR at line 4:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

I thought maybe you need to create a log to do this so I did:


     create materialized view log
    on emp_test
    with rowid,primary key(empno)
    including new values
SQL> /
 create materialized view log
*
ERROR at line 1:
ORA-12026: invalid filter column detected 

Hallo Tom,

I didn't think of the CASE-Operator when I read the question,
so I would have done it like this

sql > create materialized view mv
2 as
3 select empno, ename, nullif( to_date(1, 'J'), to_date(1, 'J')) dt
4 from emp;

Materialized View wurde erstellt.

, since

sql > create materialized view mv
2 as
3 select empno, ename, to_date( null ) dt
4 from emp;
from emp
*
FEHLER in Zeile 4:
ORA-01723: zero-length columns are not allowed

.

Also,

sql > create materialized view mv
2 as
3 select empno, ename, decode(1, 0, sysdate, null) dt
4 from emp;

Materialized View wurde erstellt.

works as well.

What is the difference between
to_date ( null )
vs.
decode(1, 0, sysdate, null)
nullif( to_date(1, 'J'), to_date(1, 'J'))
cast (null as date) ?

(They look the same to me:
sql > set null nada
sql > select to_date( null ) from dual;

TO_DATE(NULL)
-------------------
nada

sql > select nullif( to_date(1, 'J'), to_date(1, 'J')) from dual;

NULLIF(TO_DATE(1,'J
-------------------
nada

sql > select decode(1, 0, sysdate, null) from dual;

DECODE(1,0,SYSDATE,
-------------------
nada

sql > select cast (null as date) from dual;

CAST(NULLASDATE)
-------------------
nada
)

All 4 of them obviously are NULLs of type DATE
?

Pardon my lack of imagination, but I don't understand why you'd create an MV with a column that was always null?

Are they expecting to point standard, unmodified, code at this new table (MV) via a synonym or something, and that code expects a date column?

I guess my Oracle experience is too limited so far, only 4 years on a single application. :-/

Tom,
iam trying to create snapshot on one of my database.

1) I created snapshot log on source DB.
2) I tried to create snapshot on target. Iam getting ORA-01723: zero-length columns are not allowed.

SQL> desc tb_project@remotedb
 Name                            Null?    Type
 ------------------------------- -------- ----
 PMTSBU                          NOT NULL VARCHAR2(8)
 PORTFOLIOID                     NOT NULL NUMBER(15)
 PORTFOLIONAME                   NOT NULL VARCHAR2(50)
 PROGRAMID                       NOT NULL NUMBER(15)
 PROGRAMNAME                     NOT NULL VARCHAR2(50)
 PROJECTID                                NUMBER(15)
 PROJECTNBR                               VARCHAR2(25)
 PROJECTNAME                              VARCHAR2(80)
 CREATE_TIMESTAMP                NOT NULL DATE


SQL> CREATE SNAPSHOT sn_tb_project
REFRESH   FAST
START WITH SYSDATE
NEXT  trunc(sysdate)+1+(1/48) 
with ROWID
AS
select pmtsbu, to_char(portfolioid), portfolioname, to_char(programid), programname,
to_char(projectid), projectnbr, projectname, 'PMT' as projecttype, 'Dead' as projectstatus,
NULL as projectdescription, create_timestamp FROM tb_project@remotedb
/

NULL as projectdescription, decode (1,1,create_timestamp) create_timestamp   FROM tb_project@remotedb
*
ERROR at line 9:
ORA-01723: zero-length columns are not allowed

can you explain why this happens tom ?
Thanks
bobby
 

NULL as projectdescription

^^^^^

that is the cause, why do you have that?

it might work with cast( null as varchar2(10) ) or whatever.

it is basically saying "what the heck is that?  what datatype is that?  no clue here"


 
ops$tkyte@ORA9IR2> @afiedt.buf
select empno, null x from emp
                          *
ERROR at line 4:
ORA-01723: zero-length columns are not allowed
 
 
ops$tkyte@ORA9IR2> edit
Wrote file afiedt.buf
 
  1  create materialized view mv
  2  refresh complete
  3  as
  4* select empno, cast( null as varchar2(21)) x from emp
ops$tkyte@ORA9IR2> /
 
Materialized view created.


 

Hi Tom,
I created a MV with some functions as below. When I describe MV, the length of columns is not available. Is it possible to make this available? Also, Is it possible to make the columns shown in MV as - NOT NULL.

create table test10 (client number(10) not null primary key,sale_date date not null, sale_amount number(15,2));

create materialized view test10_mv as
 select to_number(to_char(sale_date,'YYYYMM')) month_key, sum(sale_amount) tot_amnt
 from test10
 group by to_number(to_char(sale_date,'YYYYMM'));
SQL> desc test10_mv;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MONTH_KEY                                          NUMBER
 TOT_AMNT                                           NUMBER 

why would you do that to a perfectly good date?  just trunc it to the MONTH!

but none of those numbers "have a length", they are just numbers after all.

ops$tkyte@ORA9IR2> create materialized view mv
  2  as
  3  select to_number(to_char(hiredate,'yyyymm')) month_key, sum(sal) tot_amt
  4    from emp
  5   group by to_number(to_char(hiredate,'yyyymm'))
  6  /
 
Materialized view created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> desc mv
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 MONTH_KEY                                         NUMBER
 TOT_AMT                                           NUMBER
 
<b>that is what you have, you can use a prebuilt table:</b>

ops$tkyte@ORA9IR2> drop materialized view mv;
 
Materialized view dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table mv
  2  ( month_key number(6) not null,
  3    tot_amt   number(10,2) not null
  4  )
  5  /
 
Table created.
 
ops$tkyte@ORA9IR2> create materialized view mv<b>
  2  on prebuilt table
  3  with reduced precision</b>
  4  as
  5  select to_number(to_char(hiredate,'yyyymm')) month_key, sum(sal) tot_amt
  6    from emp
  7   group by to_number(to_char(hiredate,'yyyymm'))
  8  /
 
Materialized view created.
 
ops$tkyte@ORA9IR2> desc mv
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 MONTH_KEY                                NOT NULL NUMBER(6)
 TOT_AMT                                  NOT NULL NUMBER(10,2)
 
ops$tkyte@ORA9IR2> exec dbms_mview.refresh('MV');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from mv;
 
 MONTH_KEY    TOT_AMT
---------- ----------
    198012        800
    198102       2850
    198104       2975
    198105       2850
    198106       2450
    198109       2750
    198111       5000
    198112       3950
    198201       1300
    198212       3000
    198301       1100
 
11 rows selected.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop materialized view mv;
 
Materialized view dropped.
 
ops$tkyte@ORA9IR2> drop table mv;
 
Table dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view mv
  2  as
  3  select trunc(hiredate,'mm') month_key, sum(sal) tot_amt
  4    from emp
  5   group by trunc(hiredate,'mm')
  6  /
 
Materialized view created.

<b>and that is my recommendation for the date, KEEP IT as a date</b>

 

Hi Tom,
Could you please mention, how
"on prebuilt table
with reduced precision"
will work if the underlying table is partitioned? Please note that underlying table is partitioned on YYYYMM key (month_key).

Thanks & Regards

Is it possible to have materialized views describe receiving column datatype and lengths without basing it off of a Table ?I saw the syntax diagram of the oracles "create mat view" stmt and it seem to support this.
Eg.

CREATE MATERIALIZED VIEW B1
TABLESPACE TBL1
COLUMN ENTITY_ID IS OF TYPE ( varchar2(50) )-- Can we specify the length here so as to avoid substr down in select ???
NOCACHE
LOGGING
NOPARALLEL
REFRESH COMPLETE
START WITH sysdate
NEXT sysdate + 1
AS
SELECT
EN.ENTITY_ID
FROM
entity_tbl EN ;


IF such an option is possible pls can you specify the syntax ?

what would be the use of that?

Now As below if you have a select in a mat view which has a function call ( and u cannot change the function bcos of 3rd party), then how do you restrict the column length of the receiving field if u don't want to use substr ?
Are Mat Views able to handle that or explicit funcs like substr is the only solution?
C and D defaults to varchar2(4000) which u don't want and only want 2 digit length.
Ex

create mat view MVW1
As
.
.
.
select
A,
B,
f1(...) C,
f2(...) D
from t1
where blah blah blah ....

ok for functions, but then -- what is wrong with substr?  you'd have to put it in one place or the other -- substr works fine.  But you can also use CAST()

ops$tkyte@ORA9IR2> create or replace function f( x in number ) return varchar2
  2  as
  3  begin
  4          return 'x';
  5  end;
  6  /
Function created.
 
ops$tkyte@ORA9IR2> create table t ( x int primary key, y int );
Table created.
 
ops$tkyte@ORA9IR2> create materialized view mv
  2  as
  3  select x, y, cast( f(y) as varchar2(30)) f_y
  4    from t;
Materialized view created.
 
ops$tkyte@ORA9IR2> desc mv
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                        NOT NULL NUMBER(38)
 Y                                                 NUMBER(38)
 F_Y                                               VARCHAR2(30)


(but careful if it returns more than 30, the view will stop refreshing.... ) 

Hi Tom,
I have a couple of clarifications with "Create materialized view " in Oracle 8.1.7/9i (we have both environments as of now).
I did try to research and get more info in Oracle documentation and on internet forums but not with much success.
Scenario: Using Mviews for pure data replication in remote instance

Create Materialized View (FAST Refresh):
a) We wanted to record the insert/update datetime stamp (additional column "ODS_DATETIME") in the MView. On trying to use "sysdate as ODS_DATETIME" in the "cREATE mATERIALIZED vIEW" statement, the following error comes up:
ORA-12015: cannot create a fast refresh materialized view from a complex query.

The documentation says that non-repeating columns like "sysdate" and "rownum" are not allowed.
As we wanted to avoid a trigger on the Table for just putting the "sysdate" value for "ODS_DATETIME" column, we tried the following in place of "sysdate" and it works:
TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY HH MI SS'),'MM/DD/YYYY HH MI SS') as ODS_DATETIME

Create statement(sample)
========================
create materialized view rawatg.gg_try_mv
REFRESH FAST
ON DEMAND
with primary key
as select a.*
,sysdate as ODS_DATETIME
--, TO_DATE(TO_CHAR(sysdate, 'MM/DD/YYYY HH MI SS'),'MM/DD/YYYY HH MI SS') as ODS_DATETIME
from rawatg.gg_try a

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

I am not sure why this works but it works.
The non-repeating column "sysdate" is still part of the create statement!!!
Further, the "ODS_DATETIME" column gets instantiated with the start datetime of refresh and hence only one specific datetime is populated as the value for ODS_DATETIME for the entire MView refresh which may span a couple of minutes.
This is OK with us.
I guess by using to_char and to_date functions on "sysdate", it makes it a repeating/static value for the whole refresh.
Please provide your observations on these.

For Materialized View Logs (using rowid or primary key):
b1) I understand that "INCLUDING NEW VALUES" clause is only good/useful if you are doing aggregations. One does not need it for a pure data/table replication ?? Please comment.
I was kind of interested to know where the LOG stores the old values when using "INCLUDING NEW VALUES" clause (The log table has a "RAW Datatype" column "change_vectors$$"; does it store the "old values" in some other data dictionary table and what are the overheads of storing the old values also ??

b2) My understanding is that with the changing nature of "rowids" (table REORG, Transportable tablespaces), the "with rowid clause" is primarily there for "backward compatibilty and for scenarios when tables do not have primary keys for whatever reasons. For tables having primary keys, it is desirable to have the logs and Mviews created using "with primary clause" so that they would always be eligible for fast refresh. Please comment on these.

b3) When do you need to use "with sequence(...)" clause. The documentation says "Sequence numbers are necessary to support fast refresh after some update scenarios". I was not able to find more details on this.

b4) When do you need to use multiple of these like "with rowid, primary key, sequence(...)" ???

Thanks a lot in adavance for your valued inputs.
Gagneesh

Hi Tom,
Thanks. We would be re-visiting the entire approach and the necessity of the ODS_DATETIME.
Could you please provide inputs on the Materialized View Logs part (b1, b2, b3)......?
Further, when you say ODS will have to record the timestamp, I guess you are referring to putting a trigger on the MView Table to record the ODS_DATETIME. Please let me know.

Thanks
Gagneesh

I came across a situation where the Mat view is

a) Taking about 2 hrs to Create.
b) Not returning any rows after creation but if you take out the query inside it returns Rows.The Select clause contains 7 occurences of two overloaded functions which populate data into objects types.

//func1 returns object of type OBJ_TYPE1

//func2 also return object of similar type with different no of members

OBJ_TYPE1
(
MEMBER1 is Varchar,
MEMBER2 is Date,
MEMBER3 is Varchar,
MEMBER4 is Date
)
SELECT
F1,
F2,
F3,
func1(funcX(PARM1,PARM2)).MEMBER1 AS F4,
substr(func1(funcX(PARM1,PARM2)).MEMBER2,1,12) AS F5,
func2(funcX(PARM1,PARM2)).MEMBER1 AS F6,
...
...
...
FROM
<Where Clause>

I just wanted to check with you what might be happening for such a discrepancy to occur( data while running select outside but not thru mat view ) and also why it takes so much time to create a mat view ?

Thx
Anil

I came across a situation where the Mat view is

a) Taking about 2 hrs to Create.
b) Not returning any rows after creation but if you take out the query inside it returns Rows.The Select clause contains 7 occurences of two overloaded functions which populate data into objects types.

//func1 returns object of type OBJ_TYPE1

//func2 also return object of similar type with different no of members

OBJ_TYPE1
(
MEMBER1 is Varchar,
MEMBER2 is Varchar,
MEMBER3 is Varchar,
MEMBER4 is Date
)
SELECT
F1,
F2,
F3,
func1(funcX(PARM1,PARM2)).MEMBER1 AS F4,
substr(func1(funcX(PARM1,PARM2)).MEMBER2,1,12) AS F5,
func2(funcX(PARM1,PARM2)).MEMBER1 AS F6,
...
...
...
FROM
<Where Clause>

I just wanted to check with you what might be happening for such a discrepancy to occur( data while running select outside but not thru mat view ) and also why it takes so much time to create a mat view ?

Thx
Anil

Dear Tom,
We have Oracle 9.2.0.1 version.
I do not think so but still wanted to verify if there is any overhead/cost/difference associated on future refreshes/queries/query_rewrite against a Materialized View on prebuilt tables than one created directly.
The Materialized View on pre-built table maybe required due to:
a) one needs precision for the column(Reference to earlier response:Column size for MVs) or
b) one has pre-existing table with data or
c) due to any other reasons

Based on my experience, the differences (which are logical and clear) are:
d) The pre-built table MView can be dropped without dropping the table while in the other case the table also gets dropped.
e) You do not specify the tablespace and the build clause with the prebuilt table MView but may specify "with or without reduced precision" clause...

Please provide your inputs.
Thanks a lot...

Hi Tom,
Thanks a lot...

Wanted to add, that the fast refreshable MViews created using "on prebuilt table" cannot use rowid i.e. "with rowid" clause for fast refresh. They need to have the Primary Key.

Hi Tom ,
When I am creating a materialized view by fetching the data again from materialized views I am getting error "ora-01723 :-zero length columns are not allowed"
at the line of from clause where i am using the name of views. e.g
1) A MV is first MV (complex view)
2) B MV is 2nd MV (complex view)

3) C MV is using both A and B Materialized Views having one join and three sub queries.
e.g.
1 create materialized view C
2 as
3 select A.X.....,
4 B.Y......
5 aa (subquery)
6 from A,B,(subquery)
7 where a.id=b.id;
/
error at line 6: and indicates MV A
ora-01723 : zero-length columns are not allowed.

---in both the MV's there is no null values.

I have two specific errors 
One is : I am not able to use Replace key word 
Second: I am not able to specify column names as I would normally do for a view.
Details are as below.
error I

  1  CREATE OR REPLACE MATERIALIZED VIEW MV_REV
  2  (D_CODE, S_CODE, TRAN_CODE, NO_DOCTS, SUM_SD_BY_DOCT)
  3   
  4  AS
  5  select ..... from t_tble
  9* group by cube (floor(s_code/100),s_code,t_code);
SQL> /
CREATE OR REPLACE MATERIALIZED VIEW SROUSER.MV_REV
                  *
ERROR at line 1:
ORA-00922: missing or invalid option 


error II 

1  CREATE MATERIALIZED VIEW MV_REV
  2  (D_CODE, S_CODE, TRAN_CODE, NO_DOCTS, SUM_SD_BY_DOCT)
  3   
  4  AS
  5  select ..... from t_tble
  9* group by cube (floor(s_code/100),s_code,t_code);
SQL> /
(D_CODE, S_CODE, TRAN_CODE, NO_DOCTS, SUM_SD_BY_DOCT)
 *
ERROR at line 2:
ORA-00905: missing keyword 

Thanks in advance,
Regards,

 

Not to debate, But I did see in internet search about replace key word. The following was the query.

Create or replace materialized view mv_parts
As
Select * from parts@parts_db;

which I find not much different from my query except for the remote db part.
Anyway, Many thanks for answering/solving my problems

Regards,

If you saw it on the internet -- it must be true :)

Look for examples that look like this:


ops$tkyte@ORA9IR2> create materialized view mv
  2  as
  3  select empno, ename, cast( null as date ) dt
  4    from emp
  5  /
 
Materialized view created.
 

Sure, the ops$tkyte@ORA9IR2> and 2, 3, 4, ... get in the way of a cut and paste, but at least you are relatively sure the author has actually *run the code* 

Hello,

Please help.

We had a look at your explanation:(above)
Using Column in Materialized Views December 16, 2004
Reviewer: Anil Bishnoie from NYC,NY

but in our case, we want to get the number field as "NUMBER(5,2)" instead of just "NUMBER".
================
yd@MYDB.MU> create table t1 (f1 number(5,2), f2 varchar2(2));

Table created.

Elapsed: 00:00:00.13
yd@MYDB.MU> desc t1
Name Null? Type
------------------------------- -------- ----
F1 NUMBER(5,2)
F2 VARCHAR2(2)

yd@MYDB.MU> create view v1 as select * from t1;

View created.

Elapsed: 00:00:00.02
yd@MYDB.MU> desc v1
Name Null? Type
------------------------------- -------- ----
F1 NUMBER(5,2)
F2 VARCHAR2(2)

yd@MYDB.MU> create or replace view v2 as select sum(f1) f1, f2 from t1 group by f2;

View created.

Elapsed: 00:00:00.09
yd@MYDB.MU> desc v2
Name Null? Type
------------------------------- -------- ----
F1 NUMBER <==should report us NUMBER(5,2)
F2 VARCHAR2(2)

We have also tried the following:
==================================
yd@MYDB.MU> create or replace view mv1 as select cast(sum(f1) as number(5,2)) f1, f2 from t1 group by f2;

View created.

Elapsed: 00:00:00.04
yd@MYDB.MU> desc mv1
Name Null? Type
------------------------------- -------- ----
F1 NUMBER
F2 VARCHAR2(2)

yd@MYDB.MU>


and:
====
yd@MYDB.MU> create or replace view v2 as select cast(sum(f1) as number(5,2)) f1, f2 from t1 group by f2;

View created.

Elapsed: 00:00:00.06
yd@MYDB.MU> desc v2
Name Null? Type
------------------------------- -------- ----
F1 NUMBER
F2 VARCHAR2(2)

yd@MYDB.MU>

thanking you in advance for your views.

regards
yogeeraj

hi again,

thank you for your reply.

sorry for the wrong example posted, below the output when we try to create the materialized view:
=====================================
yd@MYDB.MU> create materialized view mv2 as select cast(sum(f1) as number(5,2)) f1, f2 from t1 group by f2

Snapshot created.

Elapsed: 00:00:00.42
yd@MYDB.MU> desc mv2
Name Null? Type
------------------------------- -------- ----
F1 NUMBER
F2 VARCHAR2(2)

yd@MYDB.MU>


Here still this does not come out well.

please comment.

thank you
kind regards
yogeeraj

ops$tkyte@ORA9IR2> create table t1 (f1 number(5,2), f2 varchar2(2));
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view mv2 as select cast(sum(f1) as number(5,2))
  2  f1, f2 from t1 group by f2;
 
Materialized view created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> desc mv2
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 F1                                           NUMBER(5,2)
 F2                                           VARCHAR2(2)


works in currently supported versions, but you don't mention a version. 

hi,

version is 8.1.7.4. and it does not work. Tried on 10.1.0.3.0 where it works fine!

i guess not workarounds exists too!

thank you for your precious time. we really appreciate.
yogeeraj

Hi again,

thank you for this further guidance.

It works as far the precision of columns in the defining query is concerned. However, in Oracle 8i, the Materialized view is not refreshing properly. Seems like a bug.

(we have tested the same steps in 10g test database and it works fine)

if you have more insights, please help us.

thanking you in advance.
kind regards
yogeeraj

=================================================================================
Oracle 8i (8.1.7.4)
-------------------
yd@MYDB.ORA8I.MU> create table t1 (f1 number(5,2), f2 varchar2(2));

Table created.

Elapsed: 00:00:00.02
yd@MYDB.ORA8I.MU> create table mv2 (f1 number(5,2), f2 varchar2(2) );

Table created.

Elapsed: 00:00:00.04
yd@MYDB.ORA8I.MU> begin
for c1 in 1..2 loop
for c2 in 1..5 loop
insert into t1 values(c2+0.1,to_char(c1));
end loop;
end loop;
end;
/
2 3 4 5 6
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
yd@MYDB.ORA8I.MU> commit;

Commit complete.

Elapsed: 00:00:00.01
yd@MYDB.ORA8I.MU> select * from t1;

F1 F2
__________ __
1.1 1
2.1 1
3.1 1
4.1 1
5.1 1
1.1 2
2.1 2
3.1 2
4.1 2
5.1 2

10 rows selected.

Elapsed: 00:00:00.01
yd@MYDB.ORA8I.MU>
yd@MYDB.ORA8I.MU> create materialized view mv2
on PREBUILT TABLE with reduced precision
as
select sum(f1) f1, f2 from t1 group by f2;

Snapshot created.

Elapsed: 00:00:00.46
yd@MYDB.ORA8I.MU> desc mv2
Name Null? Type
------------------------------- -------- ----
F1 NUMBER(5,2)
F2 VARCHAR2(2)

yd@MYDB.ORA8I.MU> select * from mv2;

no rows selected

Elapsed: 00:00:00.01
yd@MYDB.ORA8I.MU> exec dbms_mview.refresh('mv2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.75
yd@MYDB.ORA8I.MU> select * from mv2;

no rows selected

Elapsed: 00:00:00.00
yd@MYDB.ORA8I.MU>
=================================================================
Oracle 10g (10.1.0.3.0)
-----------------------
$ sqlplus

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Jun 29 09:06:04 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Enter user-name: yd/**

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

yd@MYDB.ORA10G.MU> clear scre
yd@MYDB.ORA10G.MU> create table t1 (f1 number(5,2), f2 varchar2(2));

Table created.

yd@MYDB.ORA10G.MU> begin
for c1 in 1..2 loop
for c2 in 1..5 loop
insert into t1 values(c2+0.1,to_char(c1));
end loop;
end loop;
end;
/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
yd@MYDB.ORA10G.MU> commit;

Commit complete.

yd@MYDB.ORA10G.MU> select * from t1;

F1 F2
__________ __
1.1 1
2.1 1
3.1 1
4.1 1
5.1 1
1.1 2
2.1 2
3.1 2
4.1 2
5.1 2

10 rows selected.

Elapsed: 00:00:00.00
yd@MYDB.ORA10G.MU>
yd@MYDB.ORA10G.MU> create table mv2 (f1 number(5,2), f2 varchar2(2) );

Table created.

yd@MYDB.ORA10G.MU> create materialized view mv2
on PREBUILT TABLE with reduced precision
as
select sum(f1) f1, f2 from t1 group by f2;

2 3 4
Materialized view created.

yd@MYDB.ORA10G.MU>
yd@MYDB.ORA10G.MU> desc mv2
Name Null? Type
----------------------------------------- -------- ----------------------------
F1 NUMBER(5,2)
F2 VARCHAR2(2)

yd@MYDB.ORA10G.MU> select * from mv2;

no rows selected

yd@MYDB.ORA10G.MU> exec dbms_mview.refresh('mv2');

PL/SQL procedure successfully completed.

yd@MYDB.ORA10G.MU> select * from mv2;

F1 F2
---------- --
15.5 1
15.5 2

yd@MYDB.ORA10G.MU>

hi,

we thank you a lot! I works now. (see below)

As for our under supported software, we are in the final stages of migration of the remaining Forms 3.0 applications to developer 6i.

As for the database component, we have decided to wait for the next release (10g DB release 2) since we had difficulties troubleshooting the Oracle CSS daemon startup problem after applying patchset 2 (10.1.0.4) to our test environment (Our Primary Support not being able to provide effective support because of time lag)

Your guidances will help us in programming our Microsoft Sharepoint office webpart objects to be able to query our Oracle Database objects.

thanking you again
kind regards
yogeeraj

================================
yd@MYDB.ORA8I.MU> desc mv2
Name Null? Type
------------------------------- -------- ----
F1 NUMBER(5,2)
F2 VARCHAR2(2)

yd@MYDB.ORA8I.MU> select * from mv2;

no rows selected

Elapsed: 00:00:00.00
yd@MYDB.ORA8I.MU> exec dbms_mview.refresh('mv2','c');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
yd@MYDB.ORA8I.MU> select * from mv2;

F1 F2
__________ __
15.5 1
15.5 2

Elapsed: 00:00:00.00
yd@MYDB.ORA8I.MU> insert into t1 select * from t1;

10 rows created.

Elapsed: 00:00:00.01
yd@MYDB.ORA8I.MU> commit;

Commit complete.

Elapsed: 00:00:00.02
yd@MYDB.ORA8I.MU> exec dbms_mview.refresh('mv2','c');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
yd@MYDB.ORA8I.MU> select * from mv2;

F1 F2
__________ __
31 1
31 2

Elapsed: 00:00:00.01
yd@MYDB.ORA8I.MU>

Hi again!

We thought you had always recommended us to go for the latest versions.

But still, we have to do all our tests (applications, integration, etc) with before we can migrate to 10g. It might well take us a minimum of 3 months (resources --[more disk and memory] and business downtime constraints). So in that case, wouldn't it be wiser to start working with 10g release 2?

thank you for your advice
kind regards
yogeeraj

Hi Tom,
We are migrating from Oracle 8.1.7.4 to Oracle 9.2.0.6. Some of the instances are still Oracle 8.1.7.4 and we have to live up with dual versions for sometime.

Whenever the Materialized View Query contains user defined function calls, the datatype created is by default "varchar2(4000)". We have couple of materialized views with 7-10 function calls.

Well, one can do "cast" as you have shown or have a prebuilt table with the column length appropriately defined based on the function return value (not 4000).

The pre-built table approach works fine in Oracle 9i versions but fails in Oracle 8.1.7.4 environment with errors:
1) "ORA-12060: shape of pre-built table does not match definition query" when not using CAST
2) Also "cast(NULL as VARCHAR2(415)" fails but
"cast('' as VARCHAR2(415)"  WORKS...
3) Further, though it does not appear so but is there any downside to using multiple CAST statetements in the query ?
==========================================================
=======sql plus output ======================
SQL> CREATE TABLE scott.TRY99
  2  (
  3    TEST_ID       NUMBER(15)  NOT NULL,
  4    TEST_ID_DESC  VARCHAR2(200)
  5  );
Table created.

SQL> alter table scott.TRY99 add constraint pk_try99 primary key (test_id);
Table altered.

SQL> CREATE TABLE scott.MV_TRY99
  2  (
  3    TEST_ID     NUMBER(15)   NOT NULL,
  4    TEST_ID_DESC_concat  VARCHAR2(415)
  5  );
Table created.

SQL> CREATE MATERIALIZED view scott.mv_try99
  2  on prebuilt table with reduced precision
  3  refresh complete on demand as
  4  select test_id, concat(TEST_ID_DESC,test_id) TEST_ID_DESC_concat
  5  from scott.try99;
select test_id, concat(TEST_ID_DESC,test_id) TEST_ID_DESC_concat
                *
ERROR at line 4:
ORA-12060: shape of prebuilt table does not match definition query

SQL> CREATE MATERIALIZED view scott.mv_try99
  2  on prebuilt table with reduced precision
  3  refresh complete on demand as
  4  select test_id, cast( concat(TEST_ID_DESC,test_id) as varchar2(415)) TEST_ID_DESC_concat
  5  from scott.try99;
Materialized view created.

SQL> drop MATERIALIZED view scott.mv_try99;
Materialized view dropped.

SQL> CREATE MATERIALIZED view scott.mv_try99
  2  on prebuilt table with reduced precision
  3  refresh complete on demand as
  4  select test_id, cast( NULL as varchar2(415)) TEST_ID_DESC_concat
  5  from scott.try99;
select test_id, cast( NULL as varchar2(415)) TEST_ID_DESC_concat
                      *
ERROR at line 4:
ORA-12060: shape of prebuilt table does not match definition query

SQL> 
SQL> CREATE MATERIALIZED view scott.mv_try99
  2  on prebuilt table with reduced precision
  3  refresh complete on demand as
  4  select test_id, cast( '' as varchar2(415)) TEST_ID_DESC_concat
  5  from scott.try99;

Materialized view created.
======================================================
Please enlighten...

Thanks
 

Could someone tell me how "TAX_CATEGORY_VALUE.ALIAS" column is listed in my Materialized View.

There are some packages refering to TAX_CATEGORY_VALUE.ALIAS column.

The package is VALID since we have this column  listed in 8i, however after migrating to 9i the package becomes INVALID because the column is not listed anymore.

From 8i:
========
SQL> select owner,object_name,object_type from dba_objects where object_name='TAX_CATEGORY_VALUE';
 
OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------
PUBLIC                         TAX_CATEGORY_VALUE             SYNONYM
HIBM_TAXONOMY                  TAX_CATEGORY_VALUE             TABLE
HIBM_TAXONOMY                  TAX_CATEGORY_VALUE             UNDEFINED
 
SQL> select query from dba_mviews where MVIEW_NAME='TAX_CATEGORY_VALUE';
 
QUERY
--------------------------------------------------------------------------------
SELECT "TAX_CATEGORY_VALUE"."ID" "ID","TAX_CATEGORY_VALUE"."NAME_ID" "NAME_ID","
TAX_CATEGORY_VALUE"."VALUE" "VALUE","TAX_CATEGORY_VALUE"."IS_ACTIVE" "IS_ACTIVE"
 FROM "TAX_CATEGORY_VALUE"@Q2HIBM.US.HSBC.COM "TAX_CATEGORY_VALUE"
 
 
SQL>  desc TAX_CATEGORY_VALUE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 NAME_ID                                   NOT NULL NUMBER(10)
 VALUE                                     NOT NULL VARCHAR2(100)
 IS_ACTIVE                                          NUMBER(38)
 ALIAS                                              NUMBER(10)
 
SQL>





From 9i:
=======
SQL>  select owner,object_name,object_type from dba_objects where object_name='TAX_CATEGORY_VALUE';
 
OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------
PUBLIC                         TAX_CATEGORY_VALUE             SYNONYM
HIBM_TAXONOMY                  TAX_CATEGORY_VALUE             TABLE
HIBM_TAXONOMY                  TAX_CATEGORY_VALUE             MATERIALIZED VIEW
 
SQL>
SQL> desc TAX_CATEGORY_VALUE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 NAME_ID                                   NOT NULL NUMBER(10)
 VALUE                                     NOT NULL VARCHAR2(100)
 IS_ACTIVE                                          NUMBER(38)
 

 

I'll ask you to work with support on this, that column "alias" should not be there given the materialized view text.

but in any case, since your MV didn't "ask" for it, even if it is there, why does your code reference it?  

How did you do the upgrade from 8i to 9i? The only thing I can think of is

a) you created the mv in 8i
b) you manually added alias
c) you upgraded to 9i via export/import or some other tool
d) hence your manual addition won't come along


ops$tkyte@ORA9IR2> create table t as select * from scott.emp;

Table created.

ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(empno);

Table altered.

ops$tkyte@ORA9IR2> create materialized view log on t;

Materialized view log created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view mv
  2  refresh fast
  3  as
  4  select * from t;

Materialized view created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t set ename = lower(ename) where rownum = 1;

1 row updated.

ops$tkyte@ORA9IR2> exec dbms_mview.refresh('MV');

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> alter table mv add alias number(10);

Table altered.

ops$tkyte@ORA9IR2> update t set ename = lower(ename);

14 rows updated.

ops$tkyte@ORA9IR2> exec dbms_mview.refresh('MV');

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select query from user_mviews where mview_name = 'MV';

QUERY
-------------------------------------------------------------------------------
SELECT "T"."EMPNO" "EMPNO","T"."ENAME" "ENAME","T"."JOB" "JOB","T"."MGR" "MGR",
"T"."HIREDATE" "HIREDATE","T"."SAL" "SAL","T"."COMM" "COMM","T"."DEPTNO" "DEPTN
O" FROM "T" "T"


ops$tkyte@ORA9IR2> desc mv
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 EMPNO                                    NOT NULL NUMBER(4)
 ENAME                                             VARCHAR2(10)
 JOB                                               VARCHAR2(9)
 MGR                                               NUMBER(4)
 HIREDATE                                          DATE
 SAL                                               NUMBER(7,2)
 COMM                                              NUMBER(7,2)
 DEPTNO                                            NUMBER(2)
 ALIAS                                             NUMBER(10)

ops$tkyte@ORA9IR2>
 

Thanks Tom,

Someone added new column just like how they add in table without realizing that this is a materialized view.
[
As you did
ops$tkyte@ORA9IR2> alter table mv add alias number(10);
Table altered.
]

Why Oracle is letting someone to add columns to the Materialized View? The newly added column would be never refreshed from the Source(Master Site/Table). What is the point then?

Thanks Tom.

Tom,

When I use select empno,cast(ename as varchar2(10)) from emp
it's giving me following error

ORA-12801: error signaled in parallel query server P001, instance tsjipudbd01:MRDDA1 (1)
ORA-00910: specified length too long for its datatype

could you please throw some light on this

Actually i want to create materialized view in which there are almost 140 columns and I don't know which column will not contain any data

for ex
create materialized view mv as
select a1,a2,a3,a4,a5 from <table_name>;

where any of a1,a2,a3,a4,a5 columns may not contain data

Thanks

Thanks for quick turnaround

please let me know how I can create a materialized view from a select statement in which i dont know which columns data is entierly null

Thanks a lot
Anil

I've created a bunch of materialized views against tables in remote databases, all without logs or any additional clauses such as "with primary key".
After this I run a script to create the primary keys, and some of these statements fail because the key already exists.
What is the rule as to when a materialized view automatically gets a primary key?

Thanks.

Hi Tom,

Remote DB:
---------
create table test (id number(10),str varchar2(20));

insert into test values(10,'Test1');
insert into test values(10,'Test1');
commit;


Local DB:
---------
create materialized view
test_mv
refresh on demand
as
select str from test@link;


When I say

Select * from test_mv;

It shows only those records that
were added before creating the snapshot.

Whenever I execute any DML on Test in remote DB,
it doesn't reflect in the snapshot in the local DB.


What am I missing here?



2.

Why doesn't this work?

create materialized view
test_mv
refresh on commit
as
select str from test@link;


Error:
ORA-12054: cannot set the ON COMMIT refresh
attribute for the materialized view.

Please clarify.

Thanks.

Hi Tom,

Is there a way to control the datatype (specifically precision of a NUMBER column) in a normal view.

I noticed that casting does not result in precision, eg cast (null as number(12)) id still results in NUMBER as the datatype in the view and not NUMBER(12).

I also would like to control the precision, when the view has a union and corresponding columns have different precision.

eg.

create view a_vw (id) -- gets NUMBER as datatype
as select c1 -- NUMBER(11)
from t1
union
select c2 -- NUMBER(12)
from t2;

thanks,

Vinod

Hi Tom
You wrote that it is impossible to create a MV with ON COMMIT option over a DBLink, How about different schemas?
For example:
create materialized view mv_x refresh fast on commit
start with sysdate next sysdate+1
as
select * from SRCDB.x1;

thanks,
Avishay

on commit works in a database, yes that should work (but hopefully you would not do it - unless you were aggregating the data, this would not make sense)


ops$tkyte@ORA10GR1> create user a identified by a default tablespace users quota unlimited on users;
User created.

ops$tkyte@ORA10GR1> grant create session, create materialized view, create table to a;

Grant succeeded.

ops$tkyte@ORA10GR1> grant alter session to a;

Grant succeeded.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> create table t ( x int primary key, y int );

Table created.

ops$tkyte@ORA10GR1> create materialized view log on t;

Materialized view log created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> grant all on t to a;

Grant succeeded.

ops$tkyte@ORA10GR1> grant all on MLOG$_T to a;

Grant succeeded.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> connect a/a
Connected.
a@ORA10GR1> @trace
a@ORA10GR1> alter session set events '10046 trace name context forever, level 12';

Session altered.

a@ORA10GR1> create materialized  view mv
  2  refresh fast on commit
  3  as
  4  select * from ops$tkyte.t;

Materialized view created.

a@ORA10GR1>
a@ORA10GR1> connect /
Connected.
ops$tkyte@ORA10GR1> insert into t values ( 1, 2 );

1 row created.

ops$tkyte@ORA10GR1> commit;

Commit complete.

ops$tkyte@ORA10GR1> connect a/a
Connected.
a@ORA10GR1> select * from mv;

         X          Y
---------- ----------
         1          2

 

I was missing a privilege,
this why it didn't work for me !

BTW will MV ON COMMIT select statement work with a UNION clause?

OK,

Will an MV (FAST ON COMMIT) with a UNION ALL work ?

So far I didn't manage to create one
I was able to create a REFRESH COMPLETE though, but that is not what I need

Regards,
Avishay

Tom,

I have observed that in most of our databases (either 8i or 9i) Oracle automatically expands the "select *" clause in a materialized view definition (either based on local or remote tables) into the current columns of master tables. When we add new columns into the master tables, the mview definition is not affected and the refresh has no problem. However, there is one single 9i instance we have which behaves very differently. If we use "select *" then the dba_mviews entry remains as "select *" instead of expanding the "*" sign. Moreover, after we added new columns into the master tables, the refresh fails and it complained with these errors:

ORA-12008: error in materialized view refresh path
ORA-00913: too many values
ORA-06512: at "SYS.DBMS_SNAPSHOT" line 803

My question is, is there any parameter in DB that controls whether Oracle to expand or not to expand "select *" for mview definition?

Thanks

I tried both "select * from tname" and "select a.* from tname a" and there was no difference. In that db neither expanded into columns.

But this started me to try a combination of "comparison" experiments. I tried db links between 8i and 8i, 8i and 9i, 9i and 9i. I also tried fast referesh and complete refresh, and so on. Finally I found out the reason. It happens in that instance of Oracle, the db link was pointing to a read-only user on remote db which was actually accessing the master tables through public synonyms. If I explicitly specify the table owner in front of the master table name, and duh, it expanded "select *" into columns in the dba_mviews.

I also tried synonyms for materialized views depending on only local tables (in different schema) and it's true there too that "select * " from synonym doesn't expand and select * from owner.table does. Also those mviews with expanded columns have no problem in refershing after new columns are added to master tables while those without column expansion do give ORA-12008 and ORQA-00913.

I guess the reason is that Oracle actually treated select * from <syonym>, no matter local or remote, as complex query and decides not to expand the columns in its data dictionary.

Thanks for your time!

Dear Tom.

I have below db objects:

CREATE TABLE valyuta_mezenneleri
(valyuta_mezenne_id NUMBER(6,0) NOT NULL,
valyuta_id NUMBER(3,0),
valyuta_mezenne_tarixi DATE,
valyuta_mezenne_kursu NUMBER(8,4))
/

CREATE INDEX idx_valyuta_id ON valyuta_mezenneleri
(
valyuta_id ASC
)
/

ALTER TABLE valyuta_mezenneleri
ADD CONSTRAINT pk_valyuta_mezenne_id PRIMARY KEY (valyuta_mezenne_id)
USING INDEX


CREATE OR REPLACE VIEW view_valyuta_cari_gun_mezenne (
valyuta_mezenne_id,
valyuta_id,
valyuta_mezenne_tarixi,
valyuta_mezenne_kursu )
AS
SELECT *
FROM valyuta_mezenneleri a
WHERE a.valyuta_mezenne_id IN

(
SELECT MAX(valyuta_mezenne_id)
FROM valyuta_mezenneleri
GROUP BY valyuta_id
)
/


create materialized view mv_valyuta_cari_gun_mezennesi
build immediate
refresh on commit
enable query rewrite
as
select *
from VIEW_VALYUTA_CARI_GUN_MEZENNE


I set query_rewrite_enabled=true and query_rewrite_integrity=enforced at session level.

But it when createing materialized view I got 12054 error.
But it works wher I change to "refresh on demand"

Thanks in advance.

Hi Tom

I get a strange ORA-00600 which It seems that I can control, on 10g on Solaris, when I try to compile materialized view based on 
a join of few tables with rowid and order by.

The simple test case I created for you is below.

Table a, table b and materialized view mv_ab, here we go.





Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options



create table  a (a_id number, a_txt char(10));
alter table a add constraint pk_a_id primary key(a_id);

Table created

create table b (
b_id number, 
a_id number, 
b_txt char(10),
constraint pk_b_id primary key(b_id),
constraint fk_a_id foreign key (a_id) references A(a_id)
)
partition by range (a_id) subpartition by hash (b_id)
subpartitions 3
(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (MAXVALUE)
);


Table created

insert into A values (1, 'Tarzan');
insert into A values (2, 'Hercules');
insert into A values (3, 'Ulysses');

insert into B values (10, 1, 'B');
insert into B values (20, 2, 'A');
insert into B values (30, 3, 'C');


SQL> select * from a;

      A_ID A_TXT
---------- ----------
         1 Tarzan
         2 Hercules
         3 Ulysses

SQL> select * from b;

      B_ID       A_ID B_TXT
---------- ---------- ----------
        30          3 C
        10          1 B
        20          2 A


create materialized view log on A with rowid;

Materialized view log created

create materialized view log on B with rowid;

Materialized view log created


create materialized view mv_ab
build immediate
refresh fast on commit
as
select
a.rowid a_rowid
,b.rowid b_rowid
,a.a_txt
,b.b_txt
from a, b
where a.a_id=b.a_id
order by a.a_id


Materialized view created

SQL> alter materialized view mv_ab compile;

alter materialized view mv_ab compile

ORA-00600: internal error code, arguments: [qsmqSetupTableMetadata-2], [], [], [], [], [], [], []


When I do


QL> create materialized view mv_ab2
  2  build immediate
  3  refresh fast on commit
  4  as
  5  select
  6  a.rowid a_rowid
  7  ,b.rowid b_rowid
  8  ,a.a_txt
  9  ,b.b_txt
 10  from a, b
 11  where a.a_id=b.a_id
 12  -- order by a.a_id -- remove order by
 13  /

Materialized view created

SQL> alter materialized view mv_ab2 compile;

Materialized view altered

No problems ??!
 

Many thanks for all your help.

Kubilay  

Hi Tom

Help me out a bit, please!

What does Perfect mean in your response?

1. Does it mean you run the test case I posted and gave you the same results?
2. Or is it just my poor server doing this and I need to sort it out with support?

If it is 1 , will be happier to find out that I am not doing anything wrong and that I am not alone!

If it is 2, will have to read more more and more ... :-)

I have already posted this to metalink and they asked me to give the alert log and the user dumps, which I will upload shortly.

Best Regards

Kubilay

You have the perfect reproducible test case

it is 

a) small
b) yet 100% complete
c) and small
d) and entirely self contained.

It is "perfect"

ora-600, ora-7445, ora-3113 => support, they are not supposed to happen.


Support should love this one - it is easy for them, you have everything you need to reproduce the issue!


ops$tkyte@ORA10GR2> alter materialized view mv_ab compile;
alter materialized view mv_ab compile
            *
ERROR at line 1:
ORA-00600: internal error code, arguments: [qsmqSetupTableMetadata-2], [], [],
[], [], [], [], []

I could not have set up a better test case myself.
 

What privilege(s) do I need to be able to generate a materialized view dynamically?  I was able to create a materialized view with a PLSQL block, but not with a package even after I explicitly grant "CREATE MATERIALIZED VIEW" to the user.

SQL> 
SQL> connect test/test@orcl
Connected.
SQL> DECLARE
  2     l_mv VARCHAR2(4000) :=
  3  'CREATE MATERIALIZED VIEW test_mv BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND
  4   AS SELECT COUNT(*), object_type FROM all_objects GROUP BY object_type';
  5  
  6  BEGIN
  7     EXECUTE IMMEDIATE l_mv;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> desc test_mv
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------------------------------------
 COUNT(*)                                                                         NUMBER
 OBJECT_TYPE                                                                      VARCHAR2(19)

SQL> drop materialized view test_mv;

Materialized view dropped.

SQL> CREATE OR REPLACE PACKAGE test_pkg AS
  2  
  3     PROCEDURE Create_MV;
  4  
  5  END;
  6  /

Package created.

SQL> 
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg AS
  2  
  3  PROCEDURE Create_MV
  4  IS
  5     l_base_MV VARCHAR2(32000) :=
  6  'CREATE MATERIALIZED VIEW test_mv BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND
  7   AS SELECT COUNT(*), object_type FROM all_objects GROUP BY object_type';
  8  
  9  BEGIN
 10     EXECUTE IMMEDIATE l_base_MV;
 11  END Create_MV;
 12  
 13  END test_pkg;
 14  /

Package body created.

SQL> 
SQL> 
SQL> show error
No errors.
SQL> exec test_pkg.Create_MV;
BEGIN test_pkg.Create_MV; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.TEST_PKG", line 10
ORA-06512: at line 1


SQL> connect sys@orcl as sysdba
Connected.
SQL> grant create materialized view to test;

Grant succeeded.

SQL> 
SQL> connect test/test@orcl
Connected.
SQL> 
SQL> exec test_pkg.Create_MV;
BEGIN test_pkg.Create_MV; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.TEST_PKG", line 10
ORA-06512: at line 1


SQL> spool off

 

for why you need the direct grant.

In response to a question on Nov 17, 2005, you said there is no way to force a specific length and/or precision for columns in a view (unless you are selecting a column from another table or view that happens to have the desired length/precision).

Is there a reason for this, other than 'that's the way it is'? It might help me think of alternatives.

The poster on that day was talking about "views with a union".  Some operations will hide the precision and scale - and that'll be a UNION, without it, the results can be different:

ops$tkyte@ORA9IR2> create or replace view myview
  2  as
  3  select substr(object_name,1,10) ss10,
  4         cast(object_name as varchar2(15)) vc15,
  5             cast(object_id as number(7,2)) num1,
  6             cast( null as number(12)) num2
  7    from all_objects
  8  /

View created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> desc myview
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 SS10                                              VARCHAR2(10)
 VC15                                              VARCHAR2(15)
 NUM1                                              NUMBER(7,2)
 NUM2                                              NUMBER(12)

ops$tkyte@ORA9IR2> create or replace view myview
  2  as
  3  select substr(object_name,1,10) ss10,
  4         cast(object_name as varchar2(15)) vc15,
  5             cast(object_id as number(7,2)) num1,
  6             cast( null as number(12)) num2
  7    from all_objects
  8   union
  9  select substr(object_name,1,10) ss10,
 10         cast(object_name as varchar2(15)) vc15,
 11             cast(object_id as number(7,2)) num1,
 12             cast( null as number(12)) num2
 13    from all_objects
 14  /

View created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> desc myview
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 SS10                                              VARCHAR2(10)
 VC15                                              VARCHAR2(15)
 NUM1                                              NUMBER
 NUM2                                              NUMBER

 

Respected Mr. Tom,

In the Oracle 9i Data warehousing guide, chapter – 8, the following points are written.

To register the existing materialized view tables with Oracle,
1. Use “CREATE MATERIALIZED VIEW ... ON PREBUILT TABLE statement.
2. The table and the materialized view MUST have the SAME Name.

I request you to see the heading “partitioning a prebuilt Table” under that chapter – 8 and you will definitely find one materialized view “part_sales_tab_mv” which clearly flouts the second point – “MUST have the SAME Name”.
To this thread, you always use the same name for table and materialized view as well when “ON PREBUILT TABLE” is used.

My question – In the document, is that example wrong or okay where table name is “part_sales_tab” and materialized view name is “part_sales_tab_mv” using “ON PREBUILT TABLE” or I am wrong to get the topic?

Tom,

I need to replicate two tables from remote database to local. But those tables on remote system do not have primary key but i want to use "prebuild table option" while creating Mviews on our database.

So, in the table definition, i added dummy primary key.

SQL> desc NETWORX_SERVICE_TYPE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NETWORX_SERVICE_TYPE_ID                   NOT NULL NUMBER(11)   <---- dummy column in local db which acts as pk
 RFP_SERVICE_ID                                     VARCHAR2(15)
 ATT_SERVICE_TYPE_CODE                              VARCHAR2(4)
 SERVICE_TYPE_NAME                                  VARCHAR2(50)
 SUB_SERVICE_CODE                                   VARCHAR2(15)
 SUB_SERVICE_NAME                                   VARCHAR2(65)
 NIC_SOURCE                                         VARCHAR2(4)

In order to populate primary key, i used "rownum" in the select list of the MV query. Is "rownum" supported in MV ?

 1  create materialized view NETWORX_SERVICE_TYPE
  2  ON PREBUILT TABLE REFRESH ON DEMAND
  3  with primary key
  4  as select
  5  ROWNUM "NETWORX_SERVICE_TYPE_ID",
  6  RFP_SERVICE_ID,
  7  ATT_SERVICE_TYPE_CODE,
  8  SERVICE_TYPE_NAME,
  9  SUB_SERVICE_CODE,
 10  SUB_SERVICE_NAME,
 11  NIC_SOURCE
 12* from SERVICE_REF_TBL_VIEW_MV@dbprod18_db_link
SQL> /
ROWNUM "NETWORX_SERVICE_TYPE_ID",
       *
ERROR at line 5:
ORA-12060: shape of prebuilt table does not match definition query

But if i tried with "ON PREBUILT TABLE WITH REDUCED PRECISION", i am able to create the MV. Is this the right way to do it or can you please suggest the work around for this ?

1  create materialized view NETWORX_SERVICE_TYPE
  2  ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH ON DEMAND
  3  with primary key
  4  as select
  5  ROWNUM "NETWORX_SERVICE_TYPE_ID",
  6  RFP_SERVICE_ID,
  7  ATT_SERVICE_TYPE_CODE,
  8  SERVICE_TYPE_NAME,
  9  SUB_SERVICE_CODE,
 10  SUB_SERVICE_NAME,
 11  NIC_SOURCE
 12* from SERVICE_REF_TBL_VIEW_MV@dbprod18_db_link
SQL> /

Materialized view created.


It is just a plain copy from the remote table to local table via Mviews.
 

this is never going to work, think about it.

rownum is assigned as a psuedo column, made up on the fly, it isn't necessarily going to be the same assignment every time.  

ops$tkyte%ORA10GR2> create table t ( x int, y varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 100, rpad( '*', 4000, '*' ) );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 200, rpad( '*', 1, '*' ) );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 300, rpad( '*', 2000, '*' ) );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rownum, x from t;

    ROWNUM          X
---------- ----------
         1        100
         2        200
         3        300

ops$tkyte%ORA10GR2> update t set y = rpad('*',4000,'*') where x = 200;

1 row updated.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select rownum, x from t;

    ROWNUM          X
---------- ----------
         1        100
         2        300
         3        200



why are you trying to stick rownum in there, what is the goal???? 

<< why are you trying to stick rownum in there, what is the goal???? 

Tom, My question on using "rownum" was with respect to select rownum, <query> in building the materialized view.

As "prebuilt table" option cannot be used with "rowid" based materialized, i had to create dummy primary key for the sake of using "prebuilt option". 

When MV refreshes, as the rows pulled from remote database, it seems giving me the unique "rownum" on the fly, which is inturn used to populate the dummy primary key.

I was able to use rownum in MV select list using "ON PREBUILT TABLE WITH REDUCED PRECISION". It seems working. 
Is there any issue you are aware of using "reduced precision" with simple table Mview (table to table ) like this ? Would like to get your suggestions.

1  create materialized view NETWORX_SERVICE_TYPE
  2  ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH ON DEMAND
  3  with primary key
  4  as select
  5  ROWNUM "NETWORX_SERVICE_TYPE_ID",
  6  RFP_SERVICE_ID,
  7  ATT_SERVICE_TYPE_CODE,
  8  SERVICE_TYPE_NAME,
  9  SUB_SERVICE_CODE,
 10  SUB_SERVICE_NAME,
 11  NIC_SOURCE
 12* from SERVICE_REF_TBL_VIEW_MV@dbprod18_db_link
SQL> /

Materialized view created.
>>

 

I saw your example. Agree but i do not worry much about the pseudo PK column order in my scenario as application is not referring/using this dummy primary key column in materialized view.

Appreciate if you could reply to this question
"I was able to use rownum in MV select list using "ON PREBUILT TABLE WITH REDUCED PRECISION". It seems working. 
Is there any issue you are aware of using "reduced precision" with simple table Mview (table to table ) like this ? Would like to get your suggestions."


<<
1  create materialized view NETWORX_SERVICE_TYPE
  2  ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH ON DEMAND
  3  with primary key
  4  as select
  5  ROWNUM "NETWORX_SERVICE_TYPE_ID",
  6  RFP_SERVICE_ID,
  7  ATT_SERVICE_TYPE_CODE,
  8  SERVICE_TYPE_NAME,
  9  SUB_SERVICE_CODE,
 10  SUB_SERVICE_NAME,
 11  NIC_SOURCE
 12* from SERVICE_REF_TBL_VIEW_MV@dbprod18_db_link
SQL> /

Materialized view created.
<< 

My materialized does complete refresh.
The reason i wanted to use this dummy primary key in MV is "to use prebuilt table option" while creating materialized view.

Tom,

I am facing a strange problem during the refresh process on the materialized views setup via dbms_job (which calls stored procedure which does MV refresh).

I have two materalized views (built using prebuilt option) does complete refresh with remote source database materialized view (remote materialized views are ROWID based). I added dummy primary key in my local materialized view in order to use prebuilt option.

The problem is, when automated by dbms_job, randomly or very often, the refresh of one materialized view (NETWORX_CLIN_INFO_REF )gets "0 rows" count but at the same time the other materialized view (NETWORX_SERVICE_TYPE) gets the correct count always in sync with source MV. The same job (which invokes materialized view refresh) got the complete data and worked successfully yesterday but today
the same job gets "0 rows" in the same materialized view.
Both materialized views are using "ROWNUM" in MV SELECT query. I am guaranteed that the remote materialized view does have rows when materialized view refeshed and there is no overlap in refresh also.


If i run the same job, via DBMS_JOB.RUN(JOBID), it works most of the time successfully but one time encountered the same behaviour, i got the same "zero" row count.

I am providing my materialized view code and stored procedure as well:

-- Materialized View code

drop materialized view NETWORX_CLIN_INFO_REF ;
create materialized view NETWORX_CLIN_INFO_REF
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH ON DEMAND
with primary key
disable query rewrite
as select
ROWNUM "NETWORX_CLIN_INFO_REF_ID",
CONTRACT_ID ,
CONTRACT_SVC_ID ,
CLIN ,
NTWX_CLIN_CATG ,
COMP_TYPE ,
CLIN_DESC ,
SVC_LVL ,
FROM_GEOGRAPHIC_REGION ,
CHARGE_TYPE ,
MRC_NRC_CLIN ,
ACCESS_TYPE ,
PLAN_MINUTE ,
TRANS_TYPE ,
PORT_TYPE ,
BANDWIDTH ,
SERVICE_TYPE ,
BW_BAND_LOW ,
BW_BAND_HIGH ,
RFP_TBL ,
PROVIDER_TYPE ,
BASED_TYPE ,
CHARGE_UNIT ,
SED_SUITE ,
MFGR ,
MODEL_NO ,
DMRC_TERM ,
SOFTWARE_PLATFORM ,
RACK_SIZE ,
FIBER_BAND_HIGH ,
FIBER_BAND_LOW ,
TRANSMISSION_SPAN ,
ACCESS_ARNGMNT_TYPE ,
ACCESS_PURCHASE_ARNGMNT ,
TIER ,
NO_OF_USERS ,
CONNECTION_TYPE ,
FIREWALL_TIER ,
FIREWALL_LOCATION ,
NO_OF_IPADDR ,
NO_OF_SCAN ,
ANTIVIRUS_LOCATION ,
USER_BAND_HIGH ,
USER_BAND_LOW ,
AUTH_TYPE ,
INBOUND_MIN ,
OUTBOUND_MIN ,
STORAGE_CAPACITY ,
SUBSCRIPTION_MSG ,
MSG_TYPE ,
MSG_DIRECTION ,
TERMINAL_BAND ,
SATELITE_TYPE ,
LOCATION ,
TRANSMISSION_CONTENT ,
CLIN_PRIMARY_LOCATION ,
IN_EFFECT_DATE ,
END_EFFECT_DATE ,
ICB_IND ,
NSP_IND ,
UBI_CODE ,
SYSDATE as REFRESH_DATE
from CLIN_VIEW_MV@dbprod18_db_link
/

drop materialized view NETWORX_SERVICE_TYPE ;
create materialized view NETWORX_SERVICE_TYPE
ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH ON DEMAND
with primary key
as select
ROWNUM "NETWORX_SERVICE_TYPE_ID",
RFP_SERVICE_ID,
ATT_SERVICE_TYPE_CODE,
SERVICE_TYPE_NAME,
SUB_SERVICE_CODE,
SUB_SERVICE_NAME,
NIC_SOURCE,
SERVICE_TYPE_NUM,
SYSDATE as REFRESH_DATE
from SERVICE_REF_TBL_VIEW_MV@dbprod18_db_link
/
--

stored package code which gets invoked by DBMS_JOB :

create or replace package body networx_mv_refresh_pkg
as
procedure networx_mv_refresh is
begin
dbms_mview.refresh('NETWORX_CLIN_INFO_REF', 'C') ;
dbms_mview.refresh('NETWORX_SERVICE_TYPE', 'C') ;
dbms_stats.gather_table_stats('IOMDBO', tabname => 'NETWORX_CLIN_INFO_REF', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', degree => dbms_stats.default_degree, cascade => true) ;
dbms_stats.gather_table_stats('IOMDBO', tabname => 'NETWORX_SERVICE_TYPE', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', degree => dbms_stats.default_degree, cascade => true) ;
end networx_mv_refresh ;
end networx_mv_refresh_pkg ;
/
======


Manual refresh of executing this package always refreshed correctly. 'NETWORX_CLIN_INFO_REF' is the one i am having problem with "0 rows count".

Can you please advise what could be the cause of this behavior. Could "ROWNUM" cause this ?

Thank you.

Thanks Tom.

I am very sure and verified that remote materialized view has data at the time of refresh. I Will work with support to see if we are hitting any bug.

Tom,

Just to give you the further update on this.

If i switched the Materialized Views refresh in package
(networx_service_type first and then networx_clin_info),
now i am getting "0" rows count in networx_service_type.
Earlier i got "0" rows count with "networx_clin_info".

======
create or replace package body networx_mv_refresh_pkg
as
procedure networx_mv_refresh is
begin
dbms_mview.refresh('NETWORX_SERVICE_TYPE', 'C') ;
dbms_mview.refresh('NETWORX_CLIN_INFO_REF', 'C') ;
....
....
======

Do you know of any issues doing "DBMS_MVIEW.REFRESH consecutively one after another (in stored procedure, scheduled via DBMS_JOB") ?

Per my obervation and analysis, this has been consistently happening when scheuled via DBMS_JOB. If the same job was scheduled via UNIX CRON, everything looks good.

Tom,
Thank you.

I am not even using tnsnames.ora instead placing TNS entries in the CREATE DATABASE LINK itself.

----
create database link dbprod18_db_link connect to <username> identified by <password> using
'(description=(Address=(protocol=tcp)(host=dbprod18.mt.att.com)(port=1521))(connect_data=(SERVER = DEDICATED)(SERVICE_NAME=DBPROD18)))'
/
----

The same job works ok if i schedule it via Unix cron.
It worked today .. I will be monitoring for few more days to see if it is close to dbms_job or not.


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

Thanks Tom,

I have created SR with support.

Hi Tom,

I am facing a problem.
Scenario is, I have 2 databases. One is Test and another is Prod. Both have same objects but data in tables, is different. In both databases there is a mview called MV1 and offcourse its having the same definition (objects used) but with different records.
Now I want to have Prod.MV1 into Test.MV1 so what I did:
I took the export of Test.MV1 (for backup purpose) and dropped the mview from Test. Now I took another export of Prod.MV1 and imported it into Test and it worked fine and I can see the Prod records in Test database.
But the problem is , I can not refresh the MV1 with Test tables and even I can not drop this MV1 from Test and import the old Test.MV1 (which I exported earlier) back into Test database.

How can we do all this operations.

Thanks a lot for your help.

Hello Sir,
Please make me understand this:

1. If a view is created on a table and an materialized view (MV) is created on this view. Does the MV stored the data pertaining to table or only the data which the view selects?

For example :

Table T1
Col1 Col2
1 A
2 B
3 A
4 C
5 B
6 A
50 A
51 A

View V1 (WHERE Col2 = 'A')
Col1 Col2
1 A
3 A
6 A
50 A
51 A

MV MV1 (WHERE Col1 < 50)
Col1 Col2
1 A
3 A
6 A

What you would recommend creating a MV on view itself or creating MV on table?

(2) Do you recommend a frequently accessed table (hardly any DML activity) be kept in shared pool or create a MV? I know all the books/manual says its should be in pool.

Thanks

Thanks for the reply.

We are accessing the remote DB using synonyms and views from our local DB. We are trying to find out if replacing Views (created on local DB) with MVs will help us in improving the performance, because if we use MV, we wouldn¿t be connecting to remote DB everytime we access the tables which would improve the performance.

Can you please suggest?

I am running a long CREATE MATERIALIZED VIEW... AS SELECT
.... statement, but the dbconsole or em java console doesnt
show the explain plan for this statement?

Is there any option in Materialized view to refresh data monthly only and should not delete any data populated previous (Monthly) refresh from MV which got deleted from Master/Base table??

Appreciate your reply.

Thanks,
Balwant.

Thanks a lot Tom for prompt & proper answer.

-Balwant.

Hi Tom,

A small test case. Seems like a bug to me (creating a mat view with/without "parallel" clause makes a query fail when used in conjunction with CAST MULTISET).

Can you please validate and let me know if there is a known bug like this?

select * from v$version;
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for Solaris: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

--Test Case Starts
DROP TABLE t;
DROP TABLE t2;

CREATE TABLE t AS SELECT * FROM all_objects WHERE 1=2;
CREATE TABLE t2 AS SELECT * FROM all_objects WHERE 1=2;

ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (object_id);

DROP MATERIALIZED VIEW mv_t_parallel;
DROP MATERIALIZED VIEW mv_t_noparallel;

CREATE MATERIALIZED VIEW mv_t_parallel
PARALLEL
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT * FROM t;

CREATE MATERIALIZED VIEW mv_t_noparallel
NOPARALLEL
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT * FROM t;

BEGIN
EXECUTE IMMEDIATE 'DROP TYPE tmp_object_nm_lst_type';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/

CREATE OR REPLACE TYPE tmp_object_name_type AS OBJECT (
object_name VARCHAR2 (100)
);

CREATE OR REPLACE TYPE tmp_object_nm_lst_type AS TABLE OF tmp_object_name_type;

SELECT mv_t_noparallel.owner,
CAST (MULTISET (SELECT object_name
FROM t2 inner2
WHERE inner2.owner = t2.owner) AS tmp_object_nm_lst_type)
FROM t2, mv_t_noparallel;

no rows selected (Runs fine)

SELECT mv_t_parallel.owner,
CAST (MULTISET (SELECT object_name
FROM t2 inner2
WHERE inner2.owner = t2.owner) AS tmp_object_nm_lst_type)
FROM t2, mv_t_parallel;

ORA-01733: virtual column not allowed here

Both of these queries runs fine in 10G though.

Regards,
Vikram

Thanks very much for your prompt response Tom !

Is there any workaround for this bug?

Regards,
Vikram

Hello Tom,

We are thinking about the best database setup and I thought of Materialized views.

Assume following setup:

- We have 14 databases installed in 14 African countries and another 7 databases installed in 7 Middle East countries.
- We have a Win32 application in each country that connects to the database there to get data in some tables.
- The application can only connect to one database at a time.
- The size of the database is different from one country to another. Some of them are a large database and the others are small.
- The data in each database comes from raw files that are available in each country. These files are FTP¿d locally to the server, processes, and then loaded to the database using ETL loader.

We need:
- To be able to connect from the application to a central database that contains the data in all the databases in order to generate one common report that contains data from all DBs.

Possible solutions:

I thought of creating a central database in one of the countries then:

- FTP the raw files from each country to the central server then process and load them in the central database. Disadvantages: The number of files is large, so the size will be large and it will be very slow to FTP them.
- Export the required tables hourly from each database, FTP the dump file, and then import it in the central DB.
- Create database link between each database and the central database so that will be accessible from one central database. Disadvantages: If link goes down then data will not be accessible.
- Create snapshots (materialized views) on the central db so that a snapshot will be sent from each database to the central database and refreshed to get the new changes on defined intervals. This way, even if the link goes down, data will be available in the central database. Personally, I recommend this option.
- Use advanced replication. This option is expensive and not required. We don¿t need real time replication, we need data to be sent every 12 hours or so.

Can you please advice what is the best approach in terms of performance/cost? or if you suggest another approach?

thanks.

Because:

1- The raw files that should be loaded to the database are distributed in different countries. The size of these files is big so it will be impractical to FTP them over the Internet. It will be better to FTP and load them locally.

2- Some databases are already created and loading data in some countries. We just need the data in these different countried to be available in one central place.

Thanks for the follow up.
Maybe I didn't understand your question. I don't see any contradiction.
You suggested using a single database? This database should be located in one of these countries. Then we need to FTP files from other countries in order to load them into this single database. I think that FTPing large files from different countries is not a good option?

The other option is to FTP these files locally within each country (through LAN not WAN which makes it faster) and load them in a local database in each country.
Then we need to have a central database in one of the countries and update this database from the other databases in other countries using database links and materialized views. Does that make sense?

Thanks Tom,

I want to say that:

a) it is impractical to ftp them over the WAN (2Mbps or less) to a remote location in another country in order to load them in a remote database there.
b) it will be better to ftp them over the LAN (1 Gbps) to a local database in the same LAN.

We don't need online replication where data has to be synchronized in all databases in short periods.
We are talking about transferring data from different databases in different countries (maybe once or twice a day) over the WAN to one central database. The idea is that we want to have all the data that is distributed in different databases in different countries be accessed easily from one central place.
That is one suggested approach; it might be wrong. I am asking about the best approach to fulfill this requirement.

Good Day Tom,
I have some views which are generated from remedy froms. We are using 7 views and the complex query that is being used takes a lot of time. These views are being used as snapshots. In order to tune we tried to create a materialized views and index on it. The query syntax looks like: CREATE MATERIALIZED VIEW MV_ENTITLEMENT
NOLOGGING PARALLEL REFRESH FORCE START WITH SYSDATE NEXT SYSDATE+(15/1440) WITH ROWID
AS
SELECT c.c1,ac.c2
FROM
customer c,
authorizedcontact ac,
servicedetails sd,
serviceagreement sa,
coveredproductinstance cpi,
coveredservice cs,
serviceoffer so,
onlinecontentsolinkage ocsol
WHERE
c.state = 1
AND
( ac.contact_id = c.contact_id
OR
(ac.xcompanyentryid = c.xcmmasterentryid
AND ac.all_employees_authorized='Yes')
)
AND
ac.state = 0
AND
ocsol.service_offer_entry_id =
cs.offer_id
AND
so.offer_id = cs.offer_id
AND
cs.service_details_entry_id =
sd.service_details_entry_id
AND
sa.service_agreement_number =
sd.service_agreement_number
AND
( sa.service_agreement_type != 'Umbrella Agreement' )
AND
sd.service_agreement_number =
ac.service_agreement_number
AND
sd.service_details_entry_id =
cpi.service_details_entry_id(+)
AND
(
(
( ac.line_item = 'ALL'
OR
( ac.service_details_entry_id =
sd.service_details_entry_id
AND ac.cpi_id = 'ALL')
)
)
OR
(
( ac.service_details_entry_id =
sd.service_details_entry_id
AND ac.cpi_id = cpi.entry_id )
)
)
AND
sd.state = 1
AND
cpi.state(+) = 1
AND
cs.state = 1
AND
ocsol.state = 0;

--The select columns are some 15 columns.I have nt mentioned them.

Note: The total set of rows obtained in 26 lakhs.

During the time it refreshes the materialzed view, if i want to query data from this materialized view,Will it possible that our results obtained will be consistent although it refreshes in every 15 min?

Also I cretaed an index, I require one clarification here as well. When the refresh takes place then is it again indexed with the row id from scratch? How does this behave?

I would like to know the feature in detail regarding the refreshing options and some real time scenarios to deal with it.
Please suggest.

I am trying to create a materialized view on a remote table containing a column having user defined data type. It's throwing error
ORA-22804: remote operations not permitted on object tables or user-defined type columns

I am creating mview in a 10.2.0.1.0 database while the remote database is 8.1.7


Does this error mean, the mviews on the remote tables with user defined types can not be created or am I missing something?

Thanks!

Dinesh

$ oerr ora 22804
22804, 00000, "remote operations not permitted on object tables or user-defined type columns"
// *Cause:  An attempt was made to perform queries or DML operations on
//          remote object
//          tables or on remote table columns whose type is one of object,
//          REF, nested table or VARRAY.

create type emp
(id number primary key,
emp_name varchar2(100))

create type emp_tab as table of emp

create table main (seq_id number,emp_used emp_tab)
nested table emp_used store as emp_nest_store

create materialialized view log on main with primary key

alter materialized view log on main add(emp_used)

In above snippet to be executed on oracle 8.1.7 , i am getting ORA 600 error on last line.

I need to replicate a nested table to higher version of oracle by creating MV in fast refresh mode. But am not able to create a MV log on nested column to tracj changes on it.

Is there any work around for this ? or is there any patch avaialable which will solve the problem.

Thanks in advance.

Tom:

I have a requirement where i need to populate sysdate whenever materialized view refreshes ? In my situation, source side has materialized view log with ROWID not PRIMARY KEY. After one time complete refreshes, i have to do fast refresh due to source tabls volume is large.

This is my test case:

Here what i tried :

1. create a prebuilt table
2. create materialized view on prebuilt table

-----
drop materialized view DEPT_MV ;
create materialized view DEPT_MV
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH FORCE ON DEMAND
WITH ROWID
DISABLE QUERY REWRITE
AS SELECT
DEPTNO,
DNAME,
LOC,
SYSDATE AS MV_UPDATE_DATE
FROM SOURCE.DEPT@REMOTEDB
/


3. I created a trigger to update MV_UPDATE_DATE whenever inserts/updates takes place.

===========
create or replace trigger trg_mdept_mv_update_date
  before update on dept_mv
  for each row
declare
begin
  :new.MV_UPDATE_DATE := SYSDATE ;
end;
/
=========

Complete refresh works but FAST refresh produces the following errors :

17:56:53 SQL> exec dbms_mview.refresh('dept_mv', 'F')
BEGIN dbms_mview.refresh('dept_mv', 'F'); END;

*
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view "BALA"."DEPT_MV"
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 it possible to REFRESH FAST (mview uses prebuilt table + mv query uses sysdate) with source mview log with ROWID ?

I am 10.2.0 database.

Thanks for any suggestions/advice on this.

If you want only the time when MV was refreshed then better create a log table and make an entry into the same whenever MV was refreshed . You may put additional information in this table if needed.

Tom:
You are right, sysdate should not have been specified in the defining query and also changed to primary key based mview log.

I removed sysdate from the MV Query and created a trigger on mview to populate sysdate in mv_update_date column whenever insert/updates takes place in mview.
I tried fast refresh (inserts/updates/deletes), it seems to be working.

This is found in metalink: How to Create a Materialized View querying the SYSDATE. Doc ID: Note:176213.1

-----
drop materialized view DEPT_MV ;
create materialized view DEPT_MV
ON PREBUILT TABLE
REFRESH FORCE ON DEMAND
WITH PRIMRY KEY
AS SELECT
DEPTNO,
DNAME,
LOC
FROM SOURCE.DEPT@REMOTEDB
/


==========
create or replace trigger trg_mdept_mv_update_date
before update on dept_mv
for each row
declare
begin
:new.MV_UPDATE_DATE := SYSDATE ;
end;
/
=========

Tom:

Thanks for your insight and suggestions on this subject.

>> that and the entire trigger trick is very sketchy to me - only on updateable materialized views are you supposed to create them and even then the restriction is "make sure they do not fire during the refresh"

Can you please explain how Updateable materialized views will be considered here ?

>> If you do this, please comment the HECK out of it so that when people upgrade, they know to re-evaluate this from the ground up.

Do you mean after Oracle upgrade, materialized views will do complete refresh eventhought mviews logs are available on source side.

Thanks for your time.

Tom,

I am able to do fast refresh on this mview on 9.2.0.6 (9i DB) where as i am getting the error on 10.2.0.1 during fast refresh :

source database is on 9.2.0.8 which has mview log built.

Why it is working on 9.2.0.6 but not in 10.2.0.1 ?

10.2.0.1
========
SQL> exec dbms_mview.refresh('DEPT_MV','F')
BEGIN dbms_mview.refresh('DEPT_MV','F'); END;

*
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view "DSS"."DEPT_MV"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


create materialized view DEPT_MV
ON PREBUILT TABLE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS SELECT
DEPTNO,
DNAME,
LOC
, DECODE(1,1,sysdate) MV_UPDATE_DATE
FROM BALA.DEPT@GIOM_IOMWBDB
/

9.20.6
=======
14:10:21 SQL> exec dbms_mview.refresh('DEPT_MV','F')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
14:15:13 SQL> select mview_name, last_refresh_type, last_refresh_date, refresh_method from user_mviews ;

MVIEW_NAME                     LAST_REF LAST_REFRESH_DATE REFRESH_
------------------------------ -------- ----------------- --------
DEPT_MV                        FAST     01/23/08 14:15:13 FORCE


Appreicate any insights on this.

SELECT
DEPTNO,
DNAME,
LOC
, DECODE(1,1,sysdate) MV_UPDATE_DATE
FROM BALA.DEPT@GIOM_IOMWBDB

Thanks Tom for the quick response.

Apparently, as far as i tested in 9.2.0.6 and tkprof shows that mview (using sysdate as part of mv query) it did fast refresh (refer below).

So, it is a BUG in 9i then. Per documentation, it should not have worked.

In any case, if any one have used this trick in 9i and then later when they upgraded to 10g, materialized view queries using "sysdate" are going to fail upon fast refresh, Correct ?

********************************************************************************

UPDATE "BALA"."DEPT_MV" SET "DEPTNO" = :1,"DNAME" = :2,"LOC" = :3,
"MV_UPDATE_DATE" = :4
WHERE
"DEPTNO" = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 72 (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE
1 INDEX UNIQUE SCAN DEPT_MV_PK (object id 106686)

********************************************************************************

INSERT INTO "BALA"."DEPT_MV" ("DEPTNO","DNAME","LOC","MV_UPDATE_DATE")
VALUES
(:1,:2,:3,:4)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.01 0.00 0 0 0 0

Misses in library cache during parse: 1
Parsing user id: 72 (recursive depth: 1)
********************************************************************************

Tom:

Is there any data dictionary which has information about what are the different remote databases (has materialized views ) pointing to the source database (source table and materialized vew log).

source is 9.2.0.6 and target is 10.2.0.1

Thank you

Hi Tom,

We have our database on Oracle 10g.
All we are trying is to create a materilized view with and without PARALLEL option in the CREATE MATERIALIZED VIEW statement.

When we use PARALLEL option, we are getting almost 300k records missing in the materialized view compared to the number of records in the materialized view using NO PARALLEL option.

Can you tell me how is it possible? It happens only for one database. We tried to move the same table in some other database and do the same. We are getting same number of records in the materialized view.

Which option regarding paralell setting can tell me what is going wrong?


CREATE MATERIALIZED VIEW ASIM_MV1

TABLESPACE MP_FCTRY

NOCACHE

NOLOGGING

COMPRESS

--PARALLEL

BUILD IMMEDIATE

USING INDEX TABLESPACE MP_FCTRY

REFRESH FORCE ON DEMAND WITH PRIMARY KEY

AS

SELECT PSV.POD_ID, PSV.PRD_ID, MF.PJC_FCTR_VLMTRC, MF.PJC_FCTR_CSL, (CASE WHEN PSV.CNSS_ORG_IND='C' THEN 'Y' ELSE 'N' END) CNSS_ORG_IND, PSV.PSV, PVT.SNAME AS BNR_NM, (PVT.SSTCD || PVT.SCNTCD) FIPS_CD, PSV.TRD_CHN_CD, PRD.PRD_START_DT PRD_STRT_DT, PRD.PRD_END_DT FROM ( SELECT V.POD_ID, V.PRD_ID, V.PJC_FCTR PJC_FCTR_VLMTRC, C.PJC_FCTR PJC_FCTR_CSL FROM MP_FCTRY.LGCY_MRKT_FCTR_VLMTRC V, MP_FCTRY.LGCY_MRKT_FCTR_CSL C WHERE V.POD_ID = C.POD_ID(+) AND V.PRD_ID = C.PRD_ID(+) AND V.RPTING_STS = C.RPTING_STS(+) AND V.RPTING_STS = 'Y' ) MF, MP_FCTRY.MRKT_PSV PSV, MP_FCTRY.POD_CHR_VL_PVT PVT, CKA.PERIOD PRD WHERE PSV.POD_ID = MF.POD_ID (+) AND PSV.PRD_ID = MF.PRD_ID (+) AND PSV.POD_ID = PVT.POD_ID (+) AND PSV.PRD_ID = PRD.PRD_ID AND EXISTS (SELECT V1.PRD_ID FROM MP_FCTRY.LGCY_MRKT_FCTR_VLMTRC V1 WHERE V1.PRD_ID = PSV.PRD_ID);


Thanks for your help.
Asim

does anyone know what this does? I used dbms_advisor.tune_mview.

My question is: why are two materialized view created? And what does DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE do?

Here is the code example. The goal of the MView is to have the latest price for a security from the pricing table. The gma_sec_price table is partitioned by price_dt

CREATE TABLE GMA_SEC_PRICE
(
SEC_ID NUMBER(10),
PRICE_DT DATE,
CURRENCY_CD VARCHAR2(3 BYTE),
PRICE NUMBER
);

Then I run:

DECLARE
nm varchar2(30) := 'GMA_SEC_PRICE_MV2';
qry varchar2(2000) := 'CREATE MATERIALIZED VIEW GMA_SEC_PRICE_MV2 REFRESH FAST ENABLE QUERY REWRITE AS SELECT p1.sec_id, p1.currency_cd, p1.price_dt, p1.price FROM gma_sec_price p1 WHERE p1.price_dt = (SELECT MAX (p2.price_dt) FROM gma_sec_price p2 WHERE p2.sec_id = p1.sec_id AND p2.currency_cd = p1.currency_cd GROUP BY p2.sec_id, p2.currency_cd)';

BEGIN

dbms_advisor.tune_mview(nm, qry);

END;

select * from user_tune_mview;

And these are the results:

CREATE MATERIALIZED VIEW LOG ON "GMA_SEC_PRICE" WITH ROWID

ALTER MATERIALIZED VIEW LOG FORCE ON "GMA_SEC_PRICE" ADD ROWID

CREATE MATERIALIZED VIEW gma_sec_price_mv2$sub1
REFRESH FAST
WITH ROWID ON COMMIT
ENABLE QUERY REWRITE AS
SELECT
gma_sec_price.ROWID c1,
"GMA_SEC_PRICE"."CURRENCY_CD" m1,
"GMA_SEC_PRICE"."PRICE" m2,
"GMA_SEC_PRICE"."PRICE_DT" m3,
"GMA_SEC_PRICE"."SEC_ID" m4
FROM gma_sec_price



CREATE MATERIALIZED VIEW GMA_SEC_PRICE_MV2
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE AS
SELECT "SYS_ALIAS_2"."SEC_ID" "SEC_ID","SYS_ALIAS_2"."CURRENCY_CD" "CURRENCY_CD","SYS_ALIAS_2"."PRICE_DT" "PRICE_DT","SYS_ALIAS_2"."PRICE" "PRICE"
FROM "GMA_SEC_PRICE" "SYS_ALIAS_2","GMA_SEC_PRICE_MV2$SUB1" "GMA_SEC_PRICE_MV2$SUB1"
WHERE
"SYS_ALIAS_2".ROWID="GMA_SEC_PRICE_MV2$SUB1"."C1"
AND "SYS_ALIAS_2"."PRICE_DT"= (SELECT MAX("GMA_SEC_PRICE_MV2$SUB1"."M3") FROM "GMA_SEC_PRICE_MV2$SUB1" "GMA_SEC_PRICE_MV2$SUB1" WHERE "GMA_SEC_PRICE_MV2$SUB1"."M4"="SYS_ALIAS_2"."SEC_ID" AND "GMA_SEC_PRICE_MV2$SUB1"."M1"="SYS_ALIAS_2"."CURRENCY_CD" GROUP BY "GMA_SEC_PRICE_MV2$SUB1"."M4","GMA_SEC_PRICE_MV2$SUB1"."M1")

DROP MATERIALIZED VIEW GMA_SEC_PRICE_MV2

DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('GMA_SEC_PRICE_MV2$RWEQ','SELECT p1.sec_id, p1.currency_cd, p1.price_dt, p1.price FROM gma_sec_price p1 WHERE p1.price_dt = (SELECT MAX (p2.price_dt) FROM gma_sec_price p2 WHERE p2.sec_id = p1.sec_id AND p2.currency_cd = p1.currency_cd GROUP BY p2.sec_id, p2.currency_cd)','SELECT "SYS_ALIAS_2"."SEC_ID" "SEC_ID","SYS_ALIAS_2"."CURRENCY_CD" "CURRENCY_CD","SYS_ALIAS_2"."PRICE_DT" "PRICE_DT","SYS_ALIAS_2"."PRICE" "PRICE" FROM "GMA_SEC_PRICE" "SYS_ALIAS_2","GMA_SEC_PRICE_MV2$SUB1" "GMA_SEC_PRICE_MV2$SUB1" WHERE "SYS_ALIAS_2".ROWID="GMA_SEC_PRICE_MV2$SUB1"."C1" AND "SYS_ALIAS_2"."PRICE_DT"= (SELECT MAX("GMA_SEC_PRICE_MV2$SUB1"."M3") FROM "GMA_SEC_PRICE_MV2$SUB1" "GMA_SEC_PRICE_MV2$SUB1" WHERE "GMA_SEC_PRICE_MV2$SUB1"."M4"="SYS_ALIAS_2"."SEC_ID" AND "GMA_SEC_PRICE_MV2$SUB1"."M1"="SYS_ALIAS_2"."CURRENCY_CD" GROUP BY "GMA_SEC_PRICE_MV2$SUB1"."M4","GMA_SEC_PRICE_MV2$SUB1"."M1")',-1244708196)

DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('GMA_SEC_PRICE_MV2$RWEQ')

Hi Tom.
I am having many performance problems because the application code is very bad. There are huge SELECTS that have a very poor performance. My DBA is telling me to use Materialized Views in this cases. Is this correct? What is the price that I have to pay to use MV this way?

Thanks.

Ok Tom, let me explain my doubts.

MV can be good for speed the queries, but it can be bad for DMLs, because there is an overhead of updating MV logs and doing a refresh of MV. My fear is that MV could become a "panacea". The queries that I am trying to fix don't have aggregations/summaries , just joins. But I am reading about MV and it can be used for queries that have only joins. I just need to know how to measure when is recommended to use MV and when is not recommended. Something like that, if I have many DMLs, dont use MV with only joins. I really dont know if there is an answer for my question. Thanks.

Dear Tom,
I have read several articles about Materialized view logs, but they do not clear the concept of creating Materialized view logs.

For example
===========
1. CREATE MATERIALIZED VIEW LOG ON scott.emp
WITH OBJECT ID;

2. CREATE MATERIALIZED VIEW LOG ON scott.emp
WITH OBJECT ID, PRIMARY KEY;

Now i don't know which command should i use to create Materialized view log and why?

Can you please explain concept of Materailized view logs.

Regards

Dear Tom

Thanks for your reply. Can you please give me an example of dbms_mview.explain_mview.
Secondly please give me the exact link, where i can find the logic of creating Materialized View Logs in brief.

Regards

Hi Tom

1.   I used the following example to check DBMS_MVIEW.EXPLAIN_MVIEW as you said.

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('select hp.party_number,hp.party_name,hca.creation_date,hca.creation_date from hz_parties hp,hz_cust_accounts hca where hp.party_id=hca.party_id');

2. I quried mv_capabilities_table and got the following output.

SQL> SELECT capability_name, possible, related_text, msgtxt
  2  FROM mv_capabilities_table;

OUTPUT
=======

CAPABILITY_NAME                POSSIBLE RELATED_TEXT                                                                     MSGTXT
------------------------------ -------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
PCT                            N                                                                                         
REFRESH_COMPLETE               Y                                                                                         
REFRESH_FAST                   N                                                                                         
REWRITE                        Y                                                                                         
PCT_TABLE                      N        HZ_PARTIES                                                                       relation is not a partitioned table
PCT_TABLE                      N        HZ_CUST_ACCOUNTS                                                                 relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT      N        HCA                                                                              the SELECT list does not have the rowids of all the detail tables
REFRESH_FAST_AFTER_INSERT      N        AR.HZ_PARTIES                                                                    the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT      N        AR.HZ_CUST_ACCOUNTS                                                              the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML  N                                                                                         see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     N                                                                                         see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N                                                                                         PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH        Y                                                                                         
REWRITE_PARTIAL_TEXT_MATCH     Y                                                                                         
REWRITE_GENERAL                Y                                                                                         
REWRITE_PCT                    N                                                                                         general rewrite is not possible or PCT is not possible on any of the detail tabl
PCT_TABLE_REWRITE              N        HZ_PARTIES                                                                       relation is not a partitioned table
PCT_TABLE_REWRITE              N        HZ_CUST_ACCOUNTS                                                                 relation is not a partitioned table

18 rows selected


Now my question is, how would i know that what type of Materialized View log i have to create? 


Regards

Dear Tom
1.   I am not aware of capabilities which i quried in my previous example. I want to crate Materialized view of that query which will refresh fast on commit, which is as follows:
"select 
hp.party_number,hp.party_name,hca.creation_date,hca.creation_date from hz_parties 
hp,hz_cust_accounts hca where hp.party_id=hca.party_id"

2.   I Used the package dbms_mview.explain_mview as follows to know the feature of materialized view requiring the columns to be added to the log. 
 
SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('select 
hp.party_number,hp.party_name,hca.creation_date,hca.creation_date from hz_parties 
hp,hz_cust_accounts hca where hp.party_id=hca.party_id');

I then quried mv_capabilities_table to know the columns to be added to the log, but couldn't understand the result.


Now i don't know how should i view this data to create materilized view logs before creating materialiezed view.

Dear Tom
     Yes these are tables used by Oracle Applications. We are customizing some reports and therefore need to create Materialized view as well which could refresh fast on commit. Here is again the steps with output, which i executed earlier regarding my question.

1.   This is the main query of which i want to create a materialized view. 

"select hp.party_number,hp.party_name,hca.creation_date,hca.creation_date from hz_parties hp,hz_cust_accounts hca where hp.party_id=hca.party_id;"

some of the rows regarding the output of this query are as follows.

PARTY PARTY_NAME                     CREATION_DATE CREATION_DATE
----- ------------------------------ ------------- -------------
1202  SANAULLAH MEDICAL STORE        4/16/2008 1:4 4/16/2008 1:4
1203  SANGHAR MEDICAL STORE          4/16/2008 1:4 4/16/2008 1:4
1204  SANJEY MEDICAL STORE           4/16/2008 1:4 4/16/2008 1:4
1205  SAQIB MEDICAL STORE            4/16/2008 1:4 4/16/2008 1:4
1206  SARFARAZ MEDICAL STORE         4/16/2008 1:4 4/16/2008 1:4
1207  SARMAD MEDICAL STORE           4/16/2008 1:4 4/16/2008 1:4
1208  SARUNG MEDICAL STORE           4/16/2008 1:4 4/16/2008 1:4
1209  SARWAR MEDICAL STORE           4/16/2008 1:4 4/16/2008 1:4
1210  SARWAR MEDICAL STORE           4/16/2008 1:4 4/16/2008 1:4
1211  SARWARI MEDICAL STORE          4/16/2008 1:4 4/16/2008 1:4
1212  SATTAR MEDICAL STORE           4/16/2008 1:4 4/16/2008 1:4
1213  AL SAUDIA MEDICAL STORE        4/16/2008 1:4 4/16/2008 1:4
1214  AL SHIFA MEDICAL STORE         4/16/2008 1:4 4/16/2008 1:4
1215  UNEEQUE ENTERPRISES            4/16/2008 1:4 4/16/2008 1:4
1216  SHABBIR MEDICAL STORE          4/16/2008 1:4 4/16/2008 1:4
1217  AL SHIFA MEDICAL STORE         4/16/2008 1:4 4/16/2008 1:4
1218  NAFEES MEDICAL STORE           4/16/2008 1:4 4/16/2008 1:4
1219  SHAH MEDICAL STORE             4/16/2008 1:4 4/16/2008 1:4
1220  SHAH MEDICOS                   4/16/2008 1:4 4/16/2008 1:4

2.   I Used the package dbms_mview.explain_mview as follows to know the feature of materialized 
view requiring the columns to be added to the log. 
 
SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('select 
hp.party_number,hp.party_name,hca.creation_date,hca.creation_date from hz_parties 
hp,hz_cust_accounts hca where hp.party_id=hca.party_id');

3.    I then quried mv_capabilities_table to know the columns to be added to the log, but couldn't 
understand the result. The query and output of this table is as follows.

Query
======
SQL> SELECT capability_name, possible, related_text, msgtxt
  2  FROM mv_capabilities_table;

Output
======

CAPABILITY_NAME                POSSIBLE RELATED_TEXT                   MSGTXT
------------------------------ -------- ------------------------------ --------------------------------------------------------------------------------
PCT                            N                                       
REFRESH_COMPLETE               Y                                       
REFRESH_FAST                   N                                       
REWRITE                        Y                                       
PCT_TABLE                      N        HZ_PARTIES                     relation is not a partitioned table
PCT_TABLE                      N        HZ_CUST_ACCOUNTS               relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT      N        HCA                            the SELECT list does not have the rowids of all the detail tables
REFRESH_FAST_AFTER_INSERT      N        AR.HZ_PARTIES                  the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT      N        AR.HZ_CUST_ACCOUNTS            the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML  N                                       see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     N                                       see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N                                       PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH        Y                                       
REWRITE_PARTIAL_TEXT_MATCH     Y                                       
REWRITE_GENERAL                Y                                       
REWRITE_PCT                    N                                       general rewrite is not possible or PCT is not possible on any of the detail tabl
PCT_TABLE_REWRITE              N        HZ_PARTIES                     relation is not a partitioned table
PCT_TABLE_REWRITE              N        HZ_CUST_ACCOUNTS               relation is not a partitioned table

18 rows selected


Now i don't know how should i view this data to create materilized view logs before creating 
materialiezed view.

ops$tkyte%ORA10GR2> create or replace function explain_mview( p_mv in varchar2 )
  2  return sys.ExplainMVArrayType
  3  PIPELINED
  4  is
  5      pragma autonomous_transaction;
  6      l_data sys.explainMVArrayType;
  7  begin
  8      dbms_mview.explain_mview( mv => p_mv, msg_array => l_data );
  9      commit;
 10      for i in 1 .. l_data.count
 11      loop
 12          pipe row( l_data(i) );
 13      end loop;
 14      return;
 15  end;
 16  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table hz_parties( party_id number primary key, party_number number, party_name varchar2(20) );

Table created.

ops$tkyte%ORA10GR2> create table hz_cust_accounts( whatever number primary key, party_id number references hz_parties, creation_date date );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select capability_name,
  2         possible,
  3         case when related_text is not null then '('||related_text||') ' end || msgtxt txt
  4   from TABLE( explain_mview('select hp.party_number,hp.party_name,hca.creation_date,hca.creation_date
  5                                from hz_parties hp,hz_cust_accounts hca
  6                               where hp.party_id=hca.party_id') )
  7   where capability_name like 'REFRESH%';

CAPABILITY_NAME                P TXT
------------------------------ - ----------------------------------------
REFRESH_COMPLETE               T
REFRESH_FAST                   F
REFRESH_FAST_AFTER_INSERT      F (HCA) the SELECT list does not have the
                                 rowids of all the detail tables

REFRESH_FAST_AFTER_INSERT      F (OPS$TKYTE.HZ_PARTIES) the detail table
                                 does not have a materialized view log

REFRESH_FAST_AFTER_INSERT      F (OPS$TKYTE.HZ_CUST_ACCOUNTS) the detail
                                 table does not have a materialized view
                                 log

REFRESH_FAST_AFTER_ONETAB_DML  F see the reason why REFRESH_FAST_AFTER_IN
                                 SERT is disabled

REFRESH_FAST_AFTER_ANY_DML     F see the reason why REFRESH_FAST_AFTER_ON
                                 ETAB_DML is disabled

REFRESH_FAST_PCT               F PCT is not possible on any of the detail
                                  tables in the materialized view


8 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view log on hz_parties;

Materialized view log created.

ops$tkyte%ORA10GR2> create materialized view log on hz_cust_accounts;

Materialized view log created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select capability_name,
  2         possible,
  3         case when related_text is not null then '('||related_text||') ' end || msgtxt txt
  4   from TABLE( explain_mview('select hp.party_number,hp.party_name,hca.creation_date,hca.creation_date
  5                                from hz_parties hp,hz_cust_accounts hca
  6                               where hp.party_id=hca.party_id') )
  7   where capability_name like 'REFRESH%';

CAPABILITY_NAME                P TXT
------------------------------ - ----------------------------------------
REFRESH_COMPLETE               T
REFRESH_FAST                   F
REFRESH_FAST_AFTER_INSERT      F (HCA) the SELECT list does not have the
                                 rowids of all the detail tables

REFRESH_FAST_AFTER_INSERT      F (OPS$TKYTE.HZ_CUST_ACCOUNTS) mv log must
                                  have ROWID

REFRESH_FAST_AFTER_INSERT      F (OPS$TKYTE.HZ_PARTIES) mv log must have
                                 ROWID

REFRESH_FAST_AFTER_ONETAB_DML  F see the reason why REFRESH_FAST_AFTER_IN
                                 SERT is disabled

REFRESH_FAST_AFTER_ANY_DML     F see the reason why REFRESH_FAST_AFTER_ON
                                 ETAB_DML is disabled

REFRESH_FAST_PCT               F PCT is not possible on any of the detail
                                  tables in the materialized view


8 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter materialized view log on hz_parties add rowid;

Materialized view log altered.

ops$tkyte%ORA10GR2> alter materialized view log on hz_cust_accounts add rowid;

Materialized view log altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select capability_name,
  2         possible,
  3         case when related_text is not null then '('||related_text||') ' end || msgtxt txt
  4   from TABLE( explain_mview('select hp.party_number,hp.party_name,hca.creation_date,hca.creation_date
  5                                from hz_parties hp,hz_cust_accounts hca
  6                               where hp.party_id=hca.party_id') )
  7   where capability_name like 'REFRESH%';

CAPABILITY_NAME                P TXT
------------------------------ - ----------------------------------------
REFRESH_COMPLETE               T
REFRESH_FAST                   F
REFRESH_FAST_AFTER_INSERT      F (HCA) the SELECT list does not have the
                                 rowids of all the detail tables

REFRESH_FAST_AFTER_ONETAB_DML  F see the reason why REFRESH_FAST_AFTER_IN
                                 SERT is disabled

REFRESH_FAST_AFTER_ANY_DML     F see the reason why REFRESH_FAST_AFTER_ON
                                 ETAB_DML is disabled

REFRESH_FAST_PCT               F PCT is not possible on any of the detail
                                  tables in the materialized view


6 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select capability_name,
  2         possible,
  3         case when related_text is not null then '('||related_text||') ' end || msgtxt txt
  4   from TABLE( explain_mview('select hp.party_number,hp.party_name,hca.creation_date,hca.creation_date ,
  5                                     hp.rowid hp_rowid, hca.rowid hca_rowid
  6                                from hz_parties hp,hz_cust_accounts hca
  7                               where hp.party_id=hca.party_id') )
  8   where capability_name like 'REFRESH%';

CAPABILITY_NAME                P TXT
------------------------------ - ----------------------------------------
REFRESH_COMPLETE               T
REFRESH_FAST                   T
REFRESH_FAST_AFTER_INSERT      T
REFRESH_FAST_AFTER_ONETAB_DML  T
REFRESH_FAST_AFTER_ANY_DML     T
REFRESH_FAST_PCT               F PCT is not possible on any of the detail
                                  tables in the materialized view


6 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view mv
  2  refresh fast on commit
  3  as
  4  select hp.party_number,hp.party_name,hca.creation_date cd1,hca.creation_date cd2,
  5                                     hp.rowid hp_rowid, hca.rowid hca_rowid
  6                                from hz_parties hp,hz_cust_accounts hca
  7                               where hp.party_id=hca.party_id
  8  /

Materialized view created.

Dear Tom
    Thanks for your detailed reply. In the given example you executed the following steps.

1) There were no materialized logs, and after executing the following query you create the materialized view logs on those tables.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select capability_name,
  2         possible,
  3         case when related_text is not null then '('||related_text||') ' end || msgtxt txt
  4   from TABLE( explain_mview('select 
hp.party_number,hp.party_name,hca.creation_date,hca.creation_date
  5                                from hz_parties hp,hz_cust_accounts hca
  6                               where hp.party_id=hca.party_id') )
  7   where capability_name like 'REFRESH%';


2)    Then you altered materialized views and added rowids by executing the following command.

ops$tkyte%ORA10GR2> alter materialized view log on hz_parties add rowid;

Materialized view log altered.

ops$tkyte%ORA10GR2> alter materialized view log on hz_cust_accounts add rowid;

Materialized view log altered.


3)  My question is, is this enough to just add rowid or I will have to use column name as well in scenarios other than this. If I have to add column names as well then how would I know which columns I¿ll have to use.

Regards
Nadir 

Dear Tom

I have understood the goal of the example you gave me.

1. But please tell that how would i know, which columns i will have to use with rowid clause.

2. My second question is, materialized views are also created using primary key. Here my question is, how would i know when should i create materialized view based upon rowid or on primary key.

Please tell me these things as i don't know already.

regards

Nadir Pervez

Dear Tom

I studied the chapter 8 you mentioned and found the three points which are as follows.


1. For aggregate materialized views, it must contain every column in the table referenced in the materialized view, the INCLUDING NEW VALUES clause and the SEQUENCE clause.

Here i want to ask that which columns i have to mention, that is, all columns of the base table or only those columns which are used in materialized view.

Where as in this chapter the example states something else which is as follows.

Example 8-3 Example 3: Creating a Materialized View

CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW sum_sales
PARALLEL
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT s.prod_id, s.time_id, COUNT(*) AS count_grp,
SUM(s.amount_sold) AS sum_dollar_sales,
COUNT(s.amount_sold) AS count_dollar_sales,
SUM(s.quantity_sold) AS sum_quantity_sales,
COUNT(s.quantity_sold) AS count_quantity_sales
FROM sales s
GROUP BY s.prod_id, s.time_id;

In this example they used columns (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) of the base table to create Materialized view log.
Where as while creating materialized view they only used columns (s.prod_id, s.time_id, COUNT(*) AS count_grp,
SUM(s.amount_sold) AS sum_dollar_sales,
COUNT(s.amount_sold) AS count_dollar_sales,
SUM(s.quantity_sold) AS sum_quantity_sales,
COUNT(s.quantity_sold) AS count_quantity_sales
)

This example and the above statment is confusing me.





2. The SEQUENCE column is required in the materialized view log to support fast refresh with a combination of INSERT, UPDATE, or DELETE statements on multiple tables.

Can you please explain the above statement as the following materialized view is created without SEQUENCE which refreshes fast.

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW detail_sales_mv
PARALLEL BUILD IMMEDIATE
REFRESH FAST AS
SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id
FROM sales s, times t, customers c
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);


3. Materialize views are also based upon primary key. When should i create primary key base materialized views

Hi Tom,

I am getting the following error when i try to create the following MV. The prebuilt table has been created using the sql.

Would you please have a look and advise? Many thanks.

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

create table test_service
 (
 SERVICE_SUB_MODE  VARCHAR2(20),
 DATE_TIME_START date,
 DATE_TIME_end date);
 
 
 insert into test_service values ('No Note Mode',to_date('29/07/2008 17:00','dd/mm/yyyy 
hh24:mi'),
to_date('29/07/2008 17:30','dd/mm/yyyy hh24:mi'));

 
  
 insert into test_service values ('No 10 cents Mode',to_date('29/07/2008 
17:10','dd/mm/yyyy hh24:mi'),
to_date('29/07/2008 17:20','dd/mm/yyyy hh24:mi'));

 
insert into test_service values ('No coin Mode',to_date('29/07/2008 17:25','dd/mm/yyyy 
hh24:mi'),
to_date('29/07/2008 17:30','dd/mm/yyyy hh24:mi'));


insert into test_service values ('No 10 cents Mode',to_date('29/07/2008 
17:40','dd/mm/yyyy hh24:mi'),
to_date('29/07/2008 18:30','dd/mm/yyyy hh24:mi'));

insert into test_service values ('No Note Mode',to_date('29/07/2008 18:10','dd/mm/yyyy 
hh24:mi'),
to_date('29/07/2008 18:20','dd/mm/yyyy hh24:mi'));



commit;<code>


<code>
SQL> select t.*,
  2  numtodsinterval((date_time_end - date_time_start),'DAY') inteval_dif
  3  from test_service t
  4  /

SERVICE_SUB_MODE     DATE_TIME_START     DATE_TIME_END       INTEVAL_DIF
-------------------- ------------------- ------------------- ---------------------------------------
No Note Mode         29-07-2008:05:00:00 29-07-2008:05:30:00 +000000000 00:29:59.999999999
No 10 cents Mode     29-07-2008:05:10:00 29-07-2008:05:20:00 +000000000 00:10:00.000000000
No coin Mode         29-07-2008:05:25:00 29-07-2008:05:30:00 +000000000 00:05:00.000000000
No 10 cents Mode     29-07-2008:05:40:00 29-07-2008:06:30:00 +000000000 00:49:59.999999999
No Note Mode         29-07-2008:06:10:00 29-07-2008:06:20:00 +000000000 00:10:00.000000000

create table t_pb as
select 
 SERVICE_SUB_MODE, numtodsinterval((sum(date_time_end - date_time_start)),'DAY') interval_dif
from test_service
group by service_sub_mode
/

SQL> select * from t_pb ;

SERVICE_SUB_MODE     INTERVAL_DIF
-------------------- ---------------------------------------------------------------------------
No 10 cents Mode     +000000000 00:59:59.999999999
No Note Mode         +000000000 00:39:59.999999999
No coin Mode         +000000000 00:05:00.000000000

CREATE  MATERIALIZED VIEW t_pb
ON PREBUILT TABLE 
WITH REDUCED PRECISION 
REFRESH ON DEMAND 
with primary key 
ENABLE QUERY REWRITE
 as
select 
 SERVICE_SUB_MODE, numtodsinterval((sum(date_time_end - date_time_start)),'DAY') interval_dif
from test_service
group by service_sub_mode
/

Hi Tom,

Do I have to define the following parameters for MV's in 10gR2 for data replication:

pctfree,
pctused,
storage,
initrans,
maxtrans
pctincrease
minextents
maxextents
freelists
freelist
groups

CREATE MATERIALIZED VIEW TEST TABLESPACE TSP1D
BUILD IMMEDIATE USING INDEX TABLESPACE TSP1X REFRESH FAST ON DEMAND
AS
SELECT
X,Y
FROM T
/

Thanks

Hello Tom, I tried to search in asktom.com Advanced Search with parameter First asked from 19-dec-2008 to 31-dec-2008 (ofcourse this is wrong and i should get no data found) but i got error saying report error:
ORA-00600: internal error code, arguments: [qkeIsExprReferenced1], [], [], [], [], [], [], []

Out of my curiosity i tried many such wrong parameters and got the same.

ORA-00600 seems to be some internal error and trace file will be written on dump dest dir.

date of query is 18-dec-2008
This is just to bring to your knowledge.

br
atul