The concat forces the optimizer to NOT use the index that is on that column.
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..
>
> Thanks
> Shanx
>
>
> -------------------------------------
> It's been my observation that 90%
> of statisticians pretty much ruin it
> for the other 30%.
>
> Shashank Tripathi
> http://shanx.com
> -------------------------------------
Received on Thu Dec 16 1999 - 10:26:42 CST
