Range Partitioning 中VALUES LESS THAN 算法

dsi 302 lession 8

The VALUES LESS THAN clause applies to all of the partitioning columns collectively. Therefore, the value in the partition key’s first column can be equal to the first column of the partition bound. Once an inequality is found, subsequent values in the partition key are ignored. The ignored values may therefore be greater than values specified in the partition bound. Comparisons involving NULL values always sort the NULL value greater. This means that rows containing a NULL value for the partition key will always be placed in the highest partition (provided it has been delimited by the keyword MAXVALUE).

 

From the Oracle8i Concepts Manual , page 11-22: “ In mathematical terms, for vectors V1 and V2 which contain the same number of values, Vx[i] is the ith value in Vx.

Assuming that V1[i] and V2[i] have compatible data types:

V1 = V2 if and only if V1[i] = V2[i] for all i

V1 < V2 if and only if V1[i] = V2[i] for all i < n and V1[n] < V2[n] for some n

V1 > V2 if and only if V1[i] = V2[i] for all i < n and V1[n] > V2[n] for some n. ”

 

To show an example, assume that you have two partitions, P 1  and P 2 , which are two adjacent partitions of a range-partitioned table. The partition bound for P 1 is (6,7,3) and the partition bound for P 2 is (7,5,10).

When inserting a row, the Oracle server evaluates vectors from left to right (following ANSI/ISO SQL92 standard vector ordering rules), comparing values in corresponding positions. Once a differing value is found, then subsequent values are ignored.

If a partition key of (6,9,11) were to be inserted into this table, the evaluation would show that for P 1  (6,9,x) is greater than (6,7,x), and for P 2  (6,x,x) is less than (7,x,x). Hence, the row is inserted into partition P 2 .

A partition key of (7,3,15) is also placed in partition P 2  because (7,x,x) is greater than (6,x,x) (hence ruling out P 1 ), and (7,3,x) is less than (7,5,x).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16158219/viewspace-617524/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16158219/viewspace-617524/