Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to supress hint being used in an insert query - plss help

Sujana A-OracleDec 18 2014 — edited Dec 18 2014

I just came to know that when we create a materialized view it inturn creates a table too and this newly created object can be found in both user_mviews and also user_tables.
Now starts my question. We have received a report of the long running SQLs and in it is the below query:

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO MVIEW_TEST SELECT * FROM TABLE_TEST;

There is no code/source in my entire DB which has this stmt. But creation of the mview - MVIEW_TEST has the script as below:;

CREATE MATERIALIZED VIEW MVIEW_TEST
REFRESH FORCE
ON DEMAND
AS SELECT * FROM TABLE_TEST;

Does this mean when the mview - MVIEW_TEST is getting refreshed it performs an insert into the underlying MVIEW_TEST table too?
The biggest issue for me here is the insert stmt uses BYPASS_RECURSIVE_CHECK hint which im not sure why it is being used. I want to get that changed to /*+ APPEND */. But how do i do that since i didnt trigger this INSERT manually.
Is there anyway to stop creating the table when my mview is created? Or is there a way to stop letting use this hint - BYPASS_RECURSIVE_CHECK while inserting into the table MVIEW_TEST.

I want to use the hint - APPEND instead.

This post has been answered by Sven W. on Dec 18 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 15 2015
Added on Dec 18 2014
9 comments
3,267 views