|
Home
Page
Back to Papers Logic-driven Column Calculations User-definable Report Ordering
|
Please note this page has moved to http://oracledoug.com/decode11.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-) The Up-side DECODE gives us the power to not just retune the access paths of our SQL queries, but to take a step back from our code, look at our requirements and take a completely different approach to the task. Instead of limiting our tuning efforts to improving the speed of individual queries, we can reduce the overall number of queries to retrieve all the data we require and then use DECODE to perform certain post-retrieval tasks. This reminds me of one of the first pieces of Oracle tuning advice I heard, which still holds true today. Reduce the number of 'trips' to the database to the minimum required to achieve the objective. If a report is performing multiple accesses against the same tables it is worth examining whether these might be combined. DECODE bridges the gap between pure SQL and embedding SQL in 3GLs. In some cases, the only reason that we use a 3GL is to perform cursor loops that allow us to apply additional processing to the data, row by row, based on the column values. We can often use DECODE to perform that additional processing instead. DECODE works with all versions of Oracle and isn't dependant on optimiser improvements in newer versions. This is because the performance advantage comes from taking a different approach to the problem that requires Oracle to perform less work, regardless of which optimiser is in use. The way I see it, the optimiser can only really be expected to optimise your access paths, not attempt to rewrite your code more efficiently (although I'm sure this will happen in time). Like most performing tuning activities, the big improvements come from making smart decisions about your approach in advance. |