Query Rewrite Enabled with Materialized View

[vbeer]

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

[slimdave]

analyze the master table and the MV.

[vbeer]

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

[vbeer]

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