| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle partitioning for Peoplesoft Financials
Bob,
We're in the process of partitioning huge tables in the 3rd Party Financials system (IFAS) that AISD uses. It's not Peoplesoft, but I think the approach would be the same. We did get approval from the app's vendor (SunGard Bi-Tech) that partitioning wouldn't invalidate our support agreement.
Of course you start with the biggest tables that cause performance degradation as they grow beyond the application's scalability. We then ask the users what processes or reports they run against those tables that are slow and look at the SQL to determine likely candidate partition keys. We then partition the table in our Dev database and have the users test their reports. We find that partitioning can work better than an index on the same column(s). The Optimizer really pays attention to opportunities for Partiton Pruning.
It looks like Range Partitioning - by month - on a date column in each table gives us the best performance improvement. We hope to be able to eventually put old partitions in read only tablespaces, which will reduce the time and space required for our nightly backups. Compression of those little-used partitions and their local index segments should help, too.
Since some of the app's utilities check for the existence of specifically-named tables and indexes, we keep the same names, but make all indexes Local, which helps tremendously for Inserts and Deletes in the current "hot" fiscal periods. Making unique indexes local means we sometimes have to add the partition key column to the unique index, but, overall, the extra space required is not significant.
Hope this helps.
Jack C. Applewhite - Database Administrator Austin (Texas) Independent School District 512.414.9715 (wk) / 512.935.5929 (pager)
I'm OK, you're OK - in small doses. -- Introverts' Motto
Bob Robert <mssql_2002_at_yahoo.com>
Sent by: oracle-l-bounce_at_freelists.org
06/07/2006 09:07 AM
Please respond to
mssql_2002_at_yahoo.com
To
oracle-l_at_freelists.org
cc
Subject
Oracle partitioning for Peoplesoft Financials
Gurus,
Did any one implement oracle 9i table partitioning in Peoplesoft Financials 8.8?
I have identified few tables for partitioning. They are PS_LEDGER, PS_JRNL_LN, PS_JRNL_HEADER and PS_LEDGER_BUDG. I am planning to use list partition by using business_unit or range partition by using fiscal_year.
Could you please share your experience for the following questions?
1) What are the tables you have implemented table partitioning? 2) What is the partition key and partition type? 3) Did you build any additional indexes? 4) Did you come across any issues after implementation? 5) Any tips, you could suggest for me.
Thanks,
Bob
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 07 2006 - 09:34:51 CDT
 |
 |