BigP, You stated that you would like to limit the number of rows in a partition. The partitioning option uses a "range" function on a column to determine what partition to place to data into. If you do not have a column that is used in your where clause, you are going to have a difficult time determining what data is placed in what partition. The biggest performance is gained when Oracle can eliminate the partitions that do not match the where clause and then applies the search criteria to a small subset of data, ie: a partition. As an example, if all of your data had a date field that was used in the where clause you could partition the data by range on that column by year or year,month and have the data divided into a years worth of data or a months worth of data. When oracle is requested to search for data it would eliminate all of the partitions that do not match the date in the where clause, thus eliminating the majority of the partitions if not all but one partition. I would suggest as others have that you read and understand the workings of partitioning and the possible benefits gained. Ron ROR m���m
>>> [EMAIL PROTECTED] 05/31/02 10:33AM >>> BigP - I agree with Dick that you will need to create your new partitioned table and copy the rows from your current table into it. Given your questions, before you charge into partitioning, carefully study the ways partitioning can increase your performance. It isn't just some magic pixie dust that simply makes everything faster. Carefully study how the table is accessed. For example, I applied partitioning to two instances. For one, the performance gain was tremendous. Queries that had taken more than 2 minutes to complete dropped to under 10 seconds. You could hear the users cheering. On another instance, there was no detectable performance gain and I ended up undoing the partitioning. Fortunately Oracle is pretty lenient in terms of letting you try the feature to make sure it will deliver performance worth the licensing fee. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Thursday, May 30, 2002 7:59 PM To: Multiple recipients of list ORACLE-L Hi All , We are thinking of converting one of huge table in to partition table . What is best way to achieve this ? Is there any alter table clause that can do this or I will have to export ..recreate table with partition option and then import . Also how can I mentiod that partition should have only 100000 rows . For example after each 100000 rows add another partition ? If I have 10000000 rows in the table , should I expect some performance gain out of this Thanks , BigP -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [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).
