CREATE INDEX Examples
1. Create an index for the columns, ename and age, on the employee table. The index is recreated when the table is modified.
CREATE INDEX ename ON employee (ename, age)
WITH PERSISTENCE;
2. Create an index called ename and locate it on the area referred to by the location_name, remote.
CREATE INDEX ename ON employee (ename, age)
WITH LOCATION = (remote);
3. Create a B-tree index on the ename and eage columns, keyed on ename with leaf index pages filled 50 percent.
CREATE INDEX ename2 ON employee (ename, eage)
WITH KEY = (ename),
STRUCTURE = BTREE,
LEAFFILL = 50;
4. Create a unique index, specifying that uniqueness is checked after any UPDATE statements are completed.
CREATE UNIQUE INDEX ename3 ON employee (ename, empno)
WITH KEY = (ename, empno),
UNIQUE_SCOPE = STATEMENT;
5. Create a single, unique index on column c1 in table tl.
CREATE INDEX i1 ON t1 (c1) UNIQUE
6. Create a unique index using the WITH clause to override the default index structure.
CREATE UNIQUE INDEX (i1 on t1(c1) WITH STRUCTURE=HASH)
7. Create multiple indexes at one time using the UNIQUE qualifier before the INDEX keyword. All indexes created by this statement are unique.
CREATE UNIQUE INDEX (i1 ON t1(c1) WITH STRUCTURE=HASH,PERSISTENCE),
(i2 ON t1(c2) WITH STRUCTURE=BTREE)
8. Create multiple indexes at one time using the UNIQUE keyword within each index specification.
CREATE INDEX (i1 ON t1 (c1) UNIQUE WITH STRUCTURE=HASH,PERSISTENCE), (i2 ON
t1(c2) UNIQUE WITH STRUCTURE=BTREE)
9. Create both unique and non-unique indexes.
CREATE INDEX (i1 on t1(c1) UNIQUE WITH STRUCTURE=HASH,PERSISTENCE),
(i2 ON t1(c2) WITH STRUCTURE=BTREE)
Note: Examples 7 and 8 perform the same operation, while 9 demonstrates individual control of the UNIQUE attribute.
10. X100: Create a primary index named o_order_date_idx on the order_date column of the orders table:
CREATE INDEX o_order_date_idx ON orders(order_date);
11. X100: Create a secondary index named si_supplier on the s_name column of the supplier table:
CREATE INDEX si_supplier ON supplier (s_name) WITH STRUCTURE=X100SI;
Last modified date: 08/14/2024