A drop of performance testing-SQL Optimization 3

Decode function in PL/SQL has the function with if-then-else in this language’s split mode. To use the function can reduce the times of scanning same records and connecting same tables.Totally,  it can reduce process time to some degree.

How to use it then?  Make  an example below:

 

  Now we choose a master table in DB, for example:  SQL: select * from ***; DB Snapshot is as below:

Fig1:

 

Now we add a column in return result(result1) whose value according to another column sls_qtn_id , and an existing column (uqid)  

And the decode function as below:(we just select top 19 rows)

select uqid,
  decode(sls_qtn_id,408,'test1',
                   409,'test2',
                   522,'test3',
                   523,'test4',
                   'testdefault') result1
                   from *** where rownum<20;

 

and if execute the sql , the result is:

 

Fig2:

 

 

The decode function is equal to if-then-else as below:

 

If sls_qtn_id =408 then

Result1:=’test1’;

elsIf sls_qtn_id =409 then

Result1:=’test2’;

 

elsIf sls_qtn_id =522 then

Result1:=’test3’;

 

elsIf sls_qtn_id =523 then

Result1:=’test4’;

Else

Result1:= ‘testdefault’;

 

When in our code development about pl/sql please remember this lovely function.