Huge Tables

[rmiozzo20]

Hi all,

We went live 23 months ago and have been facing some performance problems for the last 6 months.
We are not allowed to purge tables because of country localization and business requirement.

Some of our huge database tables:

Table / Records
-------/ ----------
F3111T / 44,302,962
F3102 / 43,244,711
F4111 / 43,184,898
F39120W/ 41,759,244
F3111 / 29,071,557
F30026 / 19,215,399
F0911 / 17,430,645

We are planning to install Oracle Partitioning Table in order to improve performance. Any suggestions? Comments?

TIA

Regina

Enterprise One XE, Update 7, SP 19.1
HP-UX 11.0/Intel Win2K
Oracle 8i - 8.1.7

 

[TAV]

OK I'm not going to go into the obvious integrity issue presented by the F3111-F3111T discrepency other than to note it as an issue. You should not have this.

I will however comment on what huge is since it is all relative and without purging this site would be well on it's way to being the biggest I have worked on. Our F0911 is currently 168,060,491 (other have had over 500M) and we have only been live 1.5 years financials, Plants started going live a year ago and we are really only half way live now, so yours having 23 months is not huge in comparison.

Now again since the definition is relative maybe it is huge based on your resources and budget and that is understandable. So if this is becoming an issue you would normally need to purge the files as this is the way you regain performance in JDE.

But you claim you can not purge so I think you need to get more assistance in clarifying why with some very good consultants having a come to Jesus meeting wtih the business or something. Not sure what the business requirements are you have but it can always be worked around by having the archival and production environemnts viewed through joins if you created a purge process or bought a product that helps with this.

Then again I'm no Oracle guru so maybe you will find a way to get around table size issues without purging and doing joins and I will be interested to see any response on how this can be done since I have never seen any of the Oracle DBA's I worked with come up with anything succesful yet other than purging at a certain point.

Things I would suggest are:
First thing check with JDE for any options they suggest
Ask Oracle for their view (someone experienced with JDE)
--- You probably did these already I am sure ---
Gather any feedback from JDELIST and include it

Present your findings which likely indicate needing to purge old data at some point to argue for getting experienced business side consultants to review and advise on your alternatives to keeping data in JDE forever as this is not a good option for you long term related to performance.

That's my two cents. .02

Hope that helps.

Tom

 

[rmiozzo20]

Tom,
You´re right. It´s not huge in comparision to 168,060,491 records in F0911.

Maybe huge it´s not the rigth word. Sorry, I´m not a native English speaker.

The problem is brazilian low requires at least five years of data for fiscal auditing, PS has no archiving strategy, there is no Brazil localized archiving 3rd party tool and our business requires 24 month background. And we are 5 branch/plant, planning to implement other 2 in a couple of months.

I agree our site must be improved( we are doing it) but business and database are going faster than suppliers. Then, we have to find some way of handling with this situation.

Oracle consultants´ suggestion is Table Partitioning. However, result depends on the right implementation on each table.

Thanks a lot,

Regina

Enterprise One XE, Update 7, SP 19.1
HP-UX 11.0/Intel Win2K
Oracle 8i - 8.1.7
--------------------------

 

[mystra]

Hi

I have implemented Oracle Table Partitioning on F0911. So far we are quite happy with the performance. Composite partitioning was implemented on the transactional date as well document number field.

 

[rmiozzo20]

Thank you for your reply.

Regina

 

[ijc]

Regina,

We previoulsy used partioning but had to remove it due to the license costs. (It does work with JDE with no issues).

We now have a separate copy of F42119 (our large table) created with a different Oracle user/jde datasource. (We copied data to this and deleted from proddta)

We then simply created a new "archive" user and OCM mapped the user to it. This can then be used to view the data when required.

This allows for the peformance we require in production while keeping the data as required by law.

/ Ian

 

[michel]

so what have you done in Upgrades? Did you convert your archive tables into the new format? Are your "view" applications still work with the upgraded archive tables?

 

[ijc]

We haven't (yet) upgraded from Xe (next year hopefully) but it shouldn't be a problem to run the upgrade progrom over these tables

 

[nevans]

I'm trying to have our technical people consider table partitioning. We have been live for 18 months and our F0911 has 30,000,000 records in it. 60% of those records are of batch type T. Purging isn't an option for us either. Can you tell me more about how you came about the decision to partition the table rather than to split the table out to an archive. How did you arrive at the decision to partition on the fields that you did? Any information on partitioning at all would be helpful.

 

[winterc]

I am not sure what DB you are using. Please state your systems in the future. I know that with SQL Server you can partition a table or a set of tables into a separate partition. Basically it would be an additional file. Doing this would only be of benefit if the file can be moved into a separate array and would be even better if it was a separate channel. I should be fairly easy to do. I have not done this as the archiving works for me. However I did not just archive the F0911. I put the F0911P on a separate database and had a search form built to query both tables. Our users rarely look at the old data. By separating the F0911p my databases are smaller and faster to backup and recover. I also only backup F0911P once and a while.

Good luck!

WIN\SQL 2000, B8.9 SP2, JAS 5.0.2