Use of Materialised Views in Oracle 8i 1

[nickdaniels]

We're currently moving from 7.3 to 8.0.&nbsp;&nbsp;I've heard a little about the use of materialised views in 8i and would like to know of any practical experience anyone may have of their useage.&nbsp;&nbsp;This is coming from a data warehousing point of view but any information would be useful.<br><br>regards

 

[oraguru]

I just completed designing and loading a large data warehouse using 8.1.6 (8i, release 2).&nbsp;&nbsp;I used materialized views in a number of areas to keep both result set detail as well as summaries.&nbsp;&nbsp;Great feature!&nbsp;&nbsp;Using the combination of materialized views and dimensional definition, we were able to eliminate the need to redundantly store cubed data.&nbsp;&nbsp;We used the new cube SQL statement instead.&nbsp;&nbsp;Let me know if there is any specific information I can give you.

 

[MikeLacey]

Hmmmm<br><br>What is a &quot;materialized view&quot; then?<br><br>Mike<br><br>(an Oracle7 dba) <p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[jnicho02]

Well, it's like a view, but they actually create the table and hold the data in it, i.e. its a stored query result. This is great for common OLAP queries, etc. but needs to be updated with triggers from the database. <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>

</a><br>

 

[Guest_imported]

It's a lot more that just a view.&nbsp;&nbsp;With a normal view you have to specically use that view in your statements.&nbsp;&nbsp;With materialized view, the user does not even know that these views exist.&nbsp;&nbsp;They continue writing DDL and DML statments using regular tables/views, blissfully unware that there are highly summarized materialized views available. When appropriate,&nbsp;&nbsp;Oracle automaticallly substitutes it by comparing the definition of your query against the definition of the materialized view, and (basically, if the former is a subset of the latter) dynamically replaces tables with the materialized views.<br><br>There are a couple of execelent chapters in the Oracle 8i documentations explaining exactly what is materialized views and all their ramifications.&nbsp;&nbsp;Highly recommended reading.<br><br>I just wonder that giving how far Oracle goes to parse user queries to determined likeness of queries, why could it not apply this technology to its optimizer so to abolish the old rule that every query must be identical (down to a space) in order to used the same parse tree and execution plan?&nbsp;&nbsp;Any idea?

 

[mrkoors]

We are off of the posted topic, but in Oracle8i rel 2 (8.1.6), Oracle has added a new initialization parameter, CURSOR_SHARING = { FORCE&nbsp;&nbsp;¦&nbsp;&nbsp;EXACT }.&nbsp;&nbsp;Setting it to EXACT gives the current mode of operation where only exact matches cause cursor sharing.&nbsp;&nbsp;If it is set to FORCE, the statements that only differ in some literals can use the same cursor.&nbsp;&nbsp;We have found that this helps with third party software that does not use bind varialbes, but codes constants in repeated statements ( INSERT ... VALUES (...)&nbsp;&nbsp.&nbsp;&nbsp;With EXACT matching, each statement was different and needed to be parsed using a new cursor.&nbsp;&nbsp;About 90% of the time was spent parsing the statements.&nbsp;&nbsp;Set to FORCE, they can now share the same cursor and we have seen large performance improvements.<br><br>

 

[wadewells]

&nbsp;&nbsp;Materialized Views are synonymous with Snapshots. They are physical tables that hold data from other tables, and Oracle offers internal triggers to (1) track changes to the base table in snapshot logs, and (2) refresh the materialized view periodically from the log.<br>&nbsp;&nbsp;Since Data Warehousing became a hot topic, they renamed this technolology and changed emphasis on situations to use it. For data warehousing, Materialized VIews are hyped as real tables that represent a view that is multi-table join, usually with aggregation or subsetting that summarizes data.<br>&nbsp;&nbsp;Since data warehouses are infrequently updated, and are enormous, this can improve performance of queries against the view, while increasing (a) disk space and (b) setup time.

 

[jdaubens]

Can indexes be created for materialized views?&nbsp;&nbsp;If not, how can accessing a non indexed materialized view be quicker than accessing an indexed table even if the table has many more rows in it?

 

[MikeLacey]

it's more like a snapshot than a view jdaubens - the data actually exists in a real table. So, if your Materialised View selects 1000 rows from a base table of 100,000 rows -- if will go a bit quicker. <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[betaylo1]

I am creating a very simplified replication environment with snapshots. I have three Oracle servers, one serves as the master site, the other two define snapshots on the master site's tables. Upon creating the snapshots at the remote sites, all tables are replicated. However, subsequent updates to the master tables are not being replicated - automatic refreshes are not executing. Below is general setup info. Any ideas?


I have set JOB_QUEUE_PROCESSES to 1 and JOB_QUEUE_INTERVAL to 3600 (one hour) in the init.ora file at each snapshot site.

Database links connect from the two snapshot sites to the master database and are functioning properly.
The snapshots do not specify a refresh interval, as I have created a refresh group at each snapshot site to perform refreshes every hour. next_date => sysdate,
interval => 'sysdate + 1/24'

 

[karluk]

Your alert log should contain any error messages generated by the refresh jobs. Do you see anything?

 

[sonik]

Although in principle the idea of materialise views is great, I found that in practice there are certain restrictions that can prove annoying. The main problem I faced was using the fast refresh mechanism to refresh the mat view when the detail tables had been changed. If you performed the following type of query :

insert into detail_table
select * from load_table

The fast refresh mechanism failed to work when there were multiple join tables in the mat view. I raised this with Oracle who told me this was a limitation. I was using 8.1.5 at the time however tech support reported the same probs in 8.1.6. Maybe they have sorted the limitations out in 8.1.7 but I doubt it.

The best thing I suggest is to try out the different scenarios you expect to use on small tables making sure the correct refresh mechs are being used.

Rgrds

Ketan

 

[sonik]

Although in principle the idea of materialise views is great, I found that in practice there are certain restrictions that can prove annoying. The main problem I faced was using the fast refresh mechanism to refresh the mat view when the detail tables had been changed. If you performed the following type of query :

insert into detail_table
select * from load_table

The fast refresh mechanism failed to work when there were multiple join tables in the mat view. I raised this with Oracle who told me this was a limitation. I was using 8.1.5 at the time however tech support reported the same probs in 8.1.6. Maybe they have sorted the limitations out in 8.1.7 but I doubt it.

The best thing I suggest is to try out the different scenarios you expect to use on small tables making sure the correct refresh mechs are being used.

Rgrds

Ketan

 

[sunarta]

Hi all,

I've been digging this problem almost 3 days now.

I created some 'normal' views and then join them all together to create a materialized view but I got this error message:

&quot;Subquery expressions not allowed here&quot;

I use subqueries *only* in 'normal' views.

any comments, advice really appreciated.

many thanks
anthony