SQL language gain a lot with introduction of the “Case” statement.
Many SQL programmers are using it, as it is more logical way to accomplish some task without even thinking that SQL language is much older then Case statement, and there is always another (more efficient) way to get the same result.

In this post I’ll explain scenario when my colleague create SQL statement for report that, in the first iteration, is using Case statement to determine if select statement will be executed or not.


select * from shipsku
    INNER JOIN (
            SELECT im.item, 
               CASE NVL(im.item_parent, 'NULL')
                    WHEN 'NULL' THEN CASE (SELECT COUNT(item) FROM item_master tbl WHERE tbl.item_parent = im.item)
                                    WHEN 0 THEN im.item
                                    ELSE NULL
                                  END
                    ELSE im.item_parent
               END AS item_parent,
               im.item_desc
            FROM item_master im
            WHERE primary_ref_item_ind = CASE NVL(item_parent, 'NULL')
                                            WHEN 'NULL' THEN 'N'
                                            ELSE 'Y'
                                         end 
   ) item_master on shipsku.item = item_master.item_parent
   where shipsku.shipment = 491;

10 rows selected.

Elapsed: 00:00:02.97

Statistics
----------------------------------------------------------
     43  recursive calls
      1  db block gets
     390090  consistent gets
    308  physical reads
      0  redo size
       2065  bytes sent via SQL*Net to client
    365  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     10  rows processed

Shipsku table has 150+ million rows and item_master half million.

In the second attempt, join has been used.


select * from shipsku
    INNER JOIN (
                SELECT im1.item, im1.item AS item_parent, im1.item_desc, im1.primary_ref_item_ind
                FROM item_master im1
                WHERE im1.item_parent IS NULL AND (
                    SELECT COUNT(im.item)
                    FROM item_master im
                    WHERE im.item_parent = im1.item
                ) = 0 AND primary_ref_item_ind = CASE NVL(item_parent, 'NULL')
                                                    WHEN 'NULL' THEN 'N'
                                                    ELSE 'Y'
                                                 END
                UNION ALL
                SELECT im2.item, item_parent, im2.item_desc, im2.primary_ref_item_ind
                FROM item_master im2
                WHERE im2.item_parent IS NOT NULL AND primary_ref_item_ind = CASE NVL(item_parent, 'NULL')
                                            WHEN 'NULL' THEN 'N'
                                            ELSE 'Y'
                                         end
   ) item_master on shipsku.item = item_master.item_parent
   where shipsku.shipment = 491;

10 rows selected.

Elapsed: 00:00:00.04

Statistics
----------------------------------------------------------
    148  recursive calls
      0  db block gets
    171  consistent gets
      0  physical reads
      0  redo size
       2109  bytes sent via SQL*Net to client
    365  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     10  rows processed

As you can see, SQL execution times has been significantly improved (2.97 seconds—>0.04 seconds).

The main difference is consistent gets which dropped from 390090 to 171.

Point is you should never overlooked old features and blindly favor only a new one.



Get notified when a new post is published!

 

Loading

Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.