RE: partition tables

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

>>> DWILLIAMS_at_LIFETOUCH.COM 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.
dwilliams_at_lifetouch.com

-----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: DWILLIAMS_at_LIFETOUCH.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
--------------------------------------------------------------------
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).