| Interface table design question [message #521269] |
Sat, 27 August 2011 16:35  |
 |
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  |
ThomasG
Messages: 3212
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 #521287 is a reply to message #521283] |
Sun, 28 August 2011 02:29 |
John Watson
Messages: 8960
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 #521407 is a reply to message #521269] |
Mon, 29 August 2011 11:21 |
 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
