Re: Online redef - is there a better way?
Date: Sat, 14 Feb 2009 08:21:56 -0800 (PST)
Message-ID: <961c9210-0053-46a3-bd4c-32193ee7bdce_at_w24g2000prd.googlegroups.com>
On Feb 14, 8:32 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
> We have a requirement to move some of the table from normal to
> partitioned.
> I tested the oracle provided online redef way by simulating select+dml
> load on the table.
>
> I tried using
> exec DBMS_REDEFINITION.START_REDEF_TABLE
> ('OWNER1','SOURCE1','SOURCE1_TEMP$')
> In this SOURCE1 is non partition table and SOURCE1_TEMP$ is hash
> partition table on one of the keys.
>
> This finished rather quickly - in 1 hr .. the no of rows in source1
> table is 40 million.
>
> After the above finished i created the necessary indexes/constraints.
> -- this took 1 more hour. In parallel select and DML (upload) load was
> running.
>
> After index+constraints -- i used: exec
> DBMS_REDEFINITION.SYNC_INTERIM_TABLE('OWNER1','SOURCE1','SOURCE1_TEMP
> $') -- this ran almost like for 8 hrs before i aborted this.
>
> It seems online redef uses Mviews to refresh the data.
>
> My question is - Is there a better way to move non-part tables to part
> ways in a real OLTP environment or any extra precaution to make
> DBMS_REDEFINITION work seamless?
>
> -- Sachin
If you need to leave the table available them dbms_redefinition is probably you best choice. Stopping the load jobs during the redefinition process should greatly reduce the time it takes to apply the DML changes that took place during the redefinition phase (the sync) to the new version of the table so that it can become the table.
If you need to run the loads but do not need to be able to query the data for a while then an alternate approach if you can get just a couple minutes of no loading might be to rename the table, build the new partition table, restart the loads, then run a task to insert the existing data. Potentially the data movement could be done so that only one partition is affected per data movement task. This would allow moving data that you need access to now before dealing with data that is only needed for month-end, quarterly, or year to date processing that will not run for a while.
HTH -- Mark D Powell -- Received on Sat Feb 14 2009 - 10:21:56 CST