Query regarding Index

[rAinDeEr]

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

[Serge Rielau]

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

[rAinDeEr]

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_TY P01" ON "DB2GRA "."T_TYP"
("AWD_TYP_CD E" ASC) CLUSTER
DB20000I The SQL command completed successfully.

ALTER TABLE "test"."T__ TYP" ADD CONSTRAINT "PK_AWD_TYP " PRIMARY KEY
("AWD_TYP_CD E")
SQL0598W Existing index "DB2GRA.IAWD_TY P01" 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

[s.sathyaram]

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

[Serge Rielau]

s.*********@goo glemail.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

[Brian Tkatch]

>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.

[Mark 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)

[Brian Tkatch]

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.

[Pierre Saint-Jacques]

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************* ********@i40g20 00...legro ups.com...

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.

[rAinDeEr]

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+in dex) 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"."T KTG" (
"PROD_TYP_C DE" CHAR(6) NOT NULL ,
"REC_LOCTR_ NUM" CHAR(7) NOT NULL ,
"DEP_ALLW_I ND" CHAR(1) NOT NULL WITH DEFAULT 'N' ,
"BKNG_RNG_MIN_D AYS" SMALLINT NOT NULL ,
"BKNG_RNG_MAX_D AYS" SMALLINT NOT NULL ,
"AFT_BKNG_D AYS" SMALLINT ,
"BEF_DPTR_D AYS" SMALLINT ,
"TKTG_GRC_D AYS" SMALLINT ,
"GARP_RVW_ADVN_ DAYS" SMALLINT ,
"LST_UPDT_U SID" CHAR(6) NOT NULL ,
"LST_UPDT_T MS" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP )

IN "SGRA005" ;
CREATE UNIQUE INDEX "DB2TAR"."ITKTG _OVRD_RULE01" ON "DB2GRA"."T KTG"
("PROD_TYP_C DE" ASC,
"REC_LOCTR_ NUM" ASC,
"DEP_ALLW_I ND" ASC,
"BKNG_RNG_MIN_D AYS" ASC,
"BKNG_RNG_MAX_D AYS" ASC)
CLUSTER ;

CREATE INDEX "DB2TAR "."ITKTG_OVRD_R ULE02" ON "DB2GRA"."T KTG"
("PROD_TYP_C DE" ASC);
ALTER TABLE "DB2TAR "."TKTG"
ADD CONSTRAINT "PK_TKTG_OVRD_R ULE" PRIMARY KEY
("PROD_TYP_CDE" ,
"REC_LOCTR_NUM" ,
"DEP_ALLW_I ND",
"BKNG_RNG_MIN_D AYS",
"BKNG_RNG_MAX_D AYS");