RE: Partitioning

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 28 Nov 2012 10:07:09 -0500
Message-ID: <039801cdcd7a$0a82f070$1f88d150$_at_rsiz.com>



A strategy that is worth examining is to create a union all view including the existing table and a new table with your desired partitioning.

Then you use the Gorman technique (Scaling to Infinity) of partition exchange to create the contents of the new table one set of dataset_id become dataset_id, status_name partition representations. Then depending on your existing queries, a brief pause of activity to swap the created partition representations into the new table of the union and swap an empty partition in place on the existing table to replace the dataset_id just completed. This strategy requires free space available for about the size of your existing largest dataset_id partition and indexes. I'd also recommend getting an export or other easily reloadable image of the "swapped out" dataset_id partitions one at a time before you drop them as you cycle through.

You can do it without outage if your queries are not made into horrible plans by a straight union view instead of the union all. Even with "union all" there is potential for queries to perform worse than currently during the migration, but given the limitation of not being able to have full simultaneous copies and continued up time it is possible they will be "good enough." Only you can tell that an it would be much more difficult to predict than to measure. Do you have a test system?

One red flag I see just from your column name "status_name": That sounds like a value that will cycle through various changes in status and so you will have all the burdens of allowing row movement between the partitions. An alternative *might* (varying on a complete analysis of your entire system) be a local index on status name within the existing partition structure carefully defining within your applications that a NULL status_name means "I'm all done with this row." IF (and only IF) that is the only column in that particular local index, then only the status values that mean the row is still a candidate for processing will be in that index.

This somewhat controversial technique (controversial because it means assigning an interpreted value to NULL) is often creates extremely good performance.

(Please don't fragment this response to leave out any of my "IF" conditions.)

Good luck. Others may have a superior strategy to propose.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zabair Ahmed
Sent: Wednesday, November 28, 2012 5:24 AM To: oracle-l_at_freelists.org
Subject: Partitioning

Hello
Oracle 11.2.0.3 on Linux

I have an existing table which is already list partitioned on partitioning key dataset-id. This is a large table with 14TB of rows and has already 44000 partitions.  The table is using 1.5TB of storage.

I now want to change this table into a composite list-list partitioned on partitioning keys dataset-id and status-name.

The question is how do I go about doing this with minimal downtime. Also I cannot hold 2 copies of the table due to size and space constraints.

What is the best approach. Any ideas welcomed. I am fairly new to partitioning.

Thanks

Sent from Yahoo! Mail on Android

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 28 2012 - 16:07:09 CET

Original text of this message