Re: Marterialised View Problem

From: ddf <oratune_at_msn.com>
Date: Tue, 26 May 2009 08:52:46 -0700 (PDT)
Message-ID: <080b513d-8cf4-42a6-a528-591f1ecb3e33_at_n8g2000vbb.googlegroups.com>



On May 26, 4:39 am, KevinS <Sear..._at_googlemail.com> wrote:
> Hi All,
>
> 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

Depends upon the function:

SQL> create table item_comment(

  2  	     item_id number,
  3  	     item_desc varchar2(40),
  4  	     commnt varchar2(40)

  5 );

Table created.

SQL>
SQL> insert all
  2 into item_comment
  3 values(1,'A','C ALPHA')
  4 into item_comment
  5 values(1,'A','C BETA')
  6 into item_comment
  7 values(1,'A','C GAMMA')
  8 into item_comment
  9 values(2,'B','C DELTA')
 10 into item_comment
 11 values(3,'C','C EPSILON')
 12 into item_comment
 13 values(3,'C','C ZETA')
 14 select * From dual;

6 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> _at_stragg
SQL> create or replace type string_agg_type as object
  2  (
  3  	total varchar2(4000),
  4
  5  	static function
  6  	     ODCIAggregateInitialize(sctx IN OUT string_agg_type )
  7  	     return number,
  8
  9  	member function
 10  	     ODCIAggregateIterate(self IN OUT string_agg_type ,
 11  				  value IN varchar2 )
 12  	     return number,
 13
 14  	member function
 15  	     ODCIAggregateTerminate(self IN string_agg_type,
 16  				    returnValue OUT  varchar2,
 17  				    flags IN number)
 18  	     return number,
 19
 20  	member function
 21  	     ODCIAggregateMerge(self IN OUT string_agg_type,
 22  				ctx2 IN string_agg_type)
 23  	     return number

 24 );
 25 /

Type created.

SQL>
SQL> create or replace type body string_agg_type   2 is
  3
  4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
  5 return number
  6 is
  7 begin

  8  	 sctx := string_agg_type( null );
  9  	 return ODCIConst.Success;

 10 end;
 11
 12 member function ODCIAggregateIterate(self IN OUT string_agg_type,  13 value IN varchar2 )
 14 return number
 15 is
 16 begin
 17  	 self.total := self.total || ',' || value;
 18  	 return ODCIConst.Success;

 19 end;
 20
 21 member function ODCIAggregateTerminate(self IN string_agg_type,
 22  					    returnValue OUT varchar2,
 23  					    flags IN number)

 24 return number
 25 is
 26 begin
 27  	 returnValue := ltrim(self.total,',');
 28  	 return ODCIConst.Success;

 29 end;
 30
 31 member function ODCIAggregateMerge(self IN OUT string_agg_type,  32 ctx2 IN string_agg_type)
 33 return number
 34 is
 35 begin
 36  	 self.total := self.total || ctx2.total;
 37  	 return ODCIConst.Success;

 38 end;
 39
 40
 41 end;
 42 /

Type body created.

SQL>
SQL> CREATE or replace
  2 FUNCTION stragg(input varchar2 )
  3 RETURN varchar2
  4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;   5 /

Function created.

SQL>
SQL>
SQL> set linesize 132
SQL> column comments format a40
SQL>
SQL> create materialized view item_comm_mv as
  2 select item_id, item_desc, stragg(commnt) comments   3 from item_comment
  4 group by item_id, item_desc
  5 /

Materialized view created.

SQL>
SQL> select item_id, item_desc, comments   2 from item_comm_mv
  3 /

   ITEM_ID ITEM_DESC                                COMMENTS
---------- ----------------------------------------
----------------------------------------
         1 A                                        C ALPHA,C BETA,C
GAMMA
         2 B                                        C DELTA
         3 C                                        C EPSILON,C ZETA

SQL> David Fitzjarrell Received on Tue May 26 2009 - 10:52:46 CDT

Original text of this message