| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re:RE: RE: Suggestions on MV Implementation !!!!!!!
Mahud,
I don't think the database appreciates the 'distinct' clause in the select, or the order by.
Dick Goulet
____________________Reply Separator____________________ Author: "Reddy; Madhusudana" <Madhusudana.Reddy_at_bestbuy.com> Date: 6/24/2002 8:04 AM
DG,
I am getting the following error :
ERROR at line 13:
ORA-12015: cannot create a fast refresh snapshot from a complex query
I have executed the following
> > CREATE MATERIALIZED VIEW GENRELOB_TEST
> > NOLOGGING
> > BUILD IMMEDIATE
> > REFRESH COMPLETE ON DEMAND
> > DISABLE QUERY REWRITE
> > AS SELECT DISTINCT
> > '1' AS CLIP,
> > LOB.LOB_ID,
> > LOB.LOB_CD,
> > GENRE.GENRE_ID,
> > GENRE.GENRE_DESC,
> > GENRE.GENRE_DESC AS INSTANCENAME
> > FROM
> > GENRE,
> > GENRE_LOB_XREF,
> > LOB,
> > GENRE_PRODUCT_XREF
> > WHERE
> > GENRE.GENRE_ID = GENRE_LOB_XREF.GENRE_ID AND
> > GENRE_LOB_XREF.LOB_ID = LOB.LOB_ID AND
> > GENRE_PRODUCT_XREF.genre_id = GENRE.genre_id AND
> > GENRE.DSPLY_IND = 'Y'
> > ORDER BY
> > LOB_CD,
> > GENRE_DESC
> > ;
> >
I have also created the MV Logs on the base tables as follows:
create materialized view log on bbyent.genre
with
rowid(GENRE_ID,GENRE_DESC,PARENT_GENRE_ID,REC_CREATE_TS,REC_CREATE_USER_ID,R
EC_UPD_TS,REC_UPD_USER_ID,MIGRATION_ID,DSPLY_SEQ,
DSPLY_IND)
including new values
/
create materialized view log on bbyent.lob
with
rowid(LOB_ID,LOB_CD,LOB_DESC,REC_CREATE_TS,REC_CREATE_USER_ID,REC_UPD_TS,REC
_UPD_USER_ID,MIGRATION_ID)
including new values
/
create materialized view log on bbyent.genre_product_xref
with
rowid(GENRE_ID,PRODUCT_ID,ASSOC_PREF_NBR,REC_CREATE_TS,REC_CREATE_USER_ID,RE
C_UPD_TS,REC_UPD_USER_ID,MIGRATION_ID)
including new values
/
create materialized view log on bbyent.genre_lob_xref
with
rowid(LOB_ID,GENRE_ID,REC_CREATE_TS,REC_CREATE_USER_ID,REC_UPD_TS,REC_UPD_US
ER_ID,MIGRATION_ID)
including new values
/
To FYI : my DB is running on 8.1.7.2 ( Unable to drop an existing MV , got end of communication error )
Seems there are some limitations fro Fast Refresh...
Thanks,
Madhu
-----Original Message-----
Sent: Monday, June 24, 2002 9:13 AM
To: Multiple recipients of list ORACLE-L
Madhu,
What are the problems?
Dick Goulet
____________________Reply Separator____________________ Author: "Reddy; Madhusudana" <Madhusudana.Reddy_at_bestbuy.com> Date: 6/23/2002 9:03 PM
Jack , DG and ALL,
I Have problem in creating the Fast Refresh MVs, from the existing code ,
which i can not change in present situation.
So I am still looking for another option to minimize the down time( blank
web pages at the time of MV refresh ) , even by using the COMPLETE refresh .
For me space is not a problem ..
I would like to hear some more ideas to eliminate the down time , with the existing MVs ( Complete Refresh )
Hope i hear you all soon ,
Thanks again
Madhu
-----Original Message-----
Sent: Friday, June 21, 2002 5:33 PM
To: Multiple recipients of list ORACLE-L
Thanks Mahu. Do you get the feeling that I might have done that a few times? ;)
Snapshots and materialized views are the same thing. I guess I might start calling them materialized views in the next version or two, but it is so hard to give up old habits.
Another thing you might need to know - you can't easily change a job in the Oracle job queue unless you are the owner, which means that you can't do it as DBA. There is a package called dbms_ijob that will allow you to change jobs as a dba even if you don't own them. There is usually no public synonym for this package, so you will have to refer to it as sys.dbms_ijob. I believe that this package is not officially supported, so you might not find a lot of documentation on it, but I have used it for over a year without any problems.
To turn off a snapshot refresh, use the
sys.dbms_ijob.broken function.
*BE ADVISED* If you unbreak a job in the Oracle job queue, it will try to run immediately. This includes snapshot jobs. If you unbreak a *complete* snapshot refresh job, the first thing it does is truncate the target table. Unbreak a complete snapshot refresh job in the middle of the day and viola, the users suddenly have no data. Be careful.
hth,
jack
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: jack_silvey_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reddy, Madhusudana INET: Madhusudana.Reddy_at_bestbuy.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: dgoulet_at_vicr.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reddy, Madhusudana INET: Madhusudana.Reddy_at_bestbuy.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: dgoulet_at_vicr.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Jun 24 2002 - 12:38:21 CDT
 |
 |