Example: HELP INDEX on Table Columns
The following example returns information about the specified index or indexes defined on the table columns named cname_1, cname_2, and cname_3.
HELP INDEX tname (cname_1, cname_2, cname_3);
Example: HELP INDEX on a Table
The following request and response show that one index is defined for the Department table.
HELP INDEX Personnel.Department; Primary or Unique Secondary Column Names Index Id Approximate Count ------ --------- --------------- -------- ----------------- Y P DeptNo 1 7
Example: Partitioned Primary Index
This example uses the following table definition.
CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHARACTER(1) CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_orderpriority CHARACTER(21), o_clerk CHARACTER(16), o_shippriority INTEGER, o_comment VARCHAR(79)) PRIMARY INDEX OrdPI (o_orderkey) PARTITION BY RANGE_N(o_orderdate BETWEEN DATE '1992-01-01' AND DATE '1998-12-31' EACH INTERVAL '1' MONTH) UNIQUE INDEX OrdUSI (o_orderkey) INDEX Ordx1 (o_orderdate) INDEX Ordx2 (o_orderdate) ORDER BY VALUES (o_orderdate) INDEX Ordx3 (o_custkey, o_orderdate) ORDER BY VALUES (o_orderdate) INDEX Ordx4 (o_custkey, o_orderdate) ORDER BY HASH (o_orderdate) INDEX Ordx5 ALL (o_custkey, o_orderstatus) ORDER BY VALUES (o_custkey) INDEX Ordx6 ALL (o_custkey, o_orderstatus) ORDER BY HASH (o_custkey) INDEX Ordx7 ALL (o_custkey) ORDER BY VALUES (o_custkey) INDEX Ordx8 ALL (o_custkey) ORDER BY HASH (o_custkey) INDEX Ordx9 (o_shippriority) ORDER BY HASH (o_shippriority) INDEX OrdxA ALL (o_clerk) ORDER BY HASH (o_clerk) INDEX OrdxB (o_orderkey, o_orderdate);
HELP INDEX generates the following report on this table. The BTEQ commands are included to show how the report format was derived. Attributes of particular interest for this example are highlighted in boldface.
BTEQ -- Enter your DBC/SQL request or BTEQ command: .sidetitles .sidetitles BTEQ -- Enter your DBC/SQL request or BTEQ command: .foldline .foldline BTEQ -- Enter your DBC/SQL request or BTEQ command: HELP INDEX orders; HELP INDEX orders; *** Help information returned. 13 rows. *** Total elapsed time was 1 second. Unique? N Primary//or//Secondary? P Column Names o_orderkey Index Id 1 Approximate Count 0 Index Name OrdPI Ordered//or//Partitioned? P CDT Index Type N Unique? Y Primary//or//Secondary? S Column Names o_orderkey Index Id 4 Approximate Count 0 Index Name OrdUSI Ordered//or//Partitioned? H CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_orderdate Index Id 8 Approximate Count 0 Index Name Ordx1 Ordered//or//Partitioned? H CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_orderdate Index Id 12 Approximate Count 0 Index Name Ordx2 Ordered//or//Partitioned? V CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_custkey, o_orderdate Index Id 16 Approximate Count 0 Index Name Ordx3 Ordered//or//Partitioned? V CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_custkey, o_orderdate Index Id 24 Approximate Count 0 Index Name Ordx4 Ordered//or//Partitioned? H CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_custkey, o_orderstatus Index Id 32 Approximate Count 0 Index Name Ordx5 Ordered//or//Partitioned? V CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_custkey, o_orderstatus Index Id 40 Approximate Count 0 Index Name Ordx6 Ordered//or//Partitioned? H CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_custkey Index Id 48 Approximate Count 0 Index Name Ordx7 Ordered//or//Partitioned? V CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_custkey Index Id 52
Example: HELP INDEX for a Geospatial Index
This example demonstrates a HELP INDEX report on table gt1 that has a NUSI named gt1_geoidx defined on the geospatial column geo that has the ST_Geometry data type.
CREATE SET TABLE jw.gt1 ( a INTEGER, geo SYSUDTLIB.ST_Geometry, c INTEGER) PRIMARY INDEX (a) INDEX gt1_geoidx (geo);
A HELP INDEX request on the NUSI named gt1_geoidx for this table returns the following.
HELP INDEX gt1_geoidx (geo); Unique? N Primary//or//Secondary? S Column Names geo Index Id 4 Approximate Count 14 Index Name gt1_idx Ordered//or//Partitioned? H CDT Index Type G Approximate Count 0 Index Name Ordx8 Ordered//or//Partitioned? H CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_shippriority Index Id 56 Approximate Count 0 Index Name Ordx9 Ordered//or//Partitioned? H CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_clerk Index Id 60 Approximate Count 0 Index Name OrdxA Ordered//or//Partitioned? H CDT Index Type N Unique? N Primary//or//Secondary? S Column Names o_orderkey, o_orderdate Index Id 64 Approximate Count 0 Index Name OrdxB Ordered//or//Partitioned? H CDT Index Type N
The CDT Index Type attribute is G to signify that the index has a Geospatial data type. If the attribute is N, the index is not a CDT Index Type.