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

Yes the combination of f1,f2,f3 is unique. f1 is actually the PK. Thing is that the SQL is actually being created and executed by a thrid party app. The app is placing the DISTINCT clause into the SQL stmt - I have no control over reformatting the SQL.

Nabil.

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:L1jo8.144133$q2.13548_at_sccrnsc01...
> 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 - 12:16:18 CST