change table to partitioned one

[ija]

Hi,

Could anybody briefly explain, what needs to be done in order to make a table partitioned?
I will not actually do the changes, so no details are needed. I only need to understand the consequences...

We have a few very big tables with data from last 3 years. We would like to create partitions for each year (total of 5 years - legal restrictions). Only the latest data should reside on a fast device. The other four partitions should be on something slow, maybe a tape.

I already know, that we have to re-create the table.
What about the indexes? Shall we change them or not? Based on what should we decide? And what about primary keys?
We use RULE optimization. Is this a problem?
We cannot change hints used when application generates queries, we only can add another "where" condition. Will it be enough?

Thanks a lot.

 

[karluk]

I'm afraid that Oracle won't allow putting some partitions on tape. All the partitions of a table must be on disk, although you will be able to put individual partitions into separate tablespaces, which allows you to place partitions on different physical drives.

It's desirable, but not required, to include indexes in your partitioning strategy. Using partitioning, indexes can be local or global. "Local" means that an index partion contains keys of only a single table partition. I'm fairly sure that using local indexes can improve performance and simplify partition maintenance. When you are ready to purge a partition of your table, you can drop the partition and affect only a single partition of your local indexes.

The main problem with using rule optimization is that it makes it impossible to take advantage of partitioning when choosing the quickest access path. Rule based optimization was written before partitioning and has not been updated with the new features. Probably your queries will run the exact same way they did before implementing partitioning, but if you are expecting any performance benefits from partitioning, you are most likely out of luck.