Hi Amit,
You haven't mentioned which versions of EBS and the database you're using,
but here are some high-level thoughts:
- Custom partitioning in EBS is not uncommon for purposes like yours
(query performance and information lifecycle management). The EBS tech
team at Oracle have released a few resources on the topic over the years,
including "Using Database Partitioning with Oracle E-Business Suite" (Doc
ID 554539.1), discussed here
<https://blogs.oracle.com/ebstech/updated%3a-database-partitioning-with-ebs-whitepaper>.
The MAA doc for EBS also discusses topics like advanced compression, which
may be useful to you.
- Keep licensing in mind when evaluating possible solutions, since some
Enterprise Edition features require additional licenses. Although the EBS
db includes partitioning, this is a limited license for the default EBS
setup. Any partitioning scheme you create beyond the out-of-box partitions
will require a full license for the partitioning option.
- Changing the table structures to accommodate partitioning,
compression, and TTS is a customization of your EBS environment, and should
be documented and tracked as such in your organization. This will be
important when interacting with Oracle Support on performance issues, and
in the rare circumstances when an EBS patch performs DDL on the tables,
since the table definitions in the ODF files will not match the reality of
your deployed system.
- You'll want to test your changes quite a bit, *especially* in data
archival scenarios. It's difficult to provide any specific product-level
advice, since every org's EBS workload is different, so make sure you have
a robust set of tests. :)
On Tue, Jul 20, 2021 at 7:47 AM Amit Saroha <eramitsaroha@xxxxxxxxx> wrote:
Dear Listers,
Could you share some thoughts, please?
Best Regards,
AMIT
On Fri, Jul 16, 2021 at 5:24 PM Amit Saroha <eramitsaroha@xxxxxxxxx>
wrote:
Dear All,
We want to make use of transportable tablespaces and partitioning in our
Oracle EBS environment to archive more than 7 years of old data and improve
the performance in some areas. Please let me know if it makes sense to you.
We don't want to buy third-party products to achieve our end goal.
I am afraid that some processes where the optimizer is choosing FTS can
impact because of this. I am also not sure about indexes and views
dependent on tables as well.
Kindly request you to provide me some insights on how to proceed with
this analysis.
Thank you in advance for your help.
Best Regards,
AMIT