Marterialised View Problem

We are developing an application in Oracle 10.2.0.3.0.

For the sake of performance we are using a materialized view which contains denormalised data for Items.

There is a Comment table to keep a history of all comments for Items.

I have been asked to add the comments for each Item to the table, however if I do a simple join I will get a materailized view which is the product of the Item table and the Comment table.

ie

ITEM_ID	DESC	COMMENT
1	A	C ALPHA
1	A	C BETA
1	A	C GAMMA
2	B	C DELTA
3	C	C EPSILON
3	C	C ZETA

What I would like to do is have a materialized view with all changes for an item in a single field as follows.

ITEM_ID	DESC	COMMENT
1	A	C ALPHA. C BETA. C GAMMA
2	B	C DELTA
3	C	C EPSILON. C ZETA

From what I can gather materialized views can't be built using functions, and my experiments so far seem to bear this out.

Is there some indiosyncracy of materialized views I might have missed which might allow me to do what I want?

Thanks Received on Tue May 26 2009 - 04:39:46 CDT