Skip to Main Content
  • Questions
  • Load and update summarized data to a table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geraldo.

Asked: April 19, 2020 - 2:47 pm UTC

Answered by: Chris Saxon - Last updated: April 27, 2020 - 4:35 pm UTC

Category: Database Development - Version: 18.6

Viewed 100+ times

You Asked

Hello, Ask TOM Team.

I have two tables:

CREATE TABLE USER.DOC_DETAILS
(CLIENT_ID NUMBER NOT NULL, 
 DOC VARCHAR2(13 BYTE) NOT NULL, 
REGISTER_DATE DATE NOT NULL, 
TAX1_AMMOUNT NUMBER(18,2),
TAX2_AMMOUNT NUMBER(18,2),
TAX3_AMMOUNT NUMBER(18,2)
);

CREATE TABLE USER.DOC_SUMMARY
(CLIENT_ID NUMBER NOT NULL, 
PERIOD VARCHAR2(6 BYTE) NOT NULL, 
DOC_QUANTITY NUMBER, --number of docs
DOC_AMMOUNT NUMBER(18,2),
TAX1_AMMOUNT NUMBER(18,2),
TAX2_AMMOUNT NUMBER(18,2)
TAX3_AMMOUNT NUMBER(18,2)
);


Requirement: I need to read USER.DOC_DETAILS table and load/update data every day in USER.DOC_SUMMARY table grouping by CLIENT_ID and PERIOD (YYYYMM from REGISTER_DATE) summarizing the amount columns.

A row per CLIENT_ID and PERIOD must exists in USER.DOC_SUMMARY table. Every day data is inserted in USER.DOC_DETAILS so the procedure must update the DOC_QUANTITY column and amount columns.

Note: Docs from January can arrive to USER.DOC_DETAILS today (April) so, row with CLIENT_ID x and PERIOD 202001 must be updated in USER.DOC_SUMMARY table.

Thanks in advanced.

Regards,





and we said...

Instead of a summary table, I'd create a materialized view. Refreshing this populates the MV with the results of the query.

Here's an example:

create table periods (
  period date not null
    primary key
    check ( trunc ( period, 'mm' ) = period )
);

create table doc_details (
  client_id number not null, 
  doc varchar2(13 byte) not null, 
  register_date date not null, 
  tax1_ammount number(18,2),
  tax2_ammount number(18,2),
  tax3_ammount number(18,2),
  primary key ( client_id, doc )
);

create materialized view log on doc_details
  with rowid, primary key ( 
    register_date, tax1_ammount, tax2_ammount, tax3_ammount 
  )
  including new values;
    
create materialized view log on periods
  with rowid, primary key
  including new values;
  
insert into periods
  select add_months ( date'2020-01-01', level - 1 )
  from   dual
  connect by level <= 12;
  
create materialized view doc_summary
  enable query rewrite 
as
select p.period, client_id, count(*), 
       sum ( tax1_ammount ) tax1_ammount, 
       sum ( tax2_ammount ) tax2_ammount, 
       sum ( tax3_ammount ) tax3_ammount
from   periods p 
left join doc_details dd
on     p.period = trunc ( register_date, 'mm' )
group  by p.period, client_id;

exec dbms_mview.refresh ( 'doc_summary', 'C' );

select * from doc_summary
where  period between trunc ( sysdate, 'mm') 
              and add_months ( trunc ( sysdate, 'mm'), 1 );
              
PERIOD                 CLIENT_ID   COUNT(*)   TAX1_AMMOUNT   TAX2_AMMOUNT   TAX3_AMMOUNT   
01-APR-2020 00:00:00      <null>          1         <null>         <null>         <null> 
01-MAY-2020 00:00:00      <null>          1         <null>         <null>         <null> 

insert into doc_details 
  values ( 1, 'doc1', sysdate, 1, 2, 3 );
insert into doc_details 
  values ( 1, 'doc2', sysdate + 30, 1, 2, 3 );
insert into doc_details 
  values ( 2, 'doc1', sysdate, 1, 2, 3 );
insert into doc_details 
  values ( 2, 'doc2', sysdate + 30, 1, 2, 3 );
commit;

exec dbms_mview.refresh ( 'doc_summary', 'C' );

select * from doc_summary
where  period between trunc ( sysdate, 'mm') 
              and add_months ( trunc ( sysdate, 'mm'), 1 );
              
PERIOD                 CLIENT_ID   COUNT(*)   TAX1_AMMOUNT   TAX2_AMMOUNT   TAX3_AMMOUNT   
01-APR-2020 00:00:00           2          1              1              2              3 
01-MAY-2020 00:00:00           2          1              1              2              3 
01-APR-2020 00:00:00           1          1              1              2              3 
01-MAY-2020 00:00:00           1          1              1              2              3


If you can change the query so it's an inner join, then you could make the MV fast refresh on commit. And the database will keep this in sync for you automatically.

If you can't, run the (complete) refresh at the end of each day.

and you rated our response

  (5 ratings)

Reviews

Follow Up

April 22, 2020 - 12:51 pm UTC

Reviewer: Geraldo

Thanks for the awesome idea of a MV. It's just great. Nice approach!

I have a couple questions in order to understand better:

1. Why create a periods table?

2. Why create column period with check ( trunc ( period, 'mm' )? Because the period (an extract from register_date) must be in the form YYYYMM, e.g. 202004 for all docs with date '04/*/2020'. So in the final output, period column must be:

PERIOD CLIENT
202004 2
202005 2
202004 1
202005 1

3. I forgot to tell you (sorry) that doc_details table has other docs that are not needed. doc_details table there is a column doc_type. In the doc_summary MV just must be inserted doc_type=32. What changes in your approach?

4. What does this script do in all this procedure? Is this a manual step that must be run sometimes?

insert into periods
select add_months ( date'2020-01-01', level - 1 )
from dual
connect by level <= 12;

Thanks in advanced.

Regards,

Chris Saxon

Followup  

April 22, 2020 - 6:04 pm UTC

1. I thought you needed a row/period. There could be some periods where clients have no entries. The table enables you to display these "missing" rows.

If this isn't the case, you can skip this.

2. You might display them in YYYYMM format, but the values are dates! Using a DATE data type has many advantages, such as allowing you to group by quarter, year, etc. easily.

3. You mean you only need to summarise rows with doc_type=32? If so, put that in the where clause for the MV!

4. That creates all the periods for this year. If you need this table (see point 1), then yes, you need to populate the values for all the periods you want to show in the report.

Review

April 22, 2020 - 6:26 pm UTC

Reviewer: Geraldo

Thanks for the help, Chris.

You're awesome!

I will implement these procedure and let you know.

Regards,

Follow Up

April 27, 2020 - 1:41 pm UTC

Reviewer: Geraldo

Chris, the procedure is just awesome. It works like a charm.

Just one thing. In order to work with a small set of data everyday, in the MV I want to filter today rows in the WHERE condition. So I would like to have:

create materialized view doc_summary
enable query rewrite
as
select p.period, client_id, count(*),
sum ( tax1_ammount ) tax1_ammount,
sum ( tax2_ammount ) tax2_ammount,
sum ( tax3_ammount ) tax3_ammount
from doc_details
where doc_type=32
and database_date >= sysdate
and database_date < sysdate + 1
group by p.period, client_id;

I got ORA-30353: expression not supported for query rewrite

Thanks in advanced.
Chris Saxon

Followup  

April 27, 2020 - 4:34 pm UTC

You can't use SYSDATE in a fast refreshable or for query rewrite.

Why?

The value changes every second!

So the values in the MV won't match those when you run the query. So the database can't just swap in the MV and get the correct result.

Follow Up

April 27, 2020 - 1:44 pm UTC

Reviewer: Geraldo

Well, thinking well. I think the MV log has the tracking change everyday, so it does not need to filter today rows.

Is that correct?

Thanks.
Chris Saxon

Followup  

April 27, 2020 - 4:35 pm UTC

Correct. In general you want the MV to store a row for every single day.

Review

April 27, 2020 - 4:41 pm UTC

Reviewer: Geraldo

Thanks, Chris.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.