RE: Partitioning
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <roon987@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Nov 2012 10:07:09 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Zabair Ahmed
Sent: Wednesday, November 28, 2012 5:24 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
