By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,979 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,979 IT Pros & Developers. It's quick & easy.

Query regarding Index

P: n/a
Suppose i have a table which holds thousands of records with the
following structure

CREATE TABLE "test "."T_CNTRY" (
"CNTRY_CDE" CHAR(2) NOT NULL ,
"CNTRY_NAME" VARCHAR(50) )
and i have Created an index like below ::

CREATE UNIQUE INDEX "testI "."Uindex1" ON "test "."T_CNTRY"
("CNTRY_CDE" ASC);
1.) Do i have to create an Index like this to make queries which make
use of this run faster

CREATE INDEX "testI "."Iindex1" ON "test "."T_CNTRY" ("CNTRY_CDE"
ASC);

OR

2.) Creating the unique index/primary key index will serve the purpose
of making queries run faster

thanks in advance

Feb 28 '06 #1
Share this Question
Share on Google+
15 Replies


P: n/a
rAinDeEr wrote:
Suppose i have a table which holds thousands of records with the
following structure

CREATE TABLE "test "."T_CNTRY" (
"CNTRY_CDE" CHAR(2) NOT NULL ,
"CNTRY_NAME" VARCHAR(50) )
and i have Created an index like below ::

CREATE UNIQUE INDEX "testI "."Uindex1" ON "test "."T_CNTRY"
("CNTRY_CDE" ASC);
1.) Do i have to create an Index like this to make queries which make
use of this run faster

CREATE INDEX "testI "."Iindex1" ON "test "."T_CNTRY" ("CNTRY_CDE"
ASC);

OR

2.) Creating the unique index/primary key index will serve the purpose
of making queries run faster

A UNIQUE index is an INDEX + UNIQUEness.
DB2 knows that and will tell you:
db2 => create table t(c1 int not null);
DB20000I The SQL command completed successfully.
db2 => create unique index i1 on t(c1);
DB20000I The SQL command completed successfully.
db2 => create index i2 on t(c1);
SQL0605W The index was not created because an index "SRIELAU.I1" with a
matching definition already exists. SQLSTATE=01550

As you see, DB2 figured that the second index is not needed.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 28 '06 #2

P: n/a
hhmmmm...

1.)this means i need not create a primary key when I have already
defined a UNIQUE + INDEX like what i have tested before.

CREATE UNIQUE INDEX "test"."IAWD_TYP01" ON "DB2GRA "."T_TYP"
("AWD_TYP_CDE" ASC) CLUSTER
DB20000I The SQL command completed successfully.

ALTER TABLE "test"."T__TYP" ADD CONSTRAINT "PK_AWD_TYP" PRIMARY KEY
("AWD_TYP_CDE")
SQL0598W Existing index "DB2GRA.IAWD_TYP01" is used as the index for
the
primary key or a unique key. SQLSTATE=01550

2.) Does that mean that I can ask the Data Modeler not to make unique
index togther with a primary key
coz cerating unique indexes serves the purpose of both.

~ Thanks for the information

Feb 28 '06 #3

P: n/a
A primary key is a unique index + NOT NULL constraints on the key
column(s) ... ie , a unique key columns can have null values but
primary key columns cannot...

The primary key is also needed when you want to define a refrential
constraint ...
From the data model perspecitive, defining a primary key is a good

practise ... Other unique keys are defined as alternate keys ...
therefor, why not define a primary key and avoid defining Unique index
on the PK columns ?

Going back to your original question, make sure you do RUNSTATS on the
tables and indexes ... Otherwise, the optimizer might not use the index
HTH

Sathyaram

Feb 28 '06 #4

P: n/a
s.*********@googlemail.com wrote:
A primary key is a unique index + NOT NULL constraints on the key
column(s) ... ie , a unique key columns can have null values but
primary key columns cannot...

The primary key is also needed when you want to define a refrential
constraint ...
From the data model perspecitive, defining a primary key is a good

practise ... Other unique keys are defined as alternate keys ...
therefor, why not define a primary key and avoid defining Unique index
on the PK columns ?

Going back to your original question, make sure you do RUNSTATS on the
tables and indexes ... Otherwise, the optimizer might not use the index

Further:
By defining the unique index explicitly before the primary key two
things are achieved:
1. You are in control of the index name
2. You can INCLUDE additional columns with the index.

Example for 2:
CREATE TABLE T(pk INT NOT NULL, c1 INT);
CREATE UNIQUE INDEX I_FOR_PK ON T(pk) INCLUDE(c1);
ALTER TABLE T ADD CONSTRAINTS PK PRIMARY KEY (pk);

DB2 will pick I_FOR_PK to enforce the primary key,
but now
SELECT c1 FROM T WHERE pk = ?
will be able to use an index only access.

In short I think it's a good thing to separate out the steps.
Don't mind the warnings.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 28 '06 #5

P: n/a
>By defining the unique index explicitly before the primary key two things are achieved:
1. You are in control of the index name
2. You can INCLUDE additional columns with the index


Can we add: 3. You can specify CLUSTER.

I am actually not familiar enough with CLUSTER to see if it makes sense
on the PRIMARY KEY.

B.

Feb 28 '06 #6

P: n/a
"Brian Tkatch" <Ma***********@ThePentagon.com> wrote in message > Can we
add: 3. You can specify CLUSTER.

I am actually not familiar enough with CLUSTER to see if it makes sense
on the PRIMARY KEY.

B.


If your primary key has more than one column, and one or more of the columns
is a primary key on another parent table (there is a foreign key
relationship), then the primary key on the dependent table is a often a good
candidate for a clustering index.

Example:

ORDER table:
Order Number Integer PK
Customer Number (clustering index)
(There PK is a single column and not the clustering index)

ORDER DETAIL Table:
Order Number Integer PK (FK to ORDER Table)
Order Item Smallint PK
(the above PK with 2 columns should be the clustering index)
Feb 28 '06 #7

P: n/a
OK, i see.

Does CLUSTERing help BETWEENs?

That is, if the PK is a part number, part numbers are somewhat
sequential, and usually parts are grabbed with a BETWEEN, would
CLUSTERing put the part in order, helping a range scan quickly grab the
range from disk too?

B.

Feb 28 '06 #8

P: n/a
Yes. Cluster option directs DB2 to do the inserts using the index with
cluster option to determine the page in which the insert should go.
The idea is to keep inserted data clustered in the proper page.
When you use range delimiting predicates (between, <=,>=, like xx%, ..) on
the column(s) of the index then DB2 will likely use the index to grab the
pages with the rows you want.
After creating the index with cluster, if the data exists in the table, do
an offline reorg and db2 will sequence the rows following the index.
Inserts will try to keep the sequencing .
It would also be advisable to alter the table (or create it) with the
PCTFREE parm. This will give a percentage of free space in each page and
DB2 would likely find room in the proper page to place the insert.
db2 create table foo (col1 int,col2 char(xx),col3 .....) PCTFREE 20
db2 create unique index partno on foo (col1) cluster
db2 alter table foo primary key (col1) constraint pkcol1
Populate the table using a sorted file in col1 sequence or reorg aft er
the import/insert/load.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Brian Tkatch" <Ma***********@ThePentagon.com> a écrit dans le message de
news: 11*********************@i40g2000cwc.googlegroups.c om...
OK, i see.

Does CLUSTERing help BETWEENs?

That is, if the PK is a part number, part numbers are somewhat
sequential, and usually parts are grabbed with a BETWEEN, would
CLUSTERing put the part in order, helping a range scan quickly grab the
range from disk too?

B.


Feb 28 '06 #9

P: n/a
Hi,

Fraser McArthur, a consultant at the IBM Toronto Lab had wrote in an
article and it contained
· When queries are completing in a reasonable time, avoid adding
indexes as they can slow down update operations and consume extra
space. It is sometimes possible to have one larger index that will
cover several queries.
· Avoid using more than five columns in an index due to management
overhead.
· For multi-column indexes, place the column which is referenced most
in queries first in the definition.
· Avoid adding an index which is similar to a preexisting index. It
creates more work for the optimizer and will slow down update
operations. Instead, alter the preexisting index to contain additional
columns. For example, there is an existing index i1 on (c1,c2) of a
table. You notice that your query using "where c2=?", so you create an
additional index i2 on (c2). This similar index adds nothing, as it is
redundant to i1 and is now additional overhead.

The data model which we make use of has tables which have the similar
pattern.

My question is if we already have a Unique index(unique+index) and a
primary key
Do we really need a separate index on prod_typ_cde (because it is
redundant and is it an additional overhead) ??

CREATE TABLE "DB2TAR"."TKTG" (
"PROD_TYP_CDE" CHAR(6) NOT NULL ,
"REC_LOCTR_NUM" CHAR(7) NOT NULL ,
"DEP_ALLW_IND" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
"BKNG_RNG_MIN_DAYS" SMALLINT NOT NULL ,
"BKNG_RNG_MAX_DAYS" SMALLINT NOT NULL ,
"AFT_BKNG_DAYS" SMALLINT ,
"BEF_DPTR_DAYS" SMALLINT ,
"TKTG_GRC_DAYS" SMALLINT ,
"GARP_RVW_ADVN_DAYS" SMALLINT ,
"LST_UPDT_USID" CHAR(6) NOT NULL ,
"LST_UPDT_TMS" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP )

IN "SGRA005" ;
CREATE UNIQUE INDEX "DB2TAR"."ITKTG_OVRD_RULE01" ON "DB2GRA"."TKTG"
("PROD_TYP_CDE" ASC,
"REC_LOCTR_NUM" ASC,
"DEP_ALLW_IND" ASC,
"BKNG_RNG_MIN_DAYS" ASC,
"BKNG_RNG_MAX_DAYS" ASC)
CLUSTER ;

CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG"
("PROD_TYP_CDE" ASC);
ALTER TABLE "DB2TAR "."TKTG"
ADD CONSTRAINT "PK_TKTG_OVRD_RULE" PRIMARY KEY
("PROD_TYP_CDE",
"REC_LOCTR_NUM",
"DEP_ALLW_IND",
"BKNG_RNG_MIN_DAYS",
"BKNG_RNG_MAX_DAYS");

Mar 6 '06 #10

P: n/a
Please post with text only on this newsgroup.

Given the following index (either explicitly created, or created for you via
a PK definition):

CREATE UNIQUE INDEX "DB2TAR"."ITKTG_OVRD_RULE01" ON "DB2GRA"."TKTG"
("PROD_TYP_CDE" ASC,
"REC_LOCTR_NUM" ASC,
"DEP_ALLW_IND" ASC,
"BKNG_RNG_MIN_DAYS" ASC,
"BKNG_RNG_MAX_DAYS" ASC)
CLUSTER ;

Then the following index is redundant since it is the same as the first
column of the index above:

CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG"
("PROD_TYP_CDE" ASC);

However, even with the unique index above, the following index is not
redundant if you have SQL statements with REC_LOCTR_NUM as the only
predicate.

CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE03" ON "DB2GRA"."TKTG"
("REC_LOCTR_NUM" ASC);

Mar 6 '06 #11

P: n/a
I didnt understand how this ones redundant

CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG"
("PROD_TYP_CDE" ASC);

and this ones not redundant

CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE03" ON "DB2GRA"."TKTG"
("REC_LOCTR_NUM" ASC);

Does that mean that creating an index on any other column is not
redundant ?

It would be great if you could put some more light to this. cause i am
really confused now.

Mar 6 '06 #12

P: n/a
Because "PROD_TYP_CDE" ASC, is the first column of the index:
CREATE UNIQUE INDEX "DB2TAR"."ITKTG_OVRD_RULE01" ON "DB2GRA"."TKTG"
("PROD_TYP_CDE" ASC,
"REC_LOCTR_NUM" ASC,
"DEP_ALLW_IND" ASC,
"BKNG_RNG_MIN_DAYS" ASC,
"BKNG_RNG_MAX_DAYS" ASC)
CLUSTER ;
and is clustered,
Then your second index:
CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG"
("PROD_TYP_CDE" ASC);
is redundant.

Because of clustering on the first column, it is highly unlikely that the
second column will also be clustered; therefore the index:
CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE03" ON "DB2GRA"."TKTG"
("REC_LOCTR_NUM" ASC);
would have a bad clusterratio and would not likely be used by DB2 and then
be redundant.
Since the keys would not be in order over the table, sorts would have to
happen, or using inequality predicates would probably cause table scans
anyway and the index would still not be used.
If the query has a where clause on the first column, then having a where on
the second column will cause the first index to be used but never the second
one. So, redundant.

There must be a reason (sme SQL statements) that cause you to think of the
two indexes.
Validate this by running the statements agains the Design Advisor. It has
an option to Evaluate indexes. I highly suspect that the advisor will
identify the second index as not used.

HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"rAinDeEr" <ta**********@gmail.com> a écrit dans le message de news:
11**********************@i40g2000cwc.googlegroups. com...
I didnt understand how this ones redundant

CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG"
("PROD_TYP_CDE" ASC);

and this ones not redundant

CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE03" ON "DB2GRA"."TKTG"
("REC_LOCTR_NUM" ASC);

Does that mean that creating an index on any other column is not
redundant ?

It would be great if you could put some more light to this. cause i am
really confused now.


Mar 6 '06 #13

P: n/a
"rAinDeEr" <ta**********@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
I didnt understand how this ones redundant

CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG"
("PROD_TYP_CDE" ASC);

and this ones not redundant

CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE03" ON "DB2GRA"."TKTG"
("REC_LOCTR_NUM" ASC);

Does that mean that creating an index on any other column is not
redundant ?

It would be great if you could put some more light to this. cause i am
really confused now.


There are two ways that indexes are accessed. The most efficient is via the
b-tree, where DB2 traverses the non-leaf pages to find the leaf page and the
matching index row which contains the table RID in the least amount of page
fetches (indexes row are stored on pages like table rows).

The other way is where an index is read in its entirety, the leaf pages are
accessed sequentially top to bottom, sort of like a table scan, but on an
index. Each index row is examined by DB2 to see if it qualifies. This is
usually not the most efficient way to find the qualifying index row and the
associated RID pointing to the table.

In order fir DB2 to use the b-tree of an index to find the qualifying rows,
the predicate must contain the left most columns (1-n) of an index. If the
predicate only contains the second column of the index, then the best DB2
can do is to scan the entire index and not be able to use the b-tree.
Mar 7 '06 #14

P: n/a
Thanks every one!!
Finally know how to create indexes and what to watch out for..
The discussion was excellent

thanks again!!!!

Mar 7 '06 #15

P: n/a
Yeah, thanx for the info.

B.

Mar 7 '06 #16

This discussion thread is closed

Replies have been disabled for this discussion.