|
Home
Page
Back to Papers Logic-driven Column Calculations User-definable Report Ordering
|
Please note this page has moved to http://oracledoug.com/decode5.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-) Mind the Trap! For our next example, we need to imagine that our emp and dept tables contain much larger volumes of data than the example tables supplied with Oracle, say 500 departments and many thousands of employees. Our previous report would have generated figures for all 500 departments and now the HR Manager says 'Very nice, but I really only want to look at the total salary figure for the Sales and Research departments'. Not wanting to disappoint our favourite user, we couldn't possibly suggest a little spreadsheet activity on the manager's part, so we rework our DECODE, as shown in example 3a. Example 3a This will produce the total of SALES employee's salaries * 1.2, plus the total of RESEARCH employee's salaries. Where an employee is not in either of these two departments the default value of NULL will be returned, so that they won't affect the total. HR gets what it's looking for, we go down the pub and everyone's happy. Well, not really. The problem here is that one of the main strengths of DECODE can encourage us to produce logically consistent but inefficient code if we're not careful. The thing to keep in mind is that DECODE is a post-retrieval function. What this example will do is happily (if slowly) trawl through every employee's data, then apply a DECODE function to it in such a way as to exclude most employees from the result. So maybe we shouldn't read the employees data in the first place! A better way of achieving the same result is shown in Example 3b.SELECT SUM(DECODE(d.dname, 'SALES', e.sal * 1.2, 'RESEARCH', e.sal) total_salary_bill FROM emp e, dept d WHERE d.deptno = e.deptno; TOTAL_SALARY_BILL ----------------- 22155 Example 3b There is one small difference, a new line in the WHERE clause which restricts the query to employees in the SALES and RESEARCH departments. In practice, the situation is complicated by whether Oracle is able to use indexes to optimise the performance of the WHERE clause to reduce the amount of work that the server needs to perform, but I suggest you follow this basic approach in all cases. There is no point in using DECODE functions to improve the execution plans of SQL statements, only to introduce new performance problems. Remember, remember, remember – DECODE is a post-retrieval function.SELECT SUM(DECODE(d.dname, 'SALES', e.sal * 1.2, 'RESEARCH', e.sal) total_salary_bill FROM emp e, dept d WHERE d.deptno = e.deptno and d.dname IN ('SALES','RESEARCH'); -- New Line TOTAL_SALARY_BILL ----------------- 22155 |