Re: SQL optimization question..

Shashank Tripathi <shanx_at_shanx.com> wrote in message news:3858e6d2.38008809_at_news.netvigator.com...
> Hi
>
> I am using a really neat SQL optimiser which scans my joins etc and
> compares them with the table definitions etc and returns better
> recommendations for the SQL. I am amazed by the difference: one of my
> SQL queries used to take 32 seconds in SQL*Plus and now it takes 4
> seconds.
>
> However, alls not perfect. The SQL query that the system suggests has
> some funny stuff thrown in. For example, my original query had the
> following statement :
>
> ....WHERE
> PARTNER_STATUS = 'ACTIVE'
> ....
>
>
> The query optimiser returned stuff like:
>
>
> ....WHERE
> PARTNER_STATUS||'' = 'ACTIVE'
> ....
>
>
> And believe it or not, this returns a much faster response! Just that
> concatenation with '' (nothing) on the left hand side!
>
> My question is: whats happening here? I am amazed and happy, but
> clueless. Would appreciate any ideas..

It looks that your index is a b-tree index of low selectivity. In this situration, using index access may be slower than full table scan. Received on Fri Dec 17 1999 - 09:35:35 CST