Re: oracle partitioning issue

msreejith_at_yahoo.com wrote:
> Hi,
> We have a huge table in oracle (~10+ Gigs of data). This table is
> partitioned by, say, col1 currently.
> But we need to have the table partitioned by col2, since most of the
> queries will involve col2.
> So the issue is: what is the easiest way out to redefine the table
> partitions.
>
> Presuming that an additional amount of space is available (which is
> close to the space that current table data occupies) would like to know
> what kind of effort is required to have all the existing data
> partitioned by the new column (COL2).
>
> And what are the steps we need to undertake to achieve this transition.
> Do we need to copy the existing data (~10Gigs) to another temp table?
> Does it require export/import? All these take lot of time. Is there any
> easier way out?
>
> regards,
> srm
>

If I had to perform this operation, I'd use the following steps:

1. Create a new table (NEW_TABLE) partitioned on COL2, but otherwise mirroring the old table (OLD_TABLE).
2. Copy the data to the new table: INSERT INTO new_table SELECT * FROM old_table;
3. Drop the old table: DROP TABLE old_table;
4. Rename the new table to become the old table: RENAME new_table TO old_table;

You'll have to handle any indexes/constraints/etc.

Another option is to use DBMS_REDEFINITION to perform an online reconfiguration of your schema so as to not interrupt production users.

You could use export/import, but that will probably take longer than the above.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown