-
Query Rewrite Enabled with Materialized View
Dear Guys,
I am testing a simple materialized view in sqlplus to see where the query rewrite is performed, but I couldn't see the materialized view is re-written.. here is my step and result..
SQL> alter session set query_rewrite_enabled=true
2 ;
Session altered.
SQL> create materialized view sub_view
2 enable query rewrite
3 as
4 select * from subscriptions where extract (year from subscription_activation_date) = 2004 and status = 'SUSPEND';
Materialized view created.
SQL> set autotrace on explain;
SQL> select * from subscriptions where extract (year from subscription_activation_date) = 2004 and status = 'SUSPEND';
###..... SOME OUTPUT ....####
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'SUBSCRIPTIONS'
From the Execution Plan, I could not see materialized view (sub_view) is read...., but I expected it would scan the sub_view..
Please help to advice and point me the mistake please...
Thank You..
Best Regards,
vbeer
Last edited by vbeer; 03-17-2004 at 11:05 PM.
-
analyze the master table and the MV.
-
Hi Slimdave,
I did the following.. but seem no diff..
sql> analyze table subscriptions compute statistics;
sql> analyze table sub_view compute statistics;
sql> select * from subscriptions where extract (year from subscription_activation_date) = 2004 and status = 'SUSPEND';
but... the output still has not triggered with the view
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=329 Card=804 Bytes=8
0400)
1 0 TABLE ACCESS (FULL) OF 'SUBSCRIPTIONS' (Cost=329 Card=804
Bytes=80400)
can you give me one of simple example pls...
thank you
-
can anyone help to follow my previous question pls? I still couldn't figure out the problem that why the materialized view wasn't used after "anlayze table sub_view compute statistics".
thank you.
vbeer
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|