Home » SQL & PL/SQL » SQL & PL/SQL » Performance with Materialized view fast refresh (oracle 11.2.0.3, Linux)
Performance with Materialized view fast refresh [message #598169] Thu, 10 October 2013 23:45 Go to next message
sspn2010
Messages: 141
Registered: October 2008
Senior Member
Hi,

I've 2 tables in schema A.

1) TXN_HD_FCT (This table has 6 billion records. Has fields TXN_DT, CUST_ID, TXN_AMT, TXN_ID and the table is partitioned on TXN_DT. We load data approximately 600K+ records to this table every day
2) RELINK_CUST (This table has 2 fieds CUST_ID and NEW_CUST_ID). We load data approximatley 100-200K+ records every day into this table. This table has total 10MM+ records now.

Now i need to create a table (TXN_UPD_HDR_FCT) in Schema B by joining above 2 tables using below query every day

CREATE TABLE TXN_UPD_HDR_FCT
  select a.TXN_DT,
         nvl(b.new_cust_id, a.cust_id) as cust_id,
         a.txn_amt,
         a.txn_id,
    from schema_A.TXN_HD_FCT a,
         schema_A.RELINK_CUST b
   where a.cust_id = b.cust_id(+);



We update CUST_ID to NEW_CUST_ID based on some rules (and keep it in the table RELINK_CUST) and need to take this new_cust_id while building the table TXN_UPD_HDR_FCT. Building this table is taking approximately 4+ hours every day. Can someone sugggest any other approach without re-building the whole table every day, just refresh only records from TXN_HDR_FCT whose CUST_ID is changed or any records inserted into TXN_HDR_FCT? I read about Materialized views but not sure how can i create MV logs on 2 tables? If i use materialized views do i need to create indexes every day?

Thanks!

icon5.gif  Re: Performance with Materialized view fast refresh [message #598175 is a reply to message #598169] Fri, 11 October 2013 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 57645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can write a MERGE statement from the MVIEW LOGs on the tables or your own log table .
How does RELINK_CUST is filled?

[Updated on: Fri, 11 October 2013 00:31]

Report message to a moderator

Re: Performance with Materialized view fast refresh [message #598203 is a reply to message #598175] Fri, 11 October 2013 08:15 Go to previous messageGo to next message
sspn2010
Messages: 141
Registered: October 2008
Senior Member
Thank you Michael. We have a process that runs every day and populates data into this RELINK_CUST table before creating the TXN_UPD_HDR_FCT table.

Could you please demostrate a simple example on how to use MERGE from the MVIEW LOGs?

Thanks!
icon5.gif  Re: Performance with Materialized view fast refresh [message #598205 is a reply to message #598203] Fri, 11 October 2013 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 57645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you explain in details (this is closely related on how to do what you want) the process how you fill RELINK_CUST from updates of TXN_HD_FCT? I fail to understand how it works to do it once per day as after you updated TXN_HD_FCT with new_id you no more have the previous id. Is there a new_cust_id column in TXN_HD_FCT?
What happens if a cust_id is updated several times?

If I understand you query TXN_UPD_HDR_FCT contains all txn information with either a new_cust_id from RELINK_CUST (find where?) or the cust_id from TXN_HD_FCT.
So if you have several "updates" of a cust_id ("update" necause it seems there is actual SQL UPDATE on cust_id) you have several rows for the same txn.

Is this correct?

Re: Performance with Materialized view fast refresh [message #598214 is a reply to message #598205] Fri, 11 October 2013 09:03 Go to previous messageGo to next message
sspn2010
Messages: 141
Registered: October 2008
Senior Member
We don't update CUST_ID in TXN_HD_FCT table (which is very big table with 3 billion rows) with new_cust_id from RELINK_CUST table, so we are creating a new table TXN_UPD_HDR_FCT by joining TXN_HD_FCT and RELINK_CUST tables.

So if you have several "updates" of a cust_id ("update" necause it seems there is actual SQL UPDATE on cust_id) you have several rows for the same txn.

No, for one CUST_ID there will be only one NEW_CUST_ID in the RELINK_CUST table (We often update this NEW_CUST_ID). RELINK_CUST table is unique on CUST_ID table.


CREATE TABLE TXN_UPD_HDR_FCT as
with txn_hd_fct as -- This table has 3 Billion records and Range partitioned on TXN_DT
  (select to_date('20130704','YYYYMMDD') txn_dt, 123 as CUST_ID, 125 as txn_amt, 'TX1' as txn_id from dual
   union
   select to_date('20130804','YYYYMMDD') txn_dt, 345 as CUST_ID, 23 as txn_amt, 'TX2' as txn_id from dual
   union
   select to_date('20130904','YYYYMMDD') txn_dt, 678 as CUST_ID, 47 as txn_amt, 'TX2' as txn_id from dual
  ),
  relink_cust as -- This table is unique on cust_id and HASH Partitioned on CUST_ID
  (select 345 as cust_id, 987 as new_cust_id from dual
   union
   select 932 as cust_id, 876 as new_cust_id from dual
  )
  Select a.txn_dt,
         nvl(b.new_cust_id, a.cust_id) as cust_id,
         a.txn_amt,
         a.txn_id
    from txn_hd_fct a,
         relink_cust b
   where a.cust_id = b.cust_id(+)
  ;


I was reading about PARTITION CHANGE TRACKING (PCT). WIll this be useful for this requirement?

Thanks!
Re: Performance with Materialized view fast refresh [message #598225 is a reply to message #598214] Fri, 11 October 2013 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 57645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So, if I understand, you do not "update" on the fly but once per day you fill RELINK_CUST table with the updates of the day (from unknown source) and the TXN_HD_FCT is not touch by this updating process. Is this correct?

Re: Performance with Materialized view fast refresh [message #598229 is a reply to message #598225] Fri, 11 October 2013 10:44 Go to previous messageGo to next message
sspn2010
Messages: 141
Registered: October 2008
Senior Member
Yes. that's correct. RELINK_CUST update process will not touch TXN_HD_FCT
icon3.gif  Re: Performance with Materialized view fast refresh [message #598231 is a reply to message #598229] Fri, 11 October 2013 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 57645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'd try to first create the table TXN_UPD_HDR_FCT with the whole TXN_HD_FCT in parallel mode, create a unique index on TXN_UPD_HDR_FCT.cust_id and then update TXN_UPD_HDR_FCT with RELINK_CUST new_cust_id.

Don't forget to collect statistics on both TXN_UPD_HDR_FCT and RELINK_CUST after their load.

[Updated on: Fri, 11 October 2013 11:08]

Report message to a moderator

Re: Performance with Materialized view fast refresh [message #598234 is a reply to message #598231] Fri, 11 October 2013 11:22 Go to previous messageGo to next message
sspn2010
Messages: 141
Registered: October 2008
Senior Member
Is it a good idea to update a big table which has 3 billion rows. Also one CUST_ID can have multiple TXN's with different TXN_ID. so TXN_HDR_FCT is unique on CUST_ID, TXN_DT and TXN_ID.

Can i create MVIEW log's on the TXN_HDR_FCT and RELINK_CUST and create a mview TXN_UPD_HDR_FCT with the query mentioned above and do the fast refresh? it will be faster than updating the table? I might be wrong. Please advise.
icon2.gif  Re: Performance with Materialized view fast refresh [message #598235 is a reply to message #598234] Fri, 11 October 2013 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 57645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't think you can do a fast refresh with an outer join but in this point Oracle improves greatly at each version so try it but for the moment I stay with my idea (with a non unique index but maybe Oracle will not take it, it depends on the memory and clustering factor you will have).

Re: Performance with Materialized view fast refresh [message #598239 is a reply to message #598234] Fri, 11 October 2013 12:16 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1094
Registered: May 2013
Location: Hi-tech city
Senior Member
sspn2010 wrote on Fri, 11 October 2013 21:52
Is it a good idea to update a big table which has 3 billion rows.


I would say a DDL to create table ...as select...with parallel no logging if possible will be much faster than a DML, i.e. update in your case. An update on a table not only updates the data but also the index keys defined on the table columns.
Re: Performance with Materialized view fast refresh [message #598246 is a reply to message #598239] Fri, 11 October 2013 13:21 Go to previous messageGo to next message
sspn2010
Messages: 141
Registered: October 2008
Senior Member
Thank you Lalit and Michael for your suggestions.

Quick question. If i create index on materialized view and refreh mateialized view, will the refresh update indexes also?

Thank you much!
Re: Performance with Materialized view fast refresh [message #598247 is a reply to message #598246] Fri, 11 October 2013 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 21967
Registered: January 2009
Senior Member
>If i create index on materialized view and refreh mateialized view, will the refresh update indexes also

what exactly prevents you from doing so yourself & looking at the results on your DB?
Re: Performance with Materialized view fast refresh [message #598257 is a reply to message #598169] Fri, 11 October 2013 15:37 Go to previous messageGo to next message
John Watson
Messages: 4102
Registered: January 2010
Location: Global Village
Senior Member
And now for something completely different: you could use the Change Data Capture facility. Rather than capturing changes in materialized view logs, capture them asynchronously from the redo stream,
http://docs.oracle.com/cd/E11882_01/server.112/e25554/cdc.htm
I am not saying that this would be better or worse than a method based on materialized view logs. But it is a technique that is specifically designed for this sort of thing.

(Do not be put off by the comment in the docs about "de-supported in a future release", that is an attempt to make you spend a pile of cash on GoldenGate. CDC is all there in 12c.)
Re: Performance with Materialized view fast refresh [message #598261 is a reply to message #598257] Fri, 11 October 2013 18:49 Go to previous messageGo to next message
sspn2010
Messages: 141
Registered: October 2008
Senior Member
Michael,

If i use update then it will update only the cust_id's but i need to move the new records inserted into TXN_HDR_FCT to TXN_UPD_HDR_FCT. I think i can do both in one statement using MERGE right? or do i need to do the insert the first and do the update part which will update CUST_ID?

Lalit mentioned that "An update on a table not only updates the data but also the index keys defined on the table columns"

if i have global indexes on TXN_UPD_HDR_FCT and i do update/insert will the index be re-build for all 3 billion records or only the partitions what changed?

Thanks!
Re: Performance with Materialized view fast refresh [message #598262 is a reply to message #598261] Fri, 11 October 2013 19:04 Go to previous message
BlackSwan
Messages: 21967
Registered: January 2009
Senior Member
sspn2010 wrote on Fri, 11 October 2013 16:49
Michael,

If i use update then it will update only the cust_id's but i need to move the new records inserted into TXN_HDR_FCT to TXN_UPD_HDR_FCT. I think i can do both in one statement using MERGE right? or do i need to do the insert the first and do the update part which will update CUST_ID?

Lalit mentioned that "An update on a table not only updates the data but also the index keys defined on the table columns"

if i have global indexes on TXN_UPD_HDR_FCT and i do update/insert will the index be re-build for all 3 billion records or only the partitions what changed?

Thanks!


realize that YOU can do proof of concept testing against a table with very few rows; say 30.
You should be able to answer all your questions about what works in less than an hour if you have decent SQL knowledge.
Previous Topic: How to get this SQL output :-
Next Topic: Inserting multiple records with CLOB using select
Goto Forum:
  


Current Time: Thu Apr 24 08:39:14 CDT 2014

Total time taken to generate the page: 0.22962 seconds