SQL optimization question..

From: Shashank Tripathi <shanx_at_shanx.com>
Date: Thu, 16 Dec 1999 13:28:39 GMT
Message-ID: <3858e6d2.38008809@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 - 07:28:39 CST