SQL Tuning

Can anyone help me to understand the following sql especially on the first line where 0 = and the fifth line ${sql.as} mytable_inner?
Also, any suggestion how to rewrite for better performance, thanks!

SELECT * FROM mytable where 0 =

              (SELECT fixed FROM yourtable WHERE yourtable.gm_id = mytable.gm_id) AND

              (EXISTS ( SELECT 1 FROM thetable WHERE thetable.gm_id = mytable.gm_id AND

             thetable.status IN ('Good', 'Bad', 'Ugly')) OR

             EXISTS (SELECT 1 FROM mytable ${sql.as} mytable_inner WHERE mytable_inner.gm_id = mytable.gm_id

             AND mytable_inner.date_todo < mytable.date_todo AND

             mytable_inner.date_todo >= ${sql.currentDate}))

;
lium1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>> on the first line where 0 =

That is a simple where equality.

It is comparing 0 to the value returned by the select that follows it.

>> the fifth line ${sql.as} mytable_inner?

No idea here.  That isn't Oracle or .Net that I know of.
slightwv (䄆 Netminder) Commented:
>>mytable ${sql.as} mytable_inner

If I had to guess, that is some variable that just returns 'as'.  

so:
mytable ${sql.as} mytable_inner

becomes:
mytable as mytable_inner

For table aliasing in Oracle, 'as' is optional.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
These two elements in your statement do not look like typical Oracle syntax to me:
  ${sql.as}
  ${sql.currentDate}
My guess is these come from an application of some kind, or maybe this really isn't an Oracle question, and these values are legal in some other database, like maybe SQL Server?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
They are not SQL Server (or Sybase) variables

I think it is PHP

But I do not believe the query is accurate because the syntax of this line would cause an error (where bolded):

EXISTS (SELECT 1 FROM mytable ${sql.as} mytable_inner WHERE mytable_inner.gm_id = mytable.gm_id
slightwv (䄆 Netminder) Commented:
>>because the syntax of this line would cause an error

Not if ${sql.as} is replaced with, as the name implies, the word "as" (without the quotes of course).
johnsoneSenior Oracle DBACommented:
As others have already said, looks to be written as some way to be portable code that can be used on multiple database systems.  The substitution variables will change the syntax for different databases.

That being said, I wouldn't think that tuning is much of an option.  Database agnostic queries have this inherent problem.  What you would do to make the query "better" in one database system isn't going to work in all of them.
lium1Author Commented:
To make it work with Oracle, I have removed ${sql.as} and replaced ${sql.currentDate} with sysdate!
slightwv (䄆 Netminder) Commented:
>>replaced ${sql.currentDate} with sysdate!

Oracle dates have a time portion build into them.  So, sysdate is the exact second the function is called.  This might return incorrect results.

You might want to make it trunc(sysdate).  Trunc zeros out the time portion.
PortletPaulEE Topic AdvisorCommented:
>>Not if ${sql.as} is replaced with, as the name implies, the word "as" (without the quotes of course).

quite right, my bad, sorry.

It's a curious thing however; making the alias longer than the original tablename
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.