FRAGMENT BY Clause for Indexes

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

FRAGMENT BY Clause for Indexes

Use the FRAGMENT BY clause to create a detached index and to define its fragmentation strategy across dbspaces, partitions (IDS), or dbslices (XPS).

FRAGMENT BY Clause for Indexes:
 
|--+-FRAGMENT BY---------+-------------------------------------->
   |  (1)                |
   '--------PARTITION BY-'
 
>--+-EXPRESSION--(--| Expression List |--,--| REMAINDER Clause |--)-+--|
   |                 .-,------.                                     |
   |  (2)            V        |                                     |
   '------+-HASH--(----column-+--)--IN--+-dbslice---------+-+-------'
          |                             |   .-,-------.   | |
          |                             |   V         |   | |
          |                             '-(---dbspace-+-)-' |
          |            .-,------.                           |
          |            V        |                           |
          '-HYBRID--(----column-+--)--| EXPRESSION Clause |-'
 
Expression List:
 
   .-,---------------------------------------------------.
   V                                                     |
|----+------------------------+--(--expr--)--IN--dbspace-+------|
     |  (1)                   |
     '--------PARTITION--part-'
 
REMAINDER Clause:
 
|--+------------------------+--+-REMAINDER--+--IN--dbspace------|
   |  (1)                   |  '-(--expr--)-'
   '--------PARTITION--part-'
 
EXPRESSION Clause:
 
               .-,-----------------------------.
               V                               |
|--EXPRESSION----expr--IN--+-dbslice---------+-+---------------->
                           +-dbspace---------+
                           |   .-,-------.   |
                           |   V         |   |
                           '-(---dbspace-+-)-'
 
>--,--+-REMAINDER-+--IN--+-dbslice---------+--------------------|
      '-expr------'      +-dbspace---------+
                         |   .-,-------.   |
                         |   V         |   |
                         '-(---dbspace-+-)-'

Notes:

Dynamic Server only
Extended Parallel Server only

Element	Description	Restrictions	Syntax
column	Column on which to fragment the index	Must exist in the current table	Identifier
dbslice	Dbslice storing all the index fragments	Must exist	Identifier
dbspace	Dbspace in which to store the index fragment that expr defines	Must exist. Include no more than 2,048 dbspace names	Identifier
expr	Expression defining which index keys each fragment stores	See Restrictions on Fragmentation Expressions.	Expression; Condition
part	Name that you declare here for a partition of a dbspace	Required for any partition in the same dbspace as another partition of the same index	Identifier

Here the IN keyword introduces the name of a storage space where an index fragment is to be stored. If you list multiple dbspace names after the IN keyword, use parentheses to delimit the dbspace list. The parentheses around the list of fragment definitions that follow the EXPRESSION keyword are optional.