Greetings and Salutations ….and of-course Happy Easter to all .
Talking about Easter, being a programmer the first thing that strikes my mind is Easter eggs present in different software solutions. 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 ( this is Demo data roughly 34k Job rows ) –
Well things look simple here. Lets see if adding in a magic clause can bring this cost down .
New Explain plan.
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.