Re: Quick question about table partitioning?

Kev,

> The table I have will become very large over time 50+ million rows.
> Would I be better off partitioning the table to get better
> performance? If I do this how much speed can I gain from it?

Partitioning such a large table will improve performance. How much? Who knows? After you've partitioned the table, you'll have to benchmark your application and compare against a benchmark you took before you partitioned.

You can also get improvements without partitioning if you stripe the table across multiple drives. But striping will not perform partition pruning like you can with partitioning and Query Rewrite. Read the Oracle 8i Concepts guide for more information.  

> Also I know I have to re-create the table but can I do an insert into
> select * from x even though there is a CLOB column invloved?

You do not have to re-create the table. You can use the ALTER TABLE EXCHANGE PARTITION command to make a table into a partition. After that, you can make the big partition smaller with the ALTER TABLE SPLIT PARTITION command. This means that you won't have to worry that you have a CLOB field.

> I figure if I have the partitions on the category ids this would help
> the query as it would only have to search on that category within the
> partition. but how would it work if I wanted to search across
> multiple categories at one time?

If your table is partitioned by category_id and you search for a specific category id, then the system will prune the partitions and search the relevant partition for you, thus speeding up your query. But if you parition by category_id, then you'll most likely have an index on that column as well which will speed up your search.

If you query multiple categories at once, then the system may just search all partitions (or the entire table) for you.

HTH,Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================