SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-46
Creating an Index
a physical location based on its size estimate of the index file and on the available
space in the pool.
In exceptional cases you might want a file to reside on a particular physical
volume. To accomplish this, you can specify a direct volume that is not in any
storage pool, or you can use the PHYSVOL option to specify a particular physical
volume in a pool. For more information about using this feature, see the SQL/MP
Reference Manual and the Storage Management Foundation User’s Guide.
Examples
This example creates an index on the ORDERS table by using the CUSTNUM column
as the key:
>> CREATE INDEX $VOL1.SALES.XORDCUS
+> ON $VOL1.SALES.ORDERS (CUSTNUM)
+> KEYTAG "OC"
+> EXTENT (100,50)
+> BLOCKSIZE 2048
+> MAXEXTENTS 24
+> ICOMPRESS
+> CATALOG $VOL1.SALES;
--- SQL operation complete.
This example creates an index on the ORDERS table in order of the most recent
DELIV_DATE:
>> CREATE INDEX $VOL1.SALES.XORDCUS
+> ON $VOL1.SALES.ORDERS (DELIV_DATE DESC)
+> KEYTAG "LD"
+> EXTENT (100,100)
+> ICOMPRESS
+> CATALOG $VOL1.SALES;
--- SQL operation complete.
This example creates an index on a virtual volume, $VIR1, associated with a SMF
pool. The example specifies that the file reside on the physical volume, $PVOL6:
>> CREATE INDEX $VIR1.SALES.XORDCUS
+> ON $VIR1.SALES.ORDERS (CUSTNUM)
+> |
+> CATALOG $VIR1.SALES
+> PHYSVOL $PVOL6;
--- SQL operation complete.
In the preceding example, $VIR1 must be associated with the storage pool that
contains $PVOL3. When you create an index name on a virtual volume such as $VIR1,
you use the same syntax you would use for volumes not managed by SMF. However,
with the PHYSVOL option, you specify only the volume name (such as $VOL6). SQL
returns an error if you specify a full volume.subvolume.file name with the
PHYSVOL option. If you omit the PHYSVOL option, SMF determines the physical
volume on which the XORDCUS index resides.