Using a field with low cardinality in a where clause.

From: Nabil Fanaian <nabil_fanaian_at_fe.ro>
Date: Wed, 27 Mar 2002 01:13:35 -0500
Message-ID: <a7ro96$em0$1@news-int.gatech.edu>

Let's say I have a table defined as:
CREATE TABLE TABLEA( F1, F2, F3 ) Now lets say that the table has 10mil records in it, but there are only 3 distinct values for F3. If I execute the following query, Oracle 8i performs a FTS and takes 35 secs to return.
SELECT DISTINCT F1, F2, F3 FROM TABLEA WHERE F3 = 'A' I create a bitmap index on F3 and Oracle still performs a FTS. I create a hint to use the bitmap index and the query takes 50 secs. Why is this happening?

However, if I remove the DISTINCT from my SQL statement, then the query completes in milliseconds.

How can I get this SQL to execute most efficiently when I need to use DISTINCT!? It works fine with the DISTINCT taken out, but I need to use the DISTINCT clause in my SQL stmt.

Thanks. Received on Wed Mar 27 2002 - 00:13:35 CST