Oracle mechanics

Igor Usoltsev's Oracle blog

  • Categories

  • Advertisements

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]:

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

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: