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.
Comments