Re: Tables Larger When using DBMS_REDEFINITION

From: Brent Day <coloradodba_at_gmail.com>
Date: Fri, 20 Feb 2015 21:57:36 -0700
Message-ID: <CAEz8shzbd2g6QWELsrN4PhHb27iZ74ix1pERcb=jf+FA3Q-jVA_at_mail.gmail.com>

When using dbms_redefintion the idea is to make your load as fast as possible and loading data can be done using PK or ROWID. Loading a very large table without a PK in the interim table in my tests has been significantly faster when dealing with complex primary keys.

DBMS_REDEFINITION is about reorganizing a table, not application design. Once the init "start_redef_table" is completed typically you will do a "sync_interim_table" to propagate the entries capture in the mview log, then use "copy_dependent_objects" which will clone the indexes, triggers, constraints, grants, etc. You can use the register_dependent_object or unregister_dependent_object to determine what you want copy_dependent_objects to clone. The final step is to run the finish_redef_table.

Many options and many reasons to choose PK vs ROWID for the sync. For example in a PeopleSoft application there are no primary keys, only unique indexes (unless you add the PK which can create its own set of issues) so you would need to create the unique index that uniquely identifies the rows but if you have many indexes you have to use the xxxx_dependent_objects api call to remove any unique index you created so you can effectively clone with copy_dependent_objects. This can create quite a bit of extra work when using dbms_redefintion for many tables. PeopleSoft has thousands of tables/database objects and many tables have multiple unique keys. With this configuration would be very time consuming to write some code to automatically redef a large group of tables and trying to determine the unique key that would identify the unique rows.

I hope this helps you understand why someone might create a table without a primary key when using dbms_redef -- its the primary key on the interim table. It doesn't mean the final state will be without a key - of course as pointed out above there are some exceptions like PeopleSoft that doesnt have PK constraints defined.

Brent

On Fri, Feb 20, 2015 at 7:44 PM, Mladen Gogala <dmarc-noreply_at_freelists.org> wrote:

> One question, in addition what Jonathan has said:
> Why would anyone create a table without the primary key? Yes, I am aware
> of the "report tables", but those should be of global temporary variety.
> Creating table without the primary key means not having a way to
> distinguish between rows. That is probably the worst design offense that I
> have ever encountered. If a table doesn't need the primary key, than the
> table itself is probably unnecessary waste of storage and the application
> designer should be water boarded.
>
> --
> Mladen Gogala
> Oracle DBAhttp://mgogala.freehostia.com
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Feb 21 2015 - 05:57:36 CET