Oracle mechanics

Igor Usoltsev's Oracle blog

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: