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 –
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)
-- 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.