Recursive queries for materialized view operations – 2

Not long ago I’ve met again the problem of the strange execution plan for CREATE MV recursive query. It looks like some query transformation were not executed for the plan building process.

Finally, I’ve found very important MOS note which explains this strange behaviour – Create Materialized View or Complete Refresh Taking Longer Than CTAS or Insert-Select [ID 763718.1]:

«Bug.6840494 CBQT DISALLOWED FOR CTAS UNDER CREATE MATERIALIZED VIEW / IAS UNDER REFRESH:
which was closed as not feasible to fix and, as only workaround, to force optimizer to pick same plan for non-refresh operations.
…
This is caused by CBQT being disabled for mview operations»

i.e. Cost Based Query Transformation (CBQT) are not available for Create Materialized View / Complete Refresh operations in Oracle 10.2.-11.2

I’ve tried to fix the optimal execution plan for the Create Materialized View recursive sql – unsuccessfully, despite of dba_outlines.used column became “USED” just after CREATE MV execution

SQL> select name, used, version from dba_outlines
2  /

NAME                           USED   VERSION
------------------------------ ------ ------------------------------------
TEST_QUERY2                    USED   11.1.0.7.0

Recursive queries for materialized view operations with database link

For operations Create Materialized View or Complete Refresh for mviews with remote tables (over database links), the execution plan can significantly differ from the base query plan, as well as from Create Table As Select (CTAS) plan. Some tests:

SQL> create table t as select * from dba_tables;

Table created.

SQL> exec dbms_stats.gather_table_stats('SYSTEM','T');

PL/SQL procedure successfully completed.

SQL> create materialized view t_mv as select t1.* from t@db_link tr, t t1,t t2 where tr.table_name = t1.table_name and t2.table_name = t1.table_name;

In Oracle 11.2 there can be found two open active cursors for my session in v$open_cursor, and one of cursors  is marked as OPEN-RECURSIVE (new column CURSOR_TYPE in v$open_cursor 11.2 version), with the strange value for LAST_SQL_EXEC_TIME column. In Oracle 10.2 and 11.1 there isn’t the field CURSOR_TYPE in v$open_cursor yet, and recursive cursor can be easily found by v$sql.sql_text

SID USER_NAME ADDRESS  HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIME SQL_EXEC_ID CURSOR_TYPE
--- --------- -------- ---------- ------------- ------------------------------------------------------------ -------------------- ----------- --------------
67  SYSTEM    233CD018 2317899508 3vcya0y52hprn CREATE TABLE "SYSTEM"."T_MV"  AS select t1.* from t@db_link  01-JAN-70              16777217  OPEN-RECURSIVE
67  SYSTEM    2639FCDC 1245822999 2dvw4u1543h0r create materialized view t_mv as select t1.* from t@db_link                         16777217  OPEN