| Using a field with low cardinality in a where clause. [message #19547] |
Tue, 26 March 2002 20:14  |
Nabil Fanaian
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
Let's say I have a table defined as:
CREATE TABLE TABLEA( F1, F2, F3 )
Now lets say that the table has 1mil 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!? I works fine with the DISTINCT taken out, but I need to use the DISTINCT clause in my SQL stmt.
Thanks.
|
|
|
|
|
|
|
|
| Re: Using a field with low cardinality in a where clause. [message #19573 is a reply to message #19551] |
Thu, 28 March 2002 15:29  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
Hi,
I think the reson why he does a full table scan is the following: the access of a row via an index costs about 5 or 10 times more ressources then a full table scan. So because you access about one third of your data with your condition WHERE F3 = 'A' the FTS is probably faster.
Because you want to get the distinct data the database has also to compare each record to see if it is a distinct value. A solution for this would probably be, as Alex said, to create a index like:
create index some_index on TABLE(f3,f2,f1)
the f3 value has to be the first column listed in the index.
HTH
Mike
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
