OraFAQ Forum: SQL & PL/SQL » table refresh process

Home » SQL & PL/SQL » SQL & PL/SQL » table refresh process - discuss optoins (12.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

table refresh process - discuss optoins [message #682722]

Thu, 12 November 2020 00:42

talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020

Junior Member

Hi,

I have a table (let's call it table_A) that is sourced from multiple other tables, having around 300k records

now, I want to refresh this table on a daily basis with the updated information. Here are a few options that I was thinking off (probably there more and better) and wanted to get your opinion

option 1)
Step 1. create a table (one time)
Step 2. Delete all records that have been changed, since the last table refresh
Step 3. Delete all records that have been deleted from the other tables, since the last table refresh
Step 4. Add records (with new data) that has been changed, since the last table refresh
Step 5. Add new records that have been added, since the last table refresh

Option 2)
Step 1. create a table (one time)
Step 2. Delete all records that have been deleted from the other tables, since the last table refresh
Step 3. Add new records that have been added, since the last table refresh
Step 4. update records (with new data) that has been changed, since the last table refresh

The difference between options 1 and 2 is that in option 1 I am Deleting a record that has been changed since the last refresh and Inserting that record. Vs, with option 2, I am using the oracle update function.

The assumption is that with option 2, an update function will work efficiently as it will not require indexing for the records that are updated. vs. option one will require indexing as It is deleting and adding new records for all the updates.

Please advise

Thanks

[Edit MC: removed 40 blank lines]

[Updated on: Thu, 12 November 2020 01:00] by Moderator

Report message to a moderator

Re: table refresh process - discuss optoins [message #682725 is a reply to message #682722]

Thu, 12 November 2020 02:02

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How table_a is loaded or modified (file, transaction process...)?
Do the other tables simple copies of table_a with all or part of its data (same columns)?
Are the other tables modified by other processes than this one?

You need to clearly specify your requirements, maybe posting a simple example mimicking what you want.

Report message to a moderator

Re: table refresh process - discuss optoins [message #682726 is a reply to message #682722]

Thu, 12 November 2020 02:11

John Watson
Messages: 8804
Registered: January 2010
Location: Global Village

Senior Member

I do not understand the difference in then need for indexing. Whether you intend to delete or update changed rows, you have to find them. That would usually be done with an index.

By the way, I wish you would not say "record" when you mean "row".

Report message to a moderator

Re: table refresh process - discuss optoins [message #682728 is a reply to message #682726]

Thu, 12 November 2020 07:56

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

Sounds to me like what you need is not a table that you are trying to keep in synch with several other tables, but just a view. And if the joining is a performance issue, then a materialize view.

Report message to a moderator

Re: table refresh process - discuss optoins [message #682729 is a reply to message #682728]

Thu, 12 November 2020 09:05

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Well, it depends on the answer of my questions.

@OP, another question: are the other tables local or remote?

Report message to a moderator

Re: table refresh process - discuss optoins [message #682730 is a reply to message #682729]

Thu, 12 November 2020 16:03

talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020

Junior Member

How table_a is loaded or modified (file, transaction process...)? Table_A is created by joining multiple tables (in the same DB).
Do the other tables simple copies of table_a with all or part of its data (same columns)? Yes
Are the other tables modified by other processes than this one? This process do not update tables, it's just a big select statement pulling data from multiple tables

I did some research (including feedback from this session) and here are my findings (in the sequence of efficiency). can you please review and provide your feedback. Thanks

#1 Use Materialized view - daily refresh
recommended by multiple forums

#2 Truncate table and repopulate - Daily
reindexing will be required but should not be an issue
reindexing on 300k to 500k records should not take more than half an hour

#3 Delete record and then add records that are updated since the last table refresh
reindexing will be required but should not be an issue
reindexing on 300k to 500k records should not take more than half an hour

#4 Use Oracle update function to update records in table_A that are updated since the last table refresh
Why it's @ #4: update function is more expensive (time consuming) then the delete/insert function
minimum reindexing will be required

Report message to a moderator

Re: table refresh process - discuss optoins [message #682732 is a reply to message #682730]

Thu, 12 November 2020 20:14

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

talhaparvaiz@yahoo.com wrote on Thu, 12 November 2020 16:03

How table_a is loaded or modified (file, transaction process...)? Table_A is created by joining multiple tables (in the same DB).

OK

Quote:

Do the other tables simple copies of table_a with all or part of its data (same columns)? Yes

You just answered 'yes' to an 'or' question. Which side of the 'or' does the 'yes' apply to?

Quote:

Are the other tables modified by other processes than this one? This process do not update tables, it's just a big select statement pulling data from multiple tables

I did some research (including feedback from this session) and here are my findings (in the sequence of efficiency). can you please review and provide your feedback. Thanks

#1 Use Materialized view - daily refresh
recommended by multiple forums

#2 Truncate table and repopulate - Daily
reindexing will be required but should not be an issue
reindexing on 300k to 500k records should not take more than half an hour

#2 - while the extra processing may be 'acceptable', it is unnecesary with #1.

Quote:

#3 Delete record and then add records that are updated since the last table refresh
reindexing will be required but should not be an issue
reindexing on 300k to 500k records should not take more than half an hour

#4 Use Oracle update function to update records in table_A that are updated since the last table refresh
Why it's @ #4: update function is more expensive (time consuming) then the delete/insert function
minimum reindexing will be required

#3 and 4 - once you start talking about 'since the last update', then YOU have to start figuring out HOW you will decide what is 'since the last update'.

All in all, it seem to me that an MV is a no-brainer. No overhead of repeatedly loading the same data, and no worries about what is 'since the last update'.

Report message to a moderator

Re: table refresh process - discuss optoins [message #682735 is a reply to message #682732]

Fri, 13 November 2020 00:27

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I agree with Ed, if other tables are only copy of table_A then MViews are the best solution especially when the target tbles are just read-only.
Don't forget to add a Mview log on table_A to enable fast refresh.

Report message to a moderator

Re: table refresh process - discuss optoins [message #682736 is a reply to message #682730]

Fri, 13 November 2020 01:22

John Watson
Messages: 8804
Registered: January 2010
Location: Global Village

Senior Member

Any re-indexing would be insane. Where did you find that advice?

And why do you think update is "more expensive" than delete and insert? It should be quicker. A lot quicker.

As for a materialized view, you need to check out the complexity. You may need to refresh it with COMPLETE, which is the same as truncate and recreate, rather than FAST. Note that FAST refresh does indeed do updates.

And one other thing, please stop saying "record" when you mean "row". It is very unprofessional to misuse those words. I hope you are not going to compound the error by describing "columns" as "fields".

[Updated on: Fri, 13 November 2020 01:25]

Report message to a moderator

Previous Topic:	table record into XML user defined (2 merged)
Next Topic:	Extract words from a string and use them as variables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 27 19:02:49 CDT 2023