OraFAQ Forum: Performance Tuning » Materialized View Based on a Union Query

Home » RDBMS Server » Performance Tuning » Materialized View Based on a Union Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized View Based on a Union Query [message #164873]

Mon, 27 March 2006 04:43

kishorekumar_1982
Messages: 1
Registered: March 2006
Location: Hyderabad

Junior Member

Hi,

We have requirement to create a materialized view based on a union query. The union query is based on tables(mtl_system_items,mtl_parameters,cst_item_costs) in Oracle Applications E- Business Suite.

We have used the complete refresh option, for refreshing the materialized view.

There view is taking around 1 hour to refresh.

Can any anybody tell, how to use to use the fast refresh option and creation the log for the refresh option.

Please suggest any improvements to enhance the performance of the materialized view.

Thanks
Kishore

Report message to a moderator

Re: Materialized View Based on a Union Query [message #164976 is a reply to message #164873]

Mon, 27 March 2006 22:14

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

The doco is really sketchy on this - I understand your predicament.

You cannot create a Fast Refresh MV with UNION ALL. Not at all. No exceptions.

You can create a Fast Refresh MV with UNION, but only in circumstances so limited to be almost useless.
- The SELECT clause must be identical in each part of the UNION
- The FROM clause must be identical in each part of the UNION

eg.

SELECT a,b,c
FROM   taba
WHERE  d=10
UNION
SELECT a,b,c
FROM   taba
WHERE  e='X'

Essentially it is a way to use OR in a fast-refresh MV. The query above is equivalent to:

SELECT a,b,c
FROM   taba
WHERE  d=10
OR     e='X'

I know of no work-around for the UNION MV limitations.

For UNION ALL, I create a separate fast refresh MV for each SQL, and then UNION ALL them together in a view.
This technique is known as a Partition View. It enjoyed a brief popularity in V7, but Partitioned Tables pretty much killed it in V8. I reckon it's still useful.
If the Partition View satisfies a number of criteria (same columns/data types, same indexes, same constraints, ...) then the CBO can treat it almost as it would a table. This was more important in V7; in later versions we have Query Transformation and Predicate Pushing, which mean that the optimiser can treat a UNION-ALL-view like a table under less strict conditions.
It may still be worth ensuring that you comply with the strict Partiton View requirements. If you do, then the CBO can perform STAR_TRANSFORMATION on the view.
Here is a doc I wrote on Partition Views.

Ross Leishman

Report message to a moderator

Previous Topic:	Different Plan for Inner and Outer Query
Next Topic:	Global and Local Index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 08:45:02 CST 2024