Re: oracle partitioning issue

From: Jim Kennedy <jim>
Date: Fri, 19 May 2006 06:28:02 -0700
Message-ID: <4_SdnQaIzvPUWvDZRVn-gw@comcast.com>

<msreejith_at_yahoo.com> wrote in message
news:1148039856.535675.308010_at_u72g2000cwu.googlegroups.com...
> 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
>

How about this?

1. Create a 2nd table with the partitioning you want. (on col2)
2. Rename table 1.
3. Create a view of the union of table 1 and table 2 and call it the

original table 1 table name.
4. Transfer the data to table 2 by inserting into table2 from table 1 and only commit at the end. Make each run be a whole partition on col2.

Jim Received on Fri May 19 2006 - 08:28:02 CDT