Range IN Clause

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > CREATE TABLE >

Range IN Clause

Use the IN clause to specify the storage spaces in which to distribute the data.

Range IN Clause:
 
       .-,---------------------.
       V                       |
|--IN----+-dbslice-----------+-+-------------------------------->
         |    .-,-------.    |
         |    V         |    |
         '-(----dbspace-+--)-'
 
>--+-------------------------------------+----------------------|
   '-REMAINDER IN--+-dbslice-----------+-'
                   |    .-,-------.    |
                   |    V         |    |
                   '-(----dbspace-+--)-'

Element	Description	Restrictions	Syntax
dbslice	Dbslice that contains the dbspaces to store table fragments	Must exist	Identifier, p. Identifier
dbspace	Dbspace to store the table fragment	Must exist	Identifier, p. Identifier

If you specify more than one dbslice, including a remainder dbslice, each dbslice must contain the same number of dbspaces.

Unless you are specifying the dbspace in the REMAINDER option, the minimum number of dbspaces that you can specify is two. The maximum number of dbspaces that you can specify is 2,048.

When you use a range-fragmentation method, the number of integer values between the minimum and maximum specified values must be equal to or greater than the number of storage spaces specified, so that the database server can allocate non-overlapping contiguous ranges across the dbspaces. For example, the following code returns an error, because the allocations for the range cannot be distributed across all specified dbspaces:

CREATE TABLE Tab1 (Col1 INT...) 
   FRAGMENT BY RANGE (Col1 MIN 5 MAX 7)
      IN db1, db2, db3, db4, db5, db6 -- returns an error

The error for this example occurs because the specified range contains three values (5, 6, and 7), but six dbspaces are specified; three values cannot be distributed across six dbspaces.