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

interesting index design question.

P: n/a
On a 3 [INTEGER] column table, the PK consists of all 3 columns.
I [also] have queries on the first two columns, as well as on the last two
columns. (Select * where column3=x and column2=y.)
I was hoping that this same pk index will suffice for the latter
(reverse-scan is on), but its doing a table scan.
Have I misinterpreted what a reverse-scan is?

Please advise. Thanks.
nat
Aug 23 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
natG wrote:
On a 3 [INTEGER] column table, the PK consists of all 3 columns.
I [also] have queries on the first two columns, as well as on the last two
columns. (Select * where column3=x and column2=y.)
I was hoping that this same pk index will suffice for the latter
(reverse-scan is on), but its doing a table scan.
Have I misinterpreted what a reverse-scan is?
Yes you have. reverse scans means the index can be scanned in reverse
ORDER of the index entries, not in reverse order of the index column.
You can search for a name in a phone book by starting at the end. But
that phone book doesn't provide you a way to look up by firstname first :-)
If you only want to afford one index you can consider placing the middle
column first in the index. That way the index can at least be used
partially for the second query type.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 23 '06 #2

P: n/a
On Tue, 22 Aug 2006 21:56:39 -0400, Serge Rielau wrote:
natG wrote:
>On a 3 [INTEGER] column table, the PK consists of all 3 columns.
I [also] have queries on the first two columns, as well as on the last two
columns. (Select * where column3=x and column2=y.)
I was hoping that this same pk index will suffice for the latter
(reverse-scan is on), but its doing a table scan.
Have I misinterpreted what a reverse-scan is?
Yes you have. reverse scans means the index can be scanned in reverse
ORDER of the index entries, not in reverse order of the index column.
You can search for a name in a phone book by starting at the end. But
that phone book doesn't provide you a way to look up by firstname first :-)
If you only want to afford one index you can consider placing the middle
column first in the index. That way the index can at least be used
partially for the second query type.
Thanks for the correction (and the explain).
nat

Aug 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.