SQL Blues: Get Happy with 10 Tips
July 15, 2009
A happy quack to the reader who sent me a link to “10 Tips for Working Smarter with SQL”. I am sufficiently old and addled to remember the joy of crafting by hand complex SQL statements. I even remember the great little tool that was made available by either Illustra, Informix, or another old school database vendor. The Web page would permit one to enter a SQL statement, and then respond with a mark up of that statement. I recall that the little tool worked quite well, then it disappeared, and I had to return to more traditional ways of coaxing Dr. Codd’s invention to spit out what I wanted. If you are working with Oracle Ultra Search (Oracle Text) or Thunderstone search systems, you will need to have some familiarity with SQL or SQL variants.
I downloaded and saved Susan Harkins’ (TechRepublic) article because it contained several quite useful tips. I can’t reproduce the full list. But I want to highlight two of her tips and urge you to visit Builder.au to garner the rest of the insights.
First, she does a very good job of reminding me about the differences between ALL, DISTINCT, and DISTINCTROW. She includes a useful table which I immediately printed and taped in my database notebook. (Yes, I still use paper.)
Second, she makes short work of the UNION operator. A glitch here can trash tables, forcing addled geese like me to reopen the two tables and rerun the instruction. She wrote:
By default, UNION sorts records by the values in the first column because UNION uses an implicit DISTINCT predicate to omit duplicate records. To include all records, including duplicates, use UNION ALL, which eliminates the implicit sort. If you know there are no duplicate records, but there are a lot of records, you can use UNION ALL to improve performance because the engine will skip the comparison that’s necessary to sort (to find duplicates).
Good work this.
Stephen Arnold, July 15, 2009
Comments
-
- Subscribe to Beyond Search
Feature archive
News archive
-
