469,291 Members | 1,703 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,291 developers. It's quick & easy.

Query on indexes for performance tuning

Hi,

I have a table with the following definition.
The primary key is west_nme and west_eff_tms and i have created unique
index on them.
The foreign keys which reference other parent tables are
west_typ_cde,r_cde,ccl_cde and t_cde and I have created indexes
FK_t_west01,FK_t_west02,FK_t_west03 respectively.

Now, all the other fields in this table participate in the WHERE clause
of different SQL's.
How should I create the indexes in order to maximise the performance ?

Do I need to create different indexes on each fields (around 20
different indexes more)?

CREATE TABLE tar.t_west (
west_nme VARCHAR(100) NOT NULL,
west_eff_tms TIMESTAMP NOT NULL,
west_expr_tms TIMESTAMP NOT NULL,
west_typ_cde CHAR(3) NOT NULL,
tdsig CHAR(7) NOT NULL,
cwp_ind CHAR(1) NOT NULL,
bcl_txt CHAR(20),
ccl_cde CHAR(1),
stxt VARCHAR(100),
t_cde CHAR(3) NOT NULL,
r_cde CHAR(3) NOT NULL,
create_usr_id CHAR(6) NOT NULL,
create_tms TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
lst_updt_usr_id CHAR(6) NOT NULL,
lst_updt_tms TIMESTAMP NOT NULL DEFAULT CURRENT
TIMESTAMP,
s_txt VARCHAR(25),
e_c_txt VARCHAR(25),
d_c_txt VARCHAR(25),
h_s_txt VARCHAR(40),
twp_ind CHAR(1) NOT NULL,
hcl_txt CHAR(20),
mcl_cde CHAR(1),
rtxt VARCHAR(100),
x_cde CHAR(3) NOT NULL,
z_cde CHAR(3) NOT NULL,
);

CREATE UNIQUE INDEX tar.PK_t_west ON tar.t_west
(
west_nme ASC,
west_eff_tms ASC
) CLUSTER;

CREATE INDEX tar.FK_t_west01 ON tar.t_west
(
west_typ_cde ASC
);

CREATE INDEX tar.FK_t_west02 ON tar.t_west
(
r_cde ASC
);

CREATE INDEX tar.FK_t_west03 ON tar.t_west
(
ccl_cde ASC
);

CREATE INDEX DB2NCI.FK_t_west04 ON DB2NCI.t_west
(
t_cde ASC
);

Thanks in advance
RaInDeEr

Jun 26 '06 #1
2 1437
I wouldn't attempt to tell you how to index this table without seeing
information about the data values and a good survey of the queries being
run against it.

DB2's Design Advisor is intended to help you determine what indexes will
be effective to improve performance. You should investigate using it.

Don't make columns with low cardinality the only column of an index. The
optimizer will never use them because they don't have eough selectivity
to limit retrieved rows. Text and code fields are frequently poor
candidates for initial columns of an index. Timestamps are great for
provide uniqueness in an index but are difficult to enter for matching
purposes.

Every index you create is additional overhead when inserting, deleting
and altering rows where the column values in the index change.

Phil Sherman
rAinDeEr wrote:
Hi,

I have a table with the following definition.
The primary key is west_nme and west_eff_tms and i have created unique
index on them.
The foreign keys which reference other parent tables are
west_typ_cde,r_cde,ccl_cde and t_cde and I have created indexes
FK_t_west01,FK_t_west02,FK_t_west03 respectively.

Now, all the other fields in this table participate in the WHERE clause
of different SQL's.
How should I create the indexes in order to maximise the performance ?

Do I need to create different indexes on each fields (around 20
different indexes more)?

CREATE TABLE tar.t_west (
west_nme VARCHAR(100) NOT NULL,
west_eff_tms TIMESTAMP NOT NULL,
west_expr_tms TIMESTAMP NOT NULL,
west_typ_cde CHAR(3) NOT NULL,
tdsig CHAR(7) NOT NULL,
cwp_ind CHAR(1) NOT NULL,
bcl_txt CHAR(20),
ccl_cde CHAR(1),
stxt VARCHAR(100),
t_cde CHAR(3) NOT NULL,
r_cde CHAR(3) NOT NULL,
create_usr_id CHAR(6) NOT NULL,
create_tms TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
lst_updt_usr_id CHAR(6) NOT NULL,
lst_updt_tms TIMESTAMP NOT NULL DEFAULT CURRENT
TIMESTAMP,
s_txt VARCHAR(25),
e_c_txt VARCHAR(25),
d_c_txt VARCHAR(25),
h_s_txt VARCHAR(40),
twp_ind CHAR(1) NOT NULL,
hcl_txt CHAR(20),
mcl_cde CHAR(1),
rtxt VARCHAR(100),
x_cde CHAR(3) NOT NULL,
z_cde CHAR(3) NOT NULL,
);

CREATE UNIQUE INDEX tar.PK_t_west ON tar.t_west
(
west_nme ASC,
west_eff_tms ASC
) CLUSTER;

CREATE INDEX tar.FK_t_west01 ON tar.t_west
(
west_typ_cde ASC
);

CREATE INDEX tar.FK_t_west02 ON tar.t_west
(
r_cde ASC
);

CREATE INDEX tar.FK_t_west03 ON tar.t_west
(
ccl_cde ASC
);

CREATE INDEX DB2NCI.FK_t_west04 ON DB2NCI.t_west
(
t_cde ASC
);

Thanks in advance
RaInDeEr

Jun 26 '06 #2

Hi Phil ..

Thanks for your input..
I would like to know the design aspect. Before i can use Index advisor,
i need to know the practise..I am trying to model a DB using ERwin

RaInDeEr

Jun 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Bob | last post: by
16 posts views Thread by Dave Weaver | last post: by
5 posts views Thread by xeqister | last post: by
2 posts views Thread by Veeru71 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.