Chapter 10: Partitioning Tables and Indexes

Creating a range-partitioned table

This example creates a range-partitioned table called fictionsales; it has four partitions, one for each quarter of the year. For best performance, each partition resides on a separate segment:

create table fictionsales
	(store_id int not null,
	order_num int not null,
	date datetime not null)
partition by range (date)
	(q1 values <= (“3/31/2004”) on seg1,
	q2 values <= (“6/30/2004”) on seg2,
	q3 values <= (“9/30/2004”) on seg3,
	q4 values <= (“12/31/2004”) on seg4)

The partitioning-key column is date. The q1 partition resides on seg1, and includes all rows with date values through 3/31/2004. The q2 partition resides on seg2, and includes all rows with date values of 4/1/2004 through 6/30/2004. q3 and q4 are partitioned similarly.

Atempting to insert date values later than “12/31/2004” causes an error, and the insert fails. In this way, the range conditions act as a check constraint on the table by limiting the rows that can be inserted into the table.

To make sure that all values, up to the maximum value for a datatype, are included, use the MAX keyword as the upper bound for the last-created partition. For example:

create table pb_fictionsales
	(store_id int not null,
	order_num int not null,
	date datetime not null)
partition by range (order_num)
	(low values <= (1000) on seg1,
	mid values <= (5000) on seg2,
	high values <= (MAX) on seg3)

Restrictions on partition keys and bound values for range-partitioned tables

Partition bounds must be in ascending order according to the order in which the partitions were created. That is, the upper bound for the second partition must be higher than for the first partition, and so on.

In addition, partition bound values must be compatible with the corresponding partition-key column datatype. For example, varchar is compatible with char. If a bound value has a different datatype than that of its corresponding partition key column, Adaptive Server converts the bound value to the datatype of the partition key column, with these exceptions:

Explicit conversions are not allowed. This example attempts an illegal conversion from varchar to int.

create table employees(emp_names varchar(20)) 
			partition by range(emp_name)
		(p1 values <=(1),
		p2 values <= (10))

Implicit conversions that result in data loss are not allowed. In this example, rounding assumptions may lead to data loss if Adaptive Server converts the bound values to integer values. The partition bounds are not compatible with the partition-key datatype.
```
create table emp_id (id int)
			partition by range(id)
		(p1 values <= (10.5),
		p2 values <= (100.5))
```
In this example, the partitions bounds and the partition key datatype are compatible. Adaptive Server converts the bound values directly to float values. No rounding is required, and conversion is supported.
```
create table id_emp (id float)
			partition by range(id)
		(p1 values <= (10),
		p2 values <= (100))
```

Conversions from nonbinary datatypes to binary datatypes are not allowed. For example. this conversion is not allowed:

create table newemp (name binary)
			partition by range(name)
		(p1 values <= (“Maarten”),
		p2 values <= (“Zymmerman”))

View this book as PDF