interesting index design question.

[natG]

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

[Serge Rielau]

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/

[natG]

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