Materialized Views, Self Joins, Refresh on Commit

[palmeal]

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.

[slimdave]

How long does a complete refresh take?

[palmeal]

It only takes about 6 seconds to refresh - the output goes straight to an online report browser so speed is important.

[slimdave]

In the context of the tables being virtually static you might be able to get away with a complete refresh, with a little tuning. You might also look at breaking out the single MV into two MV's, one of which is fast refreshable on commit (just joining the three small tables together) and the other being complete refresh on commit (the hierarchical part).

[palmeal]

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.