Home » SQL & PL/SQL » SQL & PL/SQL » Materialized Views, Self Joins, Refresh on commit
Materialized Views, Self Joins, Refresh on commit [message #188862] Tue, 22 August 2006 03:52 Go to next message
palmeal
Messages: 7
Registered: August 2006
Junior Member
Oracle Version --> 9.2.0.7.0


I have having a problem creating a materialized view with the Refresh on Commit option. I get the following error --> ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view.
I have searched the web for explanations regarding this error and haven't found the exact set of circumstances that match my own.

The view that I am trying to create basically takes a hierarchial table and displays the relationships horizontally.

The view that I am trying to set up is as follows (comments follow after code)

CREATE MATERIALIZED VIEW SORPOWN.VW_MAT_CATEGORY_HIERARCHY
BUILD IMMEDIATE
REFRESH ON COMMIT
AS
SELECT
c.id client_id,
c.name client_name,
c.code client_code,
cce1.category_id category_id,
DECODE(cce10.entry_id
,NULL, DECODE(cce9.entry_id
,NULL, DECODE(cce8.entry_id
,NULL, DECODE(cce7.entry_id
,NULL, DECODE(cce6.entry_id
,NULL, DECODE(cce5.entry_id
,NULL, DECODE(cce4.entry_id
,NULL, DECODE(cce3.entry_id
,NULL, DECODE(cce2.entry_id
,NULL,cce1.entry_id
,cce2.entry_id)
,cce3.entry_id)
,cce4.entry_id)
,cce5.entry_id)
,cce6.entry_id)
,cce7.entry_id)
,cce8.entry_id)
,cce9.entry_id)
,cce10.entry_id) lowest_level_id,
cce1.entry_id level_1_id,
cce1.description level_1_description,
cce1.display_order level_1_display_order,
cce1.code level_1_code,
cce2.entry_id level_2_id,
cce2.description level_2_description,
cce2.display_order level_2_display_order,
cce2.code level_2_code,
cce3.entry_id level_3_id,
cce3.description level_3_description,
cce3.display_order level_3_display_order,
cce3.code level_3_code,
cce4.entry_id level_4_id,
cce4.description level_4_description,
cce4.display_order level_4_display_order,
cce4.code level_4_code,
cce5.entry_id level_5_id,
cce5.description level_5_description,
cce5.display_order level_5_display_order,
cce5.code level_5_code,
cce6.entry_id level_6_id,
cce6.description level_6_description,
cce6.display_order level_6_display_order,
cce6.code level_6_code,
cce7.entry_id level_7_id,
cce7.description level_7_description,
cce7.display_order level_7_display_order,
cce7.code level_7_code,
cce8.entry_id level_8_id,
cce8.description level_8_description,
cce8.display_order level_8_display_order,
cce8.code level_8_code,
cce9.entry_id level_9_id,
cce9.description level_9_description,
cce9.display_order level_9_display_order,
cce9.code level_9_code,
cce10.entry_id level_10_id,
cce10.description level_10_description,
cce10.display_order level_10_display_order,
cce10.code level_10_code
FROM
tb_clients c
,tb_client_categories cc
,tb_client_category_entries cce1
,tb_client_category_entries cce2
,tb_client_category_entries cce3
,tb_client_category_entries cce4
,tb_client_category_entries cce5
,tb_client_category_entries cce6
,tb_client_category_entries cce7
,tb_client_category_entries cce8
,tb_client_category_entries cce9
,tb_client_category_entries cce10
WHERE c.id = cc.client_id
AND cc.id = cce1.category_id
AND cce1.PARENT_ID IS NULL
AND cce1.ENTRY_ID = cce2.PARENT_ID(+)
AND cce2.ENTRY_ID = cce3.PARENT_ID(+)
AND cce3.ENTRY_ID = cce4.PARENT_ID(+)
AND cce4.ENTRY_ID = cce5.PARENT_ID(+)
AND cce5.ENTRY_ID = cce6.PARENT_ID(+)
AND cce6.ENTRY_ID = cce7.PARENT_ID(+)
AND cce7.ENTRY_ID = cce8.PARENT_ID(+)
AND cce8.ENTRY_ID = cce9.PARENT_ID(+)
AND cce9.ENTRY_ID = cce10.PARENT_ID(+);

I am assuming that the "REFRESH ON COMMIT" fails due to the self joining table. If I take "REFRESH ON COMMIT" out of the view then it created without any problems.

If I leave it in and remove all of the self joins then the view is created - unfortunately this wouldn't give me what I want.

The 3 tables above are all very small and virtually static - the Select query itself runs in less that a second but the query that reads this runs over 30,000 times so the execute time ramps up quickly. I created the output of the select query above as a table and the performance of the query that calls this went from 20 mins to 6 secs but as there is no automatic way to easily update this table I want a materialized view to do the work for me.

I've read about adding constraints to key columns and adding view logs but I can't find anything on how they would work with "REFRESH ON COMMIT" and self joins so I haven't gone down this route yet.

Is there anything that I can do to get the materialized view to create with the "REFRESH ON COMMIT" keeping the self joins or are there any ways around this.
I don't want to have a materialized view where I have to set a dba task to renew its data if the underlying data change.
I also don't want to go down the path of triggers if possible.

I also had a look at querying hierarchies using CONNECT BY but I need to take the vertical hierarchy and return it horizontally.

This is a bit long winded but I would appreciate any help where possible.

[Updated on: Tue, 22 August 2006 04:02]

Report message to a moderator

Re: Materialized Views, Self Joins, Refresh on commit [message #188877 is a reply to message #188862] Tue, 22 August 2006 04:35 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Use DBMS_MVIEW.EXPLAIN_MVIEW to determine the Fast Refresh cpaabilities of the MV.

And there's also these three conditions from the Fast Refresh restrictions that might be applicable:
Quote:

If the WHERE clause of the query contains outer joins, then unique constraints must exist on the join columns of the inner join table.

If there are no outer joins, you can have arbitrary selections and joins in the WHERE clause. However, if there are outer joins, the WHERE clause cannot have any selections. Furthermore, if there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

There are more conditions to check at that link - I just reproduced the most likely.

This seems to indicate that your non-join predicate
AND cce1.PARENT_ID IS NULL
could be a problem as well, as outer join MVs cannot contain filters.

Ross Leishman
Re: Materialized Views, Self Joins, Refresh on commit [message #189081 is a reply to message #188877] Wed, 23 August 2006 02:36 Go to previous messageGo to next message
palmeal
Messages: 7
Registered: August 2006
Junior Member
Thank you for your reply - it looks like I can't use "REFRESH ON COMMIT" fot this view so what other options are generally available? The materialized view (without Refresh on Commit) only takes about 6 seconds to run so can this quick build time be utilised in some way ?

I reduced the view that I was trying to create to just two levels in the hierarchy (from 10 levels) but this still wouldn't create. There is already an unique index on Entry_Id which I think is the inner table. I then took off the outer join clause to make it an equi join and removed the is null predicate but the view would still not create.
The summation from this is that materialized views do not support self joins - is this know to be the case for 9i ?

[Updated on: Wed, 23 August 2006 04:03]

Report message to a moderator

Re: Materialized Views, Self Joins, Refresh on commit [message #189092 is a reply to message #189081] Wed, 23 August 2006 03:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think I did once read something in the manuals about self-joins not working for Fast Refresh, but the only time I tried it was on v10.1 and it worked.

Stongly suggest you try DBMS_MVIEW.EXPLAIN_MVIEW. It should give you a reason why it cannot be done.

Ross Leishman
Re: Materialized Views, Self Joins, Refresh on commit [message #189118 is a reply to message #189092] Wed, 23 August 2006 04:55 Go to previous messageGo to next message
palmeal
Messages: 7
Registered: August 2006
Junior Member
I see from MV_CAPABILITIES_TABLE that refresh fast is not possible but I'm not sure if this is due to the other explanations in the output.

PCT
Not Possible

REFRESH_COMPLETE
Possible

REFRESH_FAST
Not Possible

REWRITE
Possible

PCT_TABLE
Not Possible --> TB_CLIENT_CATEGORY_ENTRIES --> relation is not a partitioned table

REFRESH_FAST_AFTER_INSERT
Not Possible --> SORPOWN.TB_CLIENT_CATEGORY_ENTRIES --> the detail table does not have a materialized view log

REFRESH_FAST_AFTER_ONETAB_DML
Not Possible --> see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML
Not Possible --> see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT
Not Possible --> PCT is not possible on any of the detail tables in the materialized view

REWRITE_FULL_TEXT_MATCH
Possible


REWRITE_PARTIAL_TEXT_MATCH
Possible

REWRITE_GENERAL
Possible

REWRITE_PCT
Not Possible --> general rewrite is not possible and PCT is not possible on any of the detail tables
Re: Materialized Views, Self Joins, Refresh on commit [message #189269 is a reply to message #189118] Wed, 23 August 2006 23:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yeah, it takes a bit of interpretation.

REFRESH_FAST_AFTER_INSERT
Not Possible --> SORPOWN.TB_CLIENT_CATEGORY_ENTRIES --> the detail table does not have a materialized view log

Create an MV Log on this table

Ross Leishman
Re: Materialized Views, Self Joins, Refresh on commit [message #189329 is a reply to message #189269] Thu, 24 August 2006 03:24 Go to previous messageGo to next message
palmeal
Messages: 7
Registered: August 2006
Junior Member
I created the MV log as you suggested --> CREATE MATERIALIZED VIEW LOG ON tb_client_category_entries WITH ROWID, SEQUENCE and then re-ran the select clause of the materialized view using BMS_MVIEW.EXPLAIN_MVIEW. There was a lot more data in mv_capabilities_table this time. The Primary Key on tb_client_category_entries is Entry_Id. Thanks for the continued support.

PCT
Not Possible

REFRESH_COMPLETE
Possible

REFRESH_FAST
Not Possible

REWRITE
Possible

PCT_TABLE
Not Possible --> TB_CLIENTS --> relation is not a partitioned table

PCT_TABLE
Not Possible --> TB_CLIENT_CATEGORIES --> relation is not a partitioned table

PCT_TABLE
Not Possible --> TB_CLIENT_CATEGORIES --> no partition key or PMARKER in select list

PCT_TABLE
Not Possible --> TB_CLIENT_CATEGORY_ENTRIES --> relation is not a partitioned table - this message is repeated 10 times

PCT_TABLE
Not Possible --> TB_CLIENT_CATEGORY_ENTRIES --> no partition key or PMARKER in select list - this message is repeated 7 times

REFRESH_FAST_AFTER_INSERT
Not Possible --> multiple instances of the same table or view

REFRESH_FAST_AFTER_INSERT
Not Possible --> outer join in mv

REFRESH_FAST_AFTER_INSERT
Not Possible --> one or more joins present in mv

REFRESH_FAST_AFTER_ONETAB_DML
Not Possible --> see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML
Not Possible --> see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT
Not Possible --> PCT is not possible on any of the detail tables in the materialized view

REWRITE_FULL_TEXT_MATCH
Possible

REWRITE_PARTIAL_TEXT_MATCH
Not Possible --> multiple instances of the same table or view

REWRITE_GENERAL
Possible

REWRITE_PCT
Not Possible --> general rewrite is not possible and PCT is not possible on any of the detail tables
Re: Materialized Views, Self Joins, Refresh on commit [message #189384 is a reply to message #189329] Thu, 24 August 2006 06:39 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Brick wall this time:

REFRESH_FAST_AFTER_INSERT
Not Possible --> multiple instances of the same table or view

REFRESH_FAST_AFTER_INSERT
Not Possible --> outer join in mv

Don't think you can get past these, however I have done both of them in v10.1 without difficulty (but NOT with a filter in the outer join WHERE clause).

If you are a bit detail minded, you could work out an efficient algorithm for maintaining your own denormalised version using triggers.

Ross Leishman
Re: Materialized Views, Self Joins, Refresh on commit [message #189391 is a reply to message #189384] Thu, 24 August 2006 07:18 Go to previous message
palmeal
Messages: 7
Registered: August 2006
Junior Member
Thanks for all your help - I'll go with the materialized view without the "Refresh on Commit" for just now as these tables are static - this will change at a later date so the options are either 10g (although looks like I couldn't add my filter), triggers, or hold the hierarchy table horizontally.
Previous Topic: Retrieve data from a table based on column name specified in another table.
Next Topic: know NOTHING about Oracle, but have a dmp file
Goto Forum:
  


Current Time: Sun Dec 04 16:42:11 CST 2016

Total time taken to generate the page: 0.07145 seconds