RE: Help Needed regarding partitioning
Hi, I'm currently looking into the same. My idea is to create a new table with the partitions I need, insert the data from the old table, drop the old table and rename the new table to the old name. This will invalidate a lot of stuff, which I still have to find out exactly what, so if somebody has a script to find all the dependencies already.? Anybody have a different/better idea, because the insert may take a long time? Jack -Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 11:54 AMTo: Multiple recipients of list ORACLE-LSubject: Help Needed regarding partitioning Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj
RE: Help Needed regarding partitioning
Jack, Munish First, verify that you have licensed partitioning. Additional cost item. Second, learn about EXCHANGE PARTITION. This is a highly useful command that can be used for a multitude of uses. Create a partitioned table and then exchange your table into it. Extremely fast since it just involves dictionary manipulations. It can be used for other purposes like renaming columns, changing the owner of a table, etc. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 6:15 AM To: Multiple recipients of list ORACLE-L Hi, I'm currently looking into the same. My idea is to create a new table with the partitions I need, insert the data from the old table, drop the old table and rename the new table to the old name. This will invalidate a lot of stuff, which I still have to find out exactly what, so if somebody has a script to find all the dependencies already.? Anybody have a different/better idea, because the insert may take a long time? Jack -Original Message- Sent: Wednesday, June 25, 2003 11:54 AM To: Multiple recipients of list ORACLE-L Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Help Needed regarding partitioning
Munish, innocent question, _why_ do you think partitioning will help query performance? Have you checked asktom site? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 11:54 AMTo: Multiple recipients of list ORACLE-LSubject: Help Needed regarding partitioning Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Help Needed regarding partitioning
Read about exchanging partitions in chapter 17 and especially Converting a Partition View into a Partitioned Table : http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm Even though you may not be using a partitioned view, you can use this technique to create the partitioned table. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 06/25/03 05:54AM Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Help Needed regarding partitioning
That is the greatest RTFM answer that I've ever seen! Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 9:33 AM To: Multiple recipients of list ORACLE-L Read about exchanging partitions in chapter 17 and especially Converting a Partition View into a Partitioned Table : http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm Even though you may not be using a partitioned view, you can use this technique to create the partitioned table. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 06/25/03 05:54AM Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Help Needed regarding partitioning
You first create an empty partitioned table with the same structure as the original table and then use "exchange partition" to exchange partitions with the original table. That way, your original table will end up having a single (empy) partition and the new table will have a full partiton. Now, split the partitions in the new table, rename the old one, rebuild the indexes, recreate grants and presto, you're ready to go. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 5:54 AMTo: Multiple recipients of list ORACLE-LSubject: Help Needed regarding partitioning Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj
RE: Help Needed regarding partitioning
Thanks for mentioning it Just tried a little test and worked like a charm. alter table part2 exchange partition JUNE2003 with table test_part1 Jacob A. van Zanen -Original Message- Sent: Wednesday, June 25, 2003 4:14 PM To: Multiple recipients of list ORACLE-L Jack, Munish First, verify that you have licensed partitioning. Additional cost item. Second, learn about EXCHANGE PARTITION. This is a highly useful command that can be used for a multitude of uses. Create a partitioned table and then exchange your table into it. Extremely fast since it just involves dictionary manipulations. It can be used for other purposes like renaming columns, changing the owner of a table, etc. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 6:15 AM To: Multiple recipients of list ORACLE-L Hi, I'm currently looking into the same. My idea is to create a new table with the partitions I need, insert the data from the old table, drop the old table and rename the new table to the old name. This will invalidate a lot of stuff, which I still have to find out exactly what, so if somebody has a script to find all the dependencies already.? Anybody have a different/better idea, because the insert may take a long time? Jack -Original Message- Sent: Wednesday, June 25, 2003 11:54 AM To: Multiple recipients of list ORACLE-L Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
