Re: Marterialised View Problem
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 as2 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