Firstkeycard

[login to unmask email]

Firstkeycard
Hi lisst.

I have a question about the column firstkeycard.

I heard some years ago that the first column createed in a index must be a
column with the greatest number of distinct values(firstkeycard),
and I'm creating index in this way since that.

Now some people in my site are questioning that rule, and the
Administration Guide does not say a word about it.

If I use in the WHERE clause only predicates with "=" I stiil use the
rule , is that correct?

Why DB2 does not use the column colcard (syscolumns) to get the values of
the first column in the index, like it does for the others columns
in the index?

There was something in the past releases of DB2, where the firstkeycard was
important, if so, this still important nowadays?

TIA

Vinhaes
Caixa Seguros


Isaac Yassin

Re: Firstkeycard
(in response to vinhaes@CAIXASEGUROS.COM.BR)
Hi,
Look at Martin Hubel's article on last DB2 solution Journal.

Isaac Yassin
[login to unmask email]
----- Original Message -----
From: "Luiz Cesar Vinhaes da Costa Junior" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Wednesday, January 09, 2002 5:14 PM
Subject: Firstkeycard


Hi lisst.

I have a question about the column firstkeycard.

I heard some years ago that the first column createed in a index must be a
column with the greatest number of distinct values(firstkeycard),
and I'm creating index in this way since that.

Now some people in my site are questioning that rule, and the
Administration Guide does not say a word about it.

If I use in the WHERE clause only predicates with "=" I stiil use the
rule , is that correct?

Why DB2 does not use the column colcard (syscolumns) to get the values of
the first column in the index, like it does for the others columns
in the index?

There was something in the past releases of DB2, where the firstkeycard was
important, if so, this still important nowadays?

TIA

Vinhaes
Caixa Seguros
================
DB2-L webpage at http://www.ryci.com/db2-l. The owners
of the list can



Mark Doyle

Re: Firstkeycard
(in response to James Kwan)
You said
"I heard some years ago that the first column createed in a index must be a
column with the greatest number of distinct values(firstkeycard)". If you
change the word "must" to "should be (for the best application
performance)" then the statement is accurate.

In the earlier versions of DB2 (and this is probably still be true in V7)
the optomizer overweighted the importance of the first key cardinality to
determine filter factors used to estimate the cost of using that index.
Indexes with very low first key cardinality were not considered as useful as
indexes with high first key cardinality. So this is why it was "good DBA
practice" to have the column with the highest cardinality be the first
column in the key.

As to why DB2 doesn't use column cardinality statistics, I suspect it has
something to do with the ease of programming the optomizer, or the resulting
access path selection, but only IBM knows for sure.

Anyway, it's still good practice to have high first key cardinality, all
things being equal (which they seldom are <BG>!)



----- Original Message -----
From: "Luiz Cesar Vinhaes da Costa Junior" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Wednesday, January 09, 2002 9:14 AM
Subject: Firstkeycard


Hi lisst.

I have a question about the column firstkeycard.

I heard some years ago that the first column createed in a index must be a
column with the greatest number of distinct values(firstkeycard),
and I'm creating index in this way since that.

Now some people in my site are questioning that rule, and the
Administration Guide does not say a word about it.

If I use in the WHERE clause only predicates with "=" I stiil use the
rule , is that correct?

Why DB2 does not use the column colcard (syscolumns) to get the values of
the first column in the index, like it does for the others columns
in the index?

There was something in the past releases of DB2, where the firstkeycard was
important, if so, this still important nowadays?

TIA

Vinhaes
Caixa Seguros


http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]


James Kwan

Re: Firstkeycard
(in response to Isaac Yassin)
Caixa,

It is advisable to use greatest distinct values for the first key columns,
however it is not a must. You also need to look at your applications to see
what are the most frequently used columns in the predicates.


James Kwan

IBM Certified Expert



In a message dated 1/9/02 10:09:36 AM Central Standard Time,
[login to unmask email] writes:


>
> Hi lisst.
>
> I have a question about the column firstkeycard.
>
> I heard some years ago that the first column createed in a index must be a
> column with the greatest number of distinct values(firstkeycard),
> and I'm creating index in this way since that.
>
> Now some people in my site are questioning that rule, and the
> Administration Guide does not say a word about it.
>
> If I use in the WHERE clause only predicates with "=" I stiil use the
> rule , is that correct?
>
> Why DB2 does not use the column colcard (syscolumns) to get the values of
> the first column in the index, like it does for the others columns
> in the index?
>
> There was something in the past releases of DB2, where the firstkeycard was
> important, if so, this still important nowadays?
>
> TIA
>
> Vinhaes
>


[login to unmask email]

Re: Firstkeycard
(in response to Mark Doyle)
Isaac

Where a can find it.

TIA.

Vinhaes
Caixa Seguros.





Isaac Yassin <[login to unmask email]>@RYCI.COM> em 09/01/2002 13:41:49

Favor responder a DB2 Data Base Discussion List <[login to unmask email]>

Enviado Por: DB2 Data Base Discussion List <[login to unmask email]>


Para: [login to unmask email]
cc:

Assunto: Re: Firstkeycard


Hi,
Look at Martin Hubel's article on last DB2 solution Journal.

Isaac Yassin
[login to unmask email]
----- Original Message -----
From: "Luiz Cesar Vinhaes da Costa Junior" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Wednesday, January 09, 2002 5:14 PM
Subject: Firstkeycard


Hi lisst.

I have a question about the column firstkeycard.

I heard some years ago that the first column createed in a index must be a
column with the greatest number of distinct values(firstkeycard),
and I'm creating index in this way since that.

Now some people in my site are questioning that rule, and the
Administration Guide does not say a word about it.

If I use in the WHERE clause only predicates with "=" I stiil use the
rule , is that correct?

Why DB2 does not use the column colcard (syscolumns) to get the values of
the first column in the index, like it does for the others columns
in the index?

There was something in the past releases of DB2, where the firstkeycard was
important, if so, this still important nowadays?

TIA

Vinhaes
Caixa Seguros
================

the DB2-L webpage at http://www.ryci.com/db2-l. The owners
of the list can








Terry Purcell

Re: Firstkeycard
(in response to vinhaes@CAIXASEGUROS.COM.BR)
Vinhaes,

Although Martin has written a great article, it does not really address your
questions. To Martin's defense, he wrote the article before knowing what
your questions were. It is still worth a read because it probably answers
other questions you may have. It should be at www.idug.org, but I couldn't
find the latest edition.

If you have a full "=" match (ie. I still get a matchcols=3 no matter what
order the columns are in the index), as you state, then firstkeycard is LAST
on my list.

What I care about more is:
- ensuring additional indexes reflect (as closely as possible) the
clustering index, and hence, the data. This minimizes random I/O & list
prefetch, and encourages dynamic and sequential prefetch.
- index design considers the clustering sequence (or other index sequence)
of joined tables. This will ensure the best utilization of index lookaside
and dynamic prefetch on the inner table of a join.
- Sort avoidance for ORDER BY (GROUP BY can reorder it's columns).

For a full "=" match, the key is always concatenated for the search, so the
order is not relevant for an index probe.

As to why the optimizer uses certain statistics and not others is
proprietary information and not something that anyone else knows. I'm sure
simplicity is high on the list. If you're not capturing multicolumn
cardinality statistics (2ndkeycard, 3rdkeycard etc) then there is greater
margin of error for column correlation with a low cardinality firstkeycard.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Isaac Yassin
Sent: Wednesday, January 09, 2002 10:42 AM
To: [login to unmask email]
Subject: Re: Firstkeycard


Hi,
Look at Martin Hubel's article on last DB2 solution Journal.

Isaac Yassin
[login to unmask email]
----- Original Message -----
From: "Luiz Cesar Vinhaes da Costa Junior" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Wednesday, January 09, 2002 5:14 PM
Subject: Firstkeycard


Hi lisst.

I have a question about the column firstkeycard.

I heard some years ago that the first column createed in a index must be a
column with the greatest number of distinct values(firstkeycard),
and I'm creating index in this way since that.

Now some people in my site are questioning that rule, and the
Administration Guide does not say a word about it.

If I use in the WHERE clause only predicates with "=" I stiil use the
rule , is that correct?

Why DB2 does not use the column colcard (syscolumns) to get the values of
the first column in the index, like it does for the others columns
in the index?

There was something in the past releases of DB2, where the firstkeycard was
important, if so, this still important nowadays?

TIA

Vinhaes
Caixa Seguros
================



Ruediger Kurtz

Re: Firstkeycard
(in response to Terry Purcell)
Luis,

to add one more point .... one should also consider the columns you
sort/group by

Ruediger Kurtz
--
>"""""<
!_ _!
(o) (o)
oOOO--(_)--OOOo------------------------------------------------------*
* *
* Rüdiger Kurtz *
* *
* HUK-Coburg Phone: 09561/96-3914 *
* Versicherungen-Bausparen Fax : 09561/96-3678 *
* Abt. Informatik - Betrieb (Datenbank-Administration) *
* Bahnhofsplatz Mail: [login to unmask email] *
* 96450 Coburg *
* *
* .oooO *
*--( )--Oooo.------------------------------------------------------*
\ ( ( )
\_) ) /
(_/