Easter Egg – Magic Clause in Oracle-SQL

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 –

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 ( this is Demo data roughly 34k Job rows ) –

SQL-1

Explain Plan

SQL-2

Well things look simple here. Lets see if adding in a magic clause can bring this cost down .

SQL-3

New Explain plan.

SQL-4

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.

Share

Leave a Reply