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

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?

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

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