Range-&-Hash-partitioning tables partitioning key columns - Oracle Indexes and Partitioned Tables

Discussion

• Nirja Shah   -Posted on 29 Sep 15
  - For range- and hash-partitioned tables, you can specify up to 16 partitioning key columns.
  
  - Multicolumn partitioning should be used when the partitioning key is composed of several columns and subsequent columns define a higher granularity than the preceding ones.
  
  - The most common scenario is a decomposed DATE or TIMESTAMP key, consisting of separated columns, for year, month, and day.
  
  - In evaluating multicolumn partitioning keys, the database uses the second value only if the first value cannot uniquely identify a single target partition, and uses the third value only if the first and second do not determine the correct partition, and so forth.
  
  - A value cannot determine the correct partition only when a partition bound exactly matches that value and the same bound is defined for the next partition.
  
  - The nth column will therefore be investigated only when all previous (n-1) values of the multicolumn key exactly match the (n-1) bounds of a partition.
  
  - A second column, for example, will be evaluated only if the first column exactly matches the partition boundary value.
  
  - If all column values exactly match all of the bound values for a partition, the database will determine that the row does not fit in this partition and will consider the next partition for a match.

➨ Post your comment / Share knowledge