Query Rewrite Enabled with Materialized View
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query Rewrite Enabled with Materialized View

  1. #1
    Join Date
    Apr 2003
    Posts
    25

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    analyze the master table and the MV.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2003
    Posts
    25
    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

  4. #4
    Join Date
    Apr 2003
    Posts
    25
    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
  •  


Click Here to Expand Forum to Full Width