13. Creating Indexes : Creating Indexes : Creating a Secondary Index : Single-attribute Secondary Index
 
Share this page                  
Single-attribute Secondary Index
A secondary index can be built on one attribute.
Given the following table:
CREATE TABLE supplier (
  s_suppkey INTEGER NOT NULL PRIMARY KEY,
  s_name CHAR(25) NOT NULL,
  s_address VARCHAR(40) NOT NULL,
  s_nationkey INTEGER NOT NULL REFERENCES nation,
  s_phone CHAR(15) NOT NULL,
  s_acctbal DECIMAL(7,2) NOT NULL,
  s_comment VARCHAR(101) NOT NULL
) WITH STRUCTURE=VECTORWISE;
Create a clustered (primary) index on this table:
CREATE INDEX supplier_idx ON supplier(s_nationkey);
If there are thousands of suppliers, a query such as:
SELECT * FROM supplier WHERE s_name = 'zeebee';
will benefit significantly from a secondary index on s_name:
CREATE INDEX supplier_sidx ON supplier(s_name) WITH STRUCTURE=VECTORWISE_SI;