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

Sanity check: primary key(a, b) implies index

P: n/a

hi. i very rarely do db2 stuff, so i just need to be confirmed that
when i do:

create table (
col_a int,
col_b varchar(10),
col_c int,
primary key(col_a, col_b)
);

then col_a and col_b are indexed for lookup, correct?

thanks!

morten

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
us****@kikobu.com wrote:
then col_a and col_b are indexed for lookup, correct?


Yes. But (col_a + col_b) as one row so you don't have an separate index
on col_b

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin
Nov 12 '05 #2

P: n/a
us****@kikobu.com wrote:

hi. i very rarely do db2 stuff, so i just need to be confirmed that
when i do:

create table (
col_a int,
col_b varchar(10),
col_c int,
primary key(col_a, col_b)
);

then col_a and col_b are indexed for lookup, correct?


Correct. But the primary reason for an (unique) index on the columns of the
primary key is to ensure uniqueness of those values.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #3

P: n/a
Ok, so:

select * from table where col_b = 'boo';

will result in a full table scan, whereas

select * from table where col_a = 1 and col_b = 'boo';

will not?

thanks.

Morten

Nov 12 '05 #4

P: n/a

<us****@kikobu.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Ok, so:

select * from table where col_b = 'boo';

will result in a full table scan, whereas

select * from table where col_a = 1 and col_b = 'boo';

will not?

thanks.

Morten


Not necessarily. It depends on how may rows will be returned. If the number
of rows returned is relatively small compared to the number of rows in the
table, then select * from table where col_b = 'boo' can use the index to
determine the qualifying rows. However, DB2 will have to scan the entire
index, instead of using the b-tree of the index.

If select * from table where col_a = 1 and col_b = 'boo' uses an index also
depends on the number of qualifying rows. If DB2 determines during access
path selection that it can reduce the number of pages accessed, then it will
likely use the index. Otherwise if there is likely at least one qualifying
row on each page, then DB2 will do a table scan. However, if DB2 uses the
index for this query, it will use the b-tree and not have to read the entire
index.
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.