Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Which Partitioning Technique Do I Choose?

Choose range partitioning when the partitioning keys have distinct ranges that can be easily defined and do not vary dramatically. The problem with range partitioning is trying to determine what the range specifications are. The DBA should select a range of values that evenly divides the data among the individual tablespace partitions. Dates are excellent candidates for range partitioning keys because they allow the previously discussed rolling window operations to occur. If range partitioning will result in partitions that vary dramatically in size because of unequal key distribution, the DBA should consider other partitioning techniques.

If the DBA wants to use partitioning for performance and manageability but there are no columns that have distinct key ranges, the hash partitioning technique provides an excellent alternative. Since the rows are mapped based on a hashing algorithm, the higher the cardinality (the number of different values) the partitioning key contains, the more evenly the data will be divided among the different partitions. Many DBAs create an artificial series of numbers that are used as input to the hashing algorithm. These unique values ensure that the hashing algorithm evenly divides the rows among the tablespace partitions.

If you need specific control over the mapping of rows to tablespace partitions, use the list partitioning technique. List partitioning allows the DBA to logically group the data among the different partitions. Sales region would be an excellent example of a column that would lend itself to the list partitioning technique. The DBA would be able to divide the data among the partitions based on the company’s different sales regions.
 

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.