Materialized View with column aggregate

Question

This is another stab into a problem I posted here. Please don't close as duplicate, because it goes in another direction.

I'd like to automatically update a database column with an aggregate of another column. There are three tables involved:

T_RIDER
  RIDER_ID
  TMP_PONYLIST
  ...

T_RIDER_PONY
  RIDER_ID
  PONY_ID

T_PONY
  PONY_ID
  PONY_NAME
  ...

T_RIDER and T_PONY have an n:m relationship via T_RIDER_PONY. T_RIDER and T_PONY have some more columns but only TMP_PONYLIST and PONY_NAME are relevant here.

TMP_PONYLIST is a semicolon spararated list of PONY_NAMES, imagine something like "Twisty Tail;Candy Cane;Lucky Leaf". I'd like to keep this field up to date no matter what happens to T_RIDER_PONY or T_PONY.

All applications work only on views, the tables are never accessed directly and I need to solve this problem with a materialized view. Materialized is an absolute requirement because of performance reasons, and it is required, that the view updates itself on commit.

The view should be created like this

CREATE MATERIALIZED VIEW
  V_TMP_PONYLIST 
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
AS SELECT 
  ...

For ... I tried the following aggregation techniques from this article.

• WM_CONCAT -> not available in my Oracle
• User-Defined Aggregate -> ORA-12054
• ROW_NUMBER and SYS_CONNECT_BY_PATH -> ORA-12054

I didn't try yet:

• Specific Funtion
• Function Generic Function using Ref Cursor
• COLLECT function

Do you see any chance to get any of these working with a materialized view, or is it pointless. Do you know of other techniques that might work with a materialized view?

I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi.

Answer 1

Hi bbuser,

you want to create an ON COMMIT REFRESH JOIN AGGREGATE MATERIALIZED VIEW. This type of MV has lots of limitations. Basically anything beyond simple joins, SUM, COUNT and AVG won't be ON COMMIT-refreshable with all DML activity.

In my opinion you are trying to solve this issue in the wrong state of mind: you have already chosen the technical path before knowing if it will physically solve your problem. You should instead study every available tools, and choose among those that will answer your requirements the best one (the easiest to implement/maintain).

You have already been given options that are known to work: complex-logic triggers, simple views, procedural approach (only update the base tables through a thoroughly tested and approved API that is known to handle the column logic well).

You have already stated that a simple view won't work because of performance problem. I would suggest studying the other options: triggers will let you keep your existing code but you will probably have lots of unforeseen side effects (complex triggers are lots of fun). Procedural logic is the easiest to code/maintain but you will have to actually use it and modify your application to use the new API. You may have to revoke the rights to update the base table to make sure the tables are updated through the API.