Magic Clause in Oracle-SQL

Greetings and Salutations ….and of-course Happy Easter to all .

Be it the windows you use or the phone OS you are using , be it the search you do on Google… there is something hidden and to be discovered , a footprint that the developer has left as his/her signature.

There are list of few of Easter Eggs –

https://en.wikipedia.org/wiki/List_of_Easter_eggs_in_Microsoft_products

https://en.wikipedia.org/wiki/List_of_Google_Easter_eggs

Well , that was a bit of distraction .. coming back to our reference to magic clause. Many of my friends must have done a lot of SQL tuning and tried different tricks and techniques to bring down the SQL run-time.

SQL Tuning is an Art and every time you start tuning there are many hidden gems and new things you learn. Here I am sharing one of the trick that helps bring down the SQL Cost at least by 10 times in most of cases , okay I add a caution to last statement it will apply to most cases but in certain scenario’s it will depend on the resources in use ( CPU , I/O ) and above all the SQL structure too.

I write a lot of SQL related to PS-HCM and the most common of the statement is fetching effective dated row from PS_JOB.

So without wasting time lets dive into details. Effective dated row from JOB and its explain plan is as follows ( P37-HCM-Data- 3443 Rows )

-- SQL Query 

SELECT * FROM PS_JOB J 
WHERE 
  J.EFFDT = ( 
 SELECT MAX (J1.EFFDT) 
  FROM PS_JOB J1 
 WHERE J.EMPLID = J1.EMPLID 
   AND J.EMPL_RCD = J1.EMPL_RCD 
   AND J1.EFFDT <= SYSDATE) 
   AND J.EFFSEQ = ( 
 SELECT MAX (J1.EFFSEQ) 
  FROM PS_JOB J1 
 WHERE J.EMPLID = J1.EMPLID 
   AND J.EMPL_RCD = J1.EMPL_RCD 
   AND J.EFFDT = J1.EFFDT) 

Explain Plan before Magic Clause
-- SQL Query with Magic Clause 


SELECT * FROM PS_JOB J 
WHERE 
J.EMPLID = J.EMPLID || Substr ( UID , 1 , 0 )
AND J.EFFDT = ( 
 SELECT MAX (J1.EFFDT) 
  FROM PS_JOB J1 
 WHERE J.EMPLID = J1.EMPLID 
   AND J.EMPL_RCD = J1.EMPL_RCD 
   AND J1.EFFDT <= SYSDATE) 
   AND J.EFFSEQ = ( 
 SELECT MAX (J1.EFFSEQ) 
  FROM PS_JOB J1 
 WHERE J.EMPLID = J1.EMPLID 
   AND J.EMPL_RCD = J1.EMPL_RCD 
   AND J.EFFDT = J1.EFFDT) 

Usage and trick here is in Complex SQL , Identify the key field and add a clause like this .

Key Field = Key Field || Substr ( UID , 1 , 0 )

This clause does no impact on the SQL result count but only helps in enhancing the performance… On that note I will leave this Easter egg …. without further explanation and leave it you; to use and discover .. do leave a comment if it helped in any manner in your SQL performance.

A last note to make things more complex .. in some scenarios a explain plan showing lower cost does not always equates to a shorter execution time.

Have a nice day.