The reason it takes so long with the distinct is that it has to compare each
record with each other. Which is why the bitmapped index did not help. So
are you telling me that the combination of f1, f2, and f3 is not unique?
Which means that your table does not have a primary key. If so then this
will be faster:
select f1,f2,f3 from tablea where f3=:hv group by f1,f2,f3;
(:hv is the host variable, you should be using host variables, but for the
test replace :hv with 'A')
Jim
"Nabil Fanaian" <nabil_fanaian_at_fe.ro> wrote in message
news:a7ro96$em0$1_at_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 - 06:36:59 CST
