Quick questions on materialized views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Quick questions on materialized views

  1. #1
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82

    Quick questions on materialized views

    I'm creating some schema's in a local database that I have HTMLDB 2.0 installed on, the schema's will basically have views to other tables via dblinks to other databases I want to query thru HTMLDB.

    I've never used MV's before, nor have had anything to do with them. I have been reading up on them somewhat and they sound like the best way to go rather than ordinary views.

    I did some testing, doing a select count(*) from a large table via dblink and it returned a count in .03 seconds (600,000 records) So I created a regular view on this table (with select *) which of course returned the same result. Then I created a materialized view on refresh with rowid (there is no primary key on table) on the same table, and it was taking 8 seconds to return a count. The machines are pretty well the same, local database is on P4 windows xp machine 1GB ram, remote database is on PIII suse linux 7.3 machine 512MB ram, both running 9.2.0.6 database.

    I did not think I have to enable query_rewrite as I am querying the materialized view directly. Am I doing something wrong here?

    As well, some of the databases I will be using HTMLDB on are archival, and have no updates going on in tables, am I better off to just make copies of the tables or use regular views rather than MV's?

    thanks
    Glen A. S.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you can do what you want without MV's then go that route.

    Having said that, are you creating the MV locally against a remnote table, or on the remote database itself?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82
    Quote Originally Posted by slimdave
    If you can do what you want without MV's then go that route.

    Having said that, are you creating the MV locally against a remnote table, or on the remote database itself?
    I am creating the MV locally against a remote table. Having played some more with this I created a copy of the table itself locally and queried it, and that consistently returned a time of 6 seconds, so I am attributing the performance difference to oracle on linux versus oracle on windows (I have seen this before) rather than blaming the MV
    Glen A. S.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    When you select count(*) from the remote table you're probably getting a fast index-based access method. Check the execution plan for the local MV, as it sounds like you're getting a full table scan
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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