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, P1 and P2, which are two adjacent partitions of a range-partitioned table. The partition bound for P1 is (6,7,3) and the partition bound for P2 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 P1 (6,9,x) is greater than (6,7,x), and for P2 (6,x,x) is less than (7,x,x). Hence, the row is inserted into partition P2.

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

ITPub博客

Range Partitioning 中VALUES LESS THAN 算法

最新文章