Re: partitioning an exesting table

Eugene wrote:
> David,
> I understand that. I phoned vendor's DBA and asked about it. He told me
> that it is OK with them. And, of course, I will do it in test first
>
> Eugene

After you get vendor approval in writing I'd also suggest you look into the following areas you seem to have missed:

• Stored procedures, packages and functions which depend on this table
• Views which depend upon this table
• Foreign key constraints using this table as a parent
• Foreign key constraints on this table using others as parents
• Triggers on other tables which may access this table

All of the above mentioned items will be invalidated when this table is partitioned as several tasks need completion after you take an export of this schema (do NOT even THINK of executing any tasks in the list below before you have a current export of the schema as you may need it for recovery operations should this project fail). The list is:

1. Renaming the existing table to preserve the data and provide a fallback should the partitioning fail
2. Dropping all foreign key constraints on the original table
3. Creating the new, partitioned table
4. Loading the table from the saved copy
5. Dropping the primary key on the old, renamed table
6. Creating the primary key on the partitioned table
7. Creating all foreign key constraints on the new table
8. Editing the source for triggers on the old table to restore the original table name
9. Dropping the old triggers
10. Creating the new triggers
11. Editing any other triggers, packages, procedures and functions accessing this table
12. Recreating the objects in item 11
13. Dropping the additional indexes on the old table
14. Creating those indexes on the new table
15. Creating local prefixed indexes on the partitioned table
16. Dropping the old version of the table
17. Computing statistics on your partitioned table and associated indexes

It may be that the presence of statistics will adversely affect the performance of your application as the code from the vendor uses the RBO instead of the CBO. You have wisely chosen to perform this 'surgery' on a test instance before attempting it on production data. Before you proceed against production I would direct your application to the test instance and let it run, to reveal any performance problems created by the use of the CBO, presuming such a test is possible. Only when you are satisfied partitioning, and the related changes to your schema, actually improve your situation should you seriously consider performing the same tasks on production.

This is not a simple procedure; it requires time and effort to ensure the job is completed properly. With careful planning this procedure should offer no surprises. I believe, however, it is far more complicated than you'd originally expected.

David Fitzjarrell Received on Wed Aug 10 2005 - 23:57:52 CDT