Re: Tables Larger When using DBMS_REDEFINITION
- From: Brent Day <coloradodba@xxxxxxxxx>
- To: dmarc-noreply@xxxxxxxxxxxxx
- Date: Fri, 20 Feb 2015 21:57:36 -0700
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@xxxxxxxxxxxxx>
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
>
>
Other related posts:
