OraFAQ Forum: Performance Tuning » Creating Materialized View using Union All Operator

Home » RDBMS Server » Performance Tuning » Creating Materialized View using Union All Operator (Windows, Oracle 9iR2)

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

Creating Materialized View using Union All Operator [message #309939]

Sun, 30 March 2008 04:12

ajitpal.s
Messages: 204
Registered: November 2006

Senior Member

Hi Guys,
Im getting more and more confused after reading about materialized view
Actualy what im trying to accomplish here is to create a materialized view using union all operator on partiotioned tables, based on 'on demand' fast refreshed.

So , i was just doing some test and i hope someone could help me out here to make things clearer.

I have 2 partitioned tables with the following defination

Table1

CREATE TABLE part_tab1
    (prof_history_id                NUMBER(10,0),
    person_id                      NUMBER(10,0) NOT NULL,
    organization_id                NUMBER(10,0) NOT NULL,
    record_date                    DATE NOT NULL,
    ph_comments                    VARCHAR2(200))
  PARTITION BY RANGE (RECORD_DATE)
  (
  PARTITION jan2008 VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  )

Table2

CREATE TABLE part_tab2
    (prof_history_id                NUMBER(10,0),
    person_id                      NUMBER(10,0) NOT NULL,
    organization_id                NUMBER(10,0) NOT NULL,
    record_date                    DATE NOT NULL,
    ph_comments                    VARCHAR2(200))
  PARTITION BY RANGE (RECORD_DATE)
  (
  PARTITION jan2008 VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  )

The i issued the following

exec dbms_mview.explain_mview('select 1 AS PMARKER,RECORD_DATE from part_tab1 union all select 1 AS PMARKER,RECORD_DATE from part_tab2');

And when i queried the mv_capabilities_table, this is what i noticed
1) Unable to create fast refresh materialized view even though the mv_capabilities_table clearly says that im allowed to do that, but due to union all, i cant. So i included a pmarker column as suggested to solve that, but still im unable to do it, they advised to include pmarker or partition key in select list, but even after doing that, im unable to create fast refresh materialized view with union all

2) iM not sure why it says that PCT is not allowed on tab_part2, since pmarker or partition key is not in the select list, even if i have included it..

Quote:

What im trying to achieve is the following
1) Create a meterialized view with union all on partition table
2) Refresh the materialized view on demand as at whenever a new partition is added and old partition is truncated/dropped.

I really appreciate if someone could just let me know the normal approcach of accomplishinhg the following task

thanks for the time.

Report message to a moderator

Re: Creating Materialized View using Union All Operator [message #309943 is a reply to message #309939]

Sun, 30 March 2008 05:37

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

Senior Member

Interesting. I've been exactly where you are now. About 2 years ago I was trying to do the same thing on Oracle 10R1.

Firstly, the PMARKER needs to be different in each SELECT. You cannot SELECT 1 AS pmarker in both queries. SELECT 2 in the second one instead.

That should solve your FAST REFRESH problem. But I'm afraid you will not be able to PCT REFRESH a UNION ALL Materialized View.

See http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/advmv.htm#sthref577

Data Warehousing Manual

PCT-based refresh is not supported for UNION ALL materialized views.

ie. If you restrict yourself to just DML statements on the base tables, you'll be fine. But the minute you ADD, DROP, SPLIT, TRUNCATE, or MERGE partitions, you will have to run a FULL refresh.

This might be viable with non-time-based partitions, but with time-based RANGE partitions, you don't want to be full-refreshing every time you add a partition or archive an old one.

The solution is to use good old fashioned Partition Views. You won't find them in the 10g manual. They were implemented in v7 (before Partitioning) and have been steadily phased out ever since v8.0.

The idea is to create a UNION ALL VIEW instead of a Materialized View. Oracle is able to optimise queries on the view as if it were a partitioned table. Its a bit more tricky than that though; there are RULES! You will find them in the 7.3 manual.

Some are redundant (such as the PARTITION_VIEWS_ENABLED parameter); the important ones are:
- SELECT clause is SELECT * or an expansion of SELECT *
- One table only in the FROM clause
- No group by, aggregate functions, distinct, rownum, start-with/connect by
- Column Names and data types are identical in each table
- Each table has indexes of the same type on the same columns
- Each table has the same Constraints of the same state

If all these are true, you should be fine with a Partition View - they even support STAR TRANSFORMATION as Fact tables.

You can tell if your UNION VIEW satisfies all of the rules for a Partition View by running an Explain Plan on:

SELECT * FROM my_partition_view

The resultant Explain Plan must show the following:

UNION ALL (PARTITION)

If you DO NOT se the PARTITION keyword on the Explain Plan, then it is NOT a Partition View, and will not optimise properly in joins and scans.

If the base tables are not identical (different columns, indexes, or constraints) and you cannot make them identical; you can create MVs on the base tables that ARE identical (and also support PCT refresh) and then create a Partition View on these. I have done this.

Ross Leishman

Report message to a moderator

Re: Creating Materialized View using Union All Operator [message #309945 is a reply to message #309943]

Sun, 30 March 2008 05:59

ajitpal.s
Messages: 204
Registered: November 2006

Senior Member

Hi,
Thanks for the reply, just wanted to clarify some of your points..

Im not sure if i got you right..

Are you suggesting to create a union all view, and then creating a materialized view on top of this union all view?

Currently we already have a union all view, the main reason of converting this view to a materialized view is to improve the query processing time. And actualy the materialized view we wish to create will just going to be a read-only mechanism on the base table. All the DML Operation (add,trancute, drop partition) will only be applied to base tables

So probably, on daily/monthly basis, we will reshresh the materialized view

[Updated on: Sun, 30 March 2008 06:05]

Report message to a moderator