Introduction to NonStop SQL/MP

Physical Database Structure
NonStop SQL Architecture
3–8 113425 Tandem Computers Incorporated
Figure 3-3. A Partitioned Table
011
PARTLOC Table
(Partition 1)
LOC PART QTY
A10
A21
•••
A94
2001
1403
•••
3302
1100
21
•••
200
First Key = "A00"
PARTLOC Table
(Partition 2 )
LOC PART QTY
G11
G68
•••
G98
2002
6402
•••
5502
20
6032
•••
136
First Key = "G00"
$WHS1 (New York)
$WHS2 (Los Angeles)
PARTLOC Table
(Partition 3 )
LOC PART QTY
P11
P12
•••
P87
2002
2003
•••
3103
200
- 100
•••
300
First Key = "P00"
$WHS3 (Montreal)
CREATE TABLE \NY.$WHS1.INVENT.PARTLOC
( LOC_CODE CHARACTER (3) NO DEFAULT NOT NULL ,
PARTNUM NUMERIC (4) UNSIGNED
NO DEFAULT NOT NULL ,
QTY_ON_HAND NUMERIC (7) NO DEFAULT NOT NULL ,
PRIMARY KEY (LOC_CODE, PARTNUM)
)
CATALOG \NY.$WHS1.INVENT
PARTITION ( \LA.$WHS2.INVENT.PARTLOC
CATALOG \LA.$WHS2.INVENT
FIRST KEY ( "G00" , 0000 )
,
\MONT.$WHS3.INVENT.PARTLOC
CATALOG \MONT.$WHS3.INVENT
FIRST KEY ( "P00" , 0000 )
) ;
"A00" assumed
first key value for
LOC_CODE.
New York
Los Angeles
Montreal
The first partition, located in New York, contains all rows with a location code equal to
or greater than “A00” but less than “G00.” The second partition contains location
codes equal to or greater than “G00” and less than “P00.” The third partition has
location codes equal to or greater than “P00.”
NonStop SQL/MP handles partitions for you automatically. You insert, retrieve, or
update data in a partitioned table just as if the table were not partitioned. For
example, you update a row in the partition on $WHS1, shown in Figure 3-3, like this: