db2admin wrote:
Hello,
What is the driving factor when making more columns indexable?
Should i see if i can modify existing indexes and merge new columns in
existing indexes or create a new index with new column?
Depends on how the column(s) is/are going to be used in queries. If you
have columns that are going to be filtered on (WHERE), sorted (ORDER
BY), or grouped (GROUP BY) then it may be worth defining an entirely
new index for them.
Alternatively, it may be worth shifting those columns to the front of
the column list of existing indexes (assuming this won't adversely
affect other queries which filter/sort on the columns which currently
appear at the front of the indexes' columns).
Finally, if the columns are simply being retrieved (not
filtered/sorted) it may still be worth updating index definitions to
attempt to achieve "pure index" queries where the table isn't touched
at all. For example, in small tables I often do something like the
following:
CREATE TABLE MYTABLE (
COLA INTEGER NOT NULL,
COLB INTEGER NOT NULL,
COLC INTEGER NOT NULL
);
CREATE UNIQUE INDEX MYTABLE_PK
ON MYTABLE (COLA)
INCLUDE (COLB, COLC)
ALLOW REVERSE SCANS;
ALTER TABLE MYTABLE
ADD CONSTRAINT PK PRIMARY KEY (COLA);
In the above definition, queries against MYTABLE which only filter or
sort on COLA will never touch the table. For example:
SELECT COLB, COLC FROM MYTABLE WHERE COLA 100;
The query above should use the index to figure out the set of rows to
retrieve, and should then retrieve the values of COLB and COLC straight
from the leaves of the index (the INCLUDEd columns are effectively a
tuple hanging off the leaves of the index tree).
Given the above definitions, if I have several queries some of which
filter/sort on COLB and COLC, and others which only filter/sort on COLC
I might consider creating the following definition:
CREATE INDEX MYTABLE1 ON MYTABLE(COLC, COLB);
All such queries could utilize the index above (including those that
only filter/sort on COLC as it appears before COLB in the index column
list). There'd be little point defining another index with COLB at the
front unless I had another set of queries that filtered/sorted on COLB
alone.
HTH,
Dave.
--
