Home » RDBMS Server » Performance Tuning » Interface table design question (Oracle, 10g)
Interface table design question [message #521269] Sat, 27 August 2011 16:35 Go to next message
ironman12
Messages: 2
Registered: August 2011
Location: USA
Junior Member
This is more of an 'optimal' design question. We are working on an Integration project where we are integrating Legacy Oracle system with new SAP implementation for the client. Being an Integration project we have heavy data exchange between the 2 systems. For the Interface in question, following are the steps:
1. A program runs in SAP, does data massaging and processes that data and sends to middleware.
2. The middleware picks up the data and loads into a table, say ABC_TAB, in Oracle 11i.
3. A PLSQL program ABC_PKG, selects data out of that table, does more processing and loads the data into 11i application.

The data in ABC_TAB is going to be voluminous (~1 million rows loaded daily). In addition to ABC_PKG, 2 other programs (ABC_PKG1 and ABC_PKG2) also will be accessing the data in ABC_TAB.
As per the Oracle Database and Interface design best practices what would be your suggestion about the following:1. Overtime ABC_TAB will grow larger. Given that it has the right indexes in place which are maintained periodically and flags to identify old and new data, will the growing volume in the table ABC_TAB have the potential to slow down the programs ABC_PKG in future? ABC_PKG will be running 'SELECT' and 'UPDATE' on the table and only look at the daily transactional data.

2. ABC_PKG1 and ABC_PKG2 need to access all the data in the table, old and transactional. Will it be a better idea to archive the table ABC_TAB to ABC_TAB_ARC such that ABC_TAB is only a stage table. Middleware will INSERT data in ABC_TAB. ABC_PKG will use the data for its processing and then dump the processed data in ABC_TAB_ARC. ABC_TAB remains light and ABC_PKG1 and ABC_PKG2 access the archived data in ABC_TAB_ARC for their historical data needs. We will have 2 tables and as many Indexes to maintain. Both ABC_PKG1 and ABC_PKG2 will be running only 'SELECT' on the table.
Re: Interface table design question [message #521270 is a reply to message #521269] Sat, 27 August 2011 18:28 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have somewhat of a similar system running. The main problem we used to have with a previous implementation were select times for the data that needs to be processed daily.

To speed that up we have done the following:

1) Have a "Status" column of some sort in the ABC_TAB for the data that still needs to processed.
2) Index that column of course.
3) Set the column to NULL when you don't need to process that row any further.

Since NULL values are not stored in the index, you have a very small, very fast index that selects the data that you still need to process. And since the index never contains any of the rows that are already processed, a growing ABC_TAB is not going to slow down ABC_PKG.
Re: Interface table design question [message #521283 is a reply to message #521270] Sun, 28 August 2011 01:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There are several possible ways to approach this:

1) ThomasG mentioned one, USE A SPARE INDEX.

Other alternatives are:

2) create the ARC table you mentioned. This implies inserting the same rows twice, once into the "current" table and once into the ARC table. This also implies that you will need to "refresh" your "current" table each day. This can work or it can be problematic depending upon how you design your systems. It all has to do with the philosophy difference between an online design and a warehouse design.

3) you could partition the table by day. With partitioning, you have no need to insert twice and no need to "refresh" current, and no need to manage sparse indexes. Of course you buy in to any percieved additional overhead to managing partitioned tables like creation of future partitions. Then again, Oracle offers some new features in 11g for this so look them up.
Re: Interface table design question [message #521287 is a reply to message #521283] Sun, 28 August 2011 02:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You mention "11i", I guess you mean EBS 11.5.10? If so, I've written similar integration routines using the various interface APIs, particularly TCA.
I found the best way was to use Advanced Queueing to manage ABC_TAB, AQ has all the facilities you need to guarantee that each message is processed exactly once. Invoke ABC_PKG from EBS through either an Alert or a scheduled concurrent program. Then have ABC_PK1 and ABC_PKG2 use the supplied interface APIs to extract what they need from EBS.
That way you maximize the use of built-in EBS functionaility, and your queue table is small, and automatically managed too.
Re: Interface table design question [message #521398 is a reply to message #521287] Mon, 29 August 2011 10:26 Go to previous messageGo to next message
ironman12
Messages: 2
Registered: August 2011
Location: USA
Junior Member
Thanks for the responses.
1. The status column approach sounds good to me and when suggested to my DBA team, they also approved of it. However, he also mentioned that I may have to force the usage of Index using a HINT in my query.
2. Partitioning the table by Day: As per my DBA team, partitioning by day requires constant partition maintenance and hence was not recommended.
3. Partitioning the table by status: That would require row movement each time the status changed (think INSERT and DELETE) and hence was a costly operation.
4. Advanced Queueing: Yes I meant EBS 11.5.10. Sorry for the confusion. I read a bit about Advanced Queueing and and it appears more useful in scenarios when real-time updates are needed to be done by the Interface. We will have data coming and sitting in Oracle which will then be picked, processed and created in 11.5.10 using APIs by a scheduled concurrent program.

Creating ARC table along with the Indexed status column is the way we are planning to take. We will most likely have a Trigger written on the ABC_TAB which will archive all records on insert. Everyday run of the Interface will then purge the old records (STATUS = PROCESSED) from ABC_TAB with an assumption that the data is already archived in ABC_TAB_ARC.
Re: Interface table design question [message #521407 is a reply to message #521269] Mon, 29 August 2011 11:21 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Your DBA team has given you good advice.

That said, I would not accept their responses at face value. If I was the manager of the DBA team that has given you this advice, my response to them would be as follows:

1) OK... I understand what you have said and everything you said is true.

2) BUT... it is part of your job as DBAs to "do partition maintenance", so telling me that we are not going to look at potential significant performance advantages just because you DONT' WANT TO DO YOUR JOB? is not an acceptable answer.

3) SO... here is what we are going to do:

a) you are going to take a good hard look at the partition strategy of partitioning by day to see if there is indeed an advatage to it for us.

b) you are going to research ways to reduce the "persistent maintenance" which you perceive to be an issue. I presume this means looking at pre-allocation of partitions, and also looking at the new oracle feature of interval partitioning that automatically allocates new partitions as needed based on some pattern like oh I don't know, say each new day...

c) one of you is going to write a firm response to explain why using a daily partitioning stragegy is actually a good idea not a bad one. That way I can be sure that we are in fact considering it correctly.

That would be my response. You proceed as you see fit. But I'll tell you this, if I feel I need daily partitions, then I am getting daily partitions, and if my DBA team won't give it to me, I am going to make sure that my managers knows I am not getting what I need and I don't like it, to whit he will make sure their managers know I am not getting what I need. At this point, everyone is unhappy which is sometimes good indicator that we headed in the right direction.

Kevin

[Updated on: Mon, 29 August 2011 11:24]

Report message to a moderator

Previous Topic: Need to improve speed for UPDATE statement
Next Topic: harsh area size and sort area size
Goto Forum:
  


Current Time: Thu Mar 28 19:52:15 CDT 2024