Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Fast refresh of materialized Views after delete on base table, Why is it so complex?
jerry
post Jun 19 2006, 04:19 PM
Post #1


Member
**

Group: Members
Posts: 14
Joined: 29-September 04
Member No.: 653



scenario:

Table A: contains the raw data with millions of records. This table has multiple records for each person for each month.

eg.

person payment_date amount
1 1/2/06 10
1 1/18/06 20
1 2/4/06 30
2 2/5/06 15
2 2/20/06 25

Table B: is aggregated sum of table A. One record for each person for each year

Person year month_1 month_2 month_3 ...... month_12
1 2006 30 30 0 ...... 0
2 2006 0 40 0 ....... 0


We want to create Table B as a materialized view populated from Table A with fast refresh option. So here is what I did.

I created the materialized view on Table B with prebuilt option (since I already have data) and fast refresh. each column (month_1, month_2 etc) is populated by summing the "amount" column for each month in Table A.

This works fine as long as I am doing inserts in Table A. But if I delete a record, I am expecting the change to reflect in Table B. But instead the refresh script errors with mesg

ORA-30439: refresh of 'TABLE_B' failed because of ORA-32314: REFRESH
FAST of "MYSCHEMA"."TABLE_B" unsupported after deletes/updates. After going through the oracle documnet, I realized that I need to define a count(expr) and count(*) so as to support fast refresh. But for this I need to define 12 count(expr) columns in the materialized view and one count(*). Since I am building this on the existing table, I need to create new columns to the existign table to support this.

Is this the right method? Is there no simple way?

~Jerry
Go to the top of the page
 
+Quote Post
jerry
post Jun 20 2006, 12:37 PM
Post #2


Member
**

Group: Members
Posts: 14
Joined: 29-September 04
Member No.: 653



I did couple of tests more(I m in 9i). It appears that we need to have a count(expr) for each sum (expr) in the select statement. So if I have 12 columns with SUM(expr), I need to have additional 12 columns with COUNT(expr) apart from COUNT(*), in order for a fast refresh to work with delete/update.

This doesn't seem to be a good option at all.

~Jerry
Go to the top of the page
 
+Quote Post
jerry
post Jun 20 2006, 02:31 PM
Post #3


Member
**

Group: Members
Posts: 14
Joined: 29-September 04
Member No.: 653



QUOTE (David Aldridge @ Jun 20 2006, 07:05 PM) *
Is this on 9i? What expression are you summing on?


Yes. Its on 9i.
The expression is something as below

select yyyy --this column reports the year. eg. 2004
,sum(case when MM =1 then
amount
else
0
end) JAN --JAN amount
, sum(case when MM =2 then
amount
else
0
end) FEB
.....
,
sum(case when MM =12 then
amount
else
0
end) DEC
,sum(amount) YEAR
from TABLE_A
group by yyyy, person_id
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 27th August 2014 - 06:10 PM