Oracle-L: RE: Non-partitioned table to Hash Partitioned table

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Fri, 01 Mar 2002 05:58:31 -0800
Message-ID: <F001.0041CAA1.20020301055831@fatcity.com>

Ron:

Thanks for the suggestion. I have looked into this, and found that its very slow for hash partitions. It works well for range partitions. But, with hash parititions a row may move more than once (each time you add a partition, the row may move) And since I want 8 hash partitions, this is very slow.

the first time you add a partition, you move 1/2 the rows the second time, you move AT LEAST 1/3 of the rows (as much as 1/2 of the rows)
the third time, you move AT LEAST 1/4 of the rows (as much as 1/2 of the rows)
etc all of the way to adding 7 partitions for a total of 8

kevin

-----Original Message-----
Sent: Thursday, February 28, 2002 1:44 PM To: Multiple recipients of list ORACLE-L

Kevin,
how about using the ALTER TABLE EXCHANGE command to move the info from a non-partitioned to a partitioned table. ROR mª¿ªm

>>> ktoepke_at_trilegiant.com 02/28/02 11:28AM >>> Hello!

I am trying to figure out the best way to convert a non-partitioned table
with approx 20 million rows into a hash-partitioned table. This should be
done with minimal down-time. This will be in an Oracle 9i environment done
at a time when only SELECTs are occuring on the table.

The best way I can figure is to create a second table and copy the data
over; create the indexes; and rename the tables.

I would appreciate any hints on what section of TFM to read.

Kevin Toepke
ktoepke_at_trilegiant.com

The information in this electronic mail message is Trilegiant Confidential
and may be legally privileged. It is intended solely for the addressee(s).
Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on
it is prohibited and may be unlawful.

The sender believes that this E-mail and any attachments were free of any
virus, worm, Trojan horse, and/or malicious code when sent. This message and
its attachments could have been infected during transmission. By reading the
message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and
other defects. Trilegiant Corporation is not liable for any loss or damage
arising in any way from this message or its attachments.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Toepke, Kevin M
INET: ktoepke_at_trilegiant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Ron Rogers
INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------