Mark, one of the basic rules of the database design is to put the most
selective column first in a multi-column index. Putting a column with
very few values as the first column of a multi-column index goes against
the time tested design rules and is guaranteed to cause problems. I have
always treated skip scan as a performance problem.
As for the 42 billions, I do agree that the answer to the question of
the indexes, databases and everything is 42. I believe that's in one of
the Jonathan's books, along with the SEP field of the tablespace
headers. BTW, I wonder what will be the Oracle version at the end of the
universe?
Regards
On 8/17/21 8:51 PM, Mark W. Farnham wrote:
If your first column is just two values, say, either Y or N, then a skip scan
filtering on the second in each of Y and N will be the good polar case of skip
scan. (Well, actually all values equal would be even better, but that would
also be a silly column to have and index).
If you have 42 billion different values for the first column only a few of
which have the desired value in the second column, that will be toward the
polar bad case.
