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.