Use of marker in materialized views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Use of marker in materialized views

  1. #1
    Join Date
    Oct 2006
    Posts
    1

    Use of marker in materialized views

    Hi all,
    I joined as the fresher in the DBA team,i want to create a materialized view by using REFRESH FAST ON COMMIT option, to find the UNION ALL of two queries.
    Example i wil show you,

    create materialized view mv6
    build immediate
    refresh fast on commit
    enable query rewrite
    as
    (select d.rowid rid,d.deptno,1 marker from dept d)
    union all
    (select e.rowid rid,e.deptno,2 marker from emp e);


    Iam getting the output, but i am not understanding what is the use of rowid, marker in above example.

    Please help me....

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Who is telling you to include these? Ask them. The query makes no sense to me at all.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Thats my view dear
    the rowid is the unique & fastest way to access a certain row...and
    I want to single out some row from the group....
    marker 1 just tell that the row comes from dept...
    marker 2 just tell that the row comes from emp..
    Last edited by yxez; 10-18-2006 at 09:03 PM.
    Behind The Success And Failure Of A Man Is A Woman

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by yxez
    Thats my view dear
    the rowid is the unique & fastest way to access a certain row...and
    I want to single out some row from the group....
    marker 1 just tell that the row comes from dept...
    marker 2 just tell that the row comes from emp..
    But can you give an example query that would let you access the base table faster through this materialized view than through regular indexes?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    select * from dept where rowid='aaaaaaabbbbbbb';
    select * from emp where rowid='ccccccccdddddd';
    i can choose to access the base table anytime even if
    im reading the view;
    Last edited by yxez; 10-18-2006 at 11:59 PM.
    Behind The Success And Failure Of A Man Is A Woman

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    that didnt answer his question about mv's - why would an mv be better than querying the base table

  7. #7
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    well...i just want to have my own copy and not to disturb
    the EMP and DEPT tables so as not to be able to lock them
    Behind The Success And Failure Of A Man Is A Woman

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    eh?

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