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=X100;
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=X100_SI;
Last modified date: 04/03/2024