asked on

SQL Optimizer modified my SQLbut I don't understand the syntax that it created. Any SQL gurus out there to explain it please?

I had an Oracle SQL SELECT query that was running very slowly (upwards of 6 minutes) that, as a result, was timing out the web server trying run said query.

One of the tools that ships with Toad for Oracle is a query optimizer. I ran the code through the optimizer and it returned an new version of the SELECT query that ran in 300ms! Very impressive. The only problem is that I don't understand the syntax and I was hoping that someone could explain it to me?

The only thing that the optimizer did was to append a concatenation to the end of my comparisons (see code snippet below)

Basically it added:

|| ''

Can someone please explain what || '' does?

Typing in those characters into Experts/Goole search only serves to crash the search engine! LOL

Thanks!

Alex

--OLD VERSION
SELECT c.alm_cd
FROM gxadmin.alm c
WHERE '2008' = c.fscl_yr
AND ('04' = c.all_typ OR '05' = c.all_typ))
AND b.fscl_yr = t.fscl_yr 
AND b.fscl_yr = r.fscl_yr
... etc
 
-- NEW VERSION
 
SELECT c.alm_cd
FROM gxadmin.alm c
WHERE '2008' = c.fscl_yr
AND ('04' = c.all_typ OR '05' = c.all_typ))
AND b.fscl_yr = t.fscl_yr || ''
AND b.fscl_yr = r.fscl_yr || ''
... etc

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

imitchie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ajovan

ASKER

Thanks to both of you for answering so quickly!
I tried the to_char and it worked as well (slower that the || version: 4.5 seconds, but still a HUGE improvemnet over the 6+ minutes)

Interestingly, comparing source table columns they were all Char(4)... I wonder if when you specify a string like '2008' that it takes it as a varchar as a default, thereby requiring the conversion?

Once again thanks VERY much - I've never seen that || '' used in that manner before - and like I said - it's practically IMPOSSIBLE to search on bitwise or's

PS - since you both had the same answer, 2 minutes apart, with sighlightly different skews on it I was really torn on whom to give the points to... Faced with the age old question, angellll or imitchie, - I split them.

Thanks again!
Alex

Guy Hengel [angelIII / a3]

>Interestingly, comparing source table columns they were all Char(4)... I wonder if when you specify a string like '2008' that it takes it as a varchar as a default, thereby requiring the conversion?

if that is the case, you probably have an index on the fields, and the "trick" is used to "skip" deliberately the index.
please compare the explain plan for the query with and without the || ''

imitchie

Even though you have not showed all tables, my guess is that an explain would have shown that it is using the index on table t's fscl_yr. Making it || '' will cause it to ignore that one and force the query down the other table's index first. I think TOAD is trying to skew Oracle's query plan