I like cake, and like people world over I know that the best cake was the cake my grandmother made. So good in fact there is a family hand-me down recipe book so that the family that followed her can make cakes as good as the ones she made when she was around.
Likewise some database developers rely on a series of tried and tested recipes to write SQL queries
One of the problems with old recipe books is that the world has moved on. This is true for both cakes and for SQL. My grandma baked in a coal-fired range oven, weighed ingredients in pounds and ounces and had a very limited larder. Somethings translate (such as weights and measures) but hardware properties and ingredient availability less readily. For Grandma Brown and her SQL recipes we also find that the things she always did and handed down to you may not give the best results now.
Enough of the cake analogy – what I am getting to is that just because you did something once in the past and it seemed to give the correct result does not mean it is right to use it every time or is even generally applicable.
Or (for youngsters) UNIQUE, can have its uses – I sometimes use if for quick and dirty profiling to get a better feel for the range of data and its cardinality in a table, however I prefer to use group by with an actual count of values for this. There are three common problems I see with this:
The distinct does nothing useful in some SQL. Some database operations implicitly work over a distinct set of data, for example UNION, GROUP BY and IN(SELECT DISTINCT COL1 from TABLE1). A DISTINCT won’t change the result but may make the query slower to execute as it will attempt to find duplicates that don’t exist.
There is no point doing a distinct on data that can’t have duplicates such as SELECT DISTINCT primary_key from TABLE1; or even SELECT DISTINCT DUMMY from DUAL;
It is even possible that the use of DISTINCT/UNIQUE is masking a bug in your query logic such as a missed join condition or a filter condition.
Don’t get me wrong here, aliasing a table in a query is good thing. Well chosen aliases make a query easier to read and understand (a good thing if you are doing support), they provide clarity of the source table of two identically named columns. Where this goes wrong is when instead of meaningful aliases such as PD for PRODUCT_DIM you develop using single letter alias that have no relationship to the actual tables. Working out what A, B and C relate to is hard enough without coming across in-line views that use the same A,B and C alias convention but referring to different source tables. Talking of in-line views…
When Grandma started out some ingredients didn’t exist, now it is perfectly possible to use Common Table Expressions (CTE) as a replacement for many in-line views. The CTE offers many advantages over the in-line view, such as being able to use the same CTE many times in a query, the possibility of recursion, the possibility to hint the CTE and improved clarity of the SQL (easier to support). However, it is not the exact same thing as an in-line view so expect different query plans. Sometimes it is necessary to force Oracle to materialize the CTE and sometimes you need do the reverse and force it not to materialize it. There are hints to do that. which brings me to hints.
Most optimizer hints are a way of telling the cost based optimizer that you know more about your data than it has obtained from the statistics on tables and indexes and from other information obtained from table structure such as keys and constraints. If there was such a thing as a universal hint that would benefit all queries you would think that Oracle would have made it a default. There is no such thing; do not apply the same hint to every query as a go faster switch it will mainly not work and could make things worse, much, much worse. To my mind hints are useful when your are trying to optimize a query. Once you work out the best plan you can look at giving extra information to the optimiser so that an unhinted query has the same plan. The other thing that people following recipes often get wrong is mixing hints with aliases, it is so easy to malform a hint.
I have old US cake recipes that uses General Purpose flour. We don’t have anything called that here in France so I am going to have to use something different. Likewise when we translate code from one vendor’s SQL to another we come across things that would work a lot better if they were translated into the right equivalent. This is particularly common with conversion functions such as dates to strings. Sometimes we can replace complex hand-written functions that do string manipulation with vendor supplied in-database functionality.
In the past people cooked things like roast swan stuffed with sturgeon. The probably don’t now. Our tastes and skills have evolved. My grandma never vacuum packed and water-bathed a thing in her life. Similarly SQL has evolved. If your coding style is stuck in Oracle 7.3 you will miss out on great things like Analytic Functions, the model clause and match recognise. Using some new methods can give great results, but use them in the right places
I suspect that other people can add further chapters to the recipe book.