Re: Using a field with low cardinality in a where clause.

On Wed, 27 Mar 2002, nabil_fanaian_at_fe.ro wrote:
> I start receiving records right away, which is what I want.

Well, you are doing SELECT F1,F2,F3 from TABLEA WHERE F3='A'? Once it finds one row, it can show it to you. With a distinct, it can't know it has the distinct set until it looks at the entire set of data.

>
> Well, seems like the only way around this problem was to get rid of
> the DISTINCT clause... I was finally able to get the DISTINCT out of
> the SQL stmt and now it performs great.
>
> However, if you can think of a way to optimize the query using the
> DISTINCT, that would be even better.

SELECT DISTINCT F1, F2, F3 FROM TABLEA WHERE F3 = 'A' If F3='A' is 1/3 of the table, I don't know how you could optimize it. Maybe an index with all the referenced columns? With an index on just F3, Oracle still has to go to the disk to get F1 and F2 so that it can then get you the distinct set. If it has an index which has all the data, like one on F1, F2 and F3, then it can just use the index. Other than that, with 1/3 of the table coming back, you want Oracle to FTS.

Does this make sense?

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.