Re: partitioning an exesting table

On 10 Aug 2005 21:57:52 -0700, fitzjarrell_at_cox.net wrote:

>
>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
>
>18) 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

Hi David,

You seem to overlook the online table redefinition package/feature that came already with 9.0, DBMS_REDEFINITION. It can make the partitioning online on an existing table.

• Kenneth Koenraadt