Re: Quick question about table partitioning?

Thanks for the advice. I really didnt want to have to move the data!

Thanks,

Kev.-

On Wed, 18 Apr 2001 12:52:13 GMT, Brian Peasland <peasland_at_usgs.gov> wrote:

>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!
>========================================
Received on Wed Apr 18 2001 - 19:09:54 CDT