Oracle mechanics

Igor Usoltsev's Oracle blog

Archive for the ‘bugs’ Category

Recursive queries for materialized view operations – 2

Posted by Игорь Усольцев on July 24, 2011

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
Advertisements

Posted in 10.2, 11.2, bugs, MV | Leave a Comment »