We have run the first test with Oracle Partitioning in a real ECC 6.0, of course, has been done in a test environment but actually this system has more data than the production environment because it is copy of production and no archive process is in place there. So the results should be valid.
For this test we have splitted the table BSID by GJAHR (Fiscal Year) in ten partitions. One partition per year since 2008 to 2016 and another spare partition with MAXVALUE.
We have run a report that access the table by the GJAHR field and another test that doesn’t ask by this field and we have taken traces for both. These are the results figures:
|Slowest Execution||Fastest Execution||Average|
|Query with Fiscal Year||Before Partitioning||1162000||401||29080|
|Query without Fiscal Year||Before Partitioning||562206||5386||14623|
As you can see the results are very good. Even accessing by fields are not included in the partition definition the performance is a bit better.
Technical aspect to bear in mind:
Size of the table: 2GB.
Number of Rows: 2.285.000 ROWS.
Time to convert (Force Convcersion): 550 secs.
We have also partitioned the indexes.
After convert the table is needed to update the statistics of table and the indexes.
The next test will be to partition the table by Year and Company Code, as the 95% of the data are concentrated in one company code, we are going to create one partition with the rest of company codes and all years and one partition per year and one company code. so in total we will have 11 partitions. This should improve a lot the queries against the rest od company codes.
Let’s see how it works. I’ll keep you posted.