Friday, March 14, 2003

This is really simple, but simple is what I am all about.  When using SELECT DISTINCT from huge tables with many joins, it forces a full table scan and can slow down a query.  SELECT DISTINCT blah blah blah blah blah blah blah FROM A,B,C WHERE A.CUST_ID = B.CUST_ID AND blah blah blah blah blah AND blah Would be inefficient To get around this put the distinct on the outside of a subquery: Select Distinct blah, blah, blah, blah FROM (SELECT blah, blah, blah, blah, blah, blah, blah, FROM A,B,C WHERE A.CUST_ID = B.CUST_ID AND blah AND blah AND blah  AND blah ) 4:05:18 PM    comment []

Exporting Files from Toad.  Toad brings the query results to your desktop. This can be a bad thing if it is a huge query and you need a few tries to get it right. Toad will hog your system resources while it imports all the data from the query. It can be a good thing in that when you go to export the data to a file, it doesn't require another query. When saving to a delimited file, use the SaveAs feature available from right clicking on the result grid. If you check "include null text" any field with no value will show up with the word 'null' in it. If you are doing mailing labels, this is not good. Tab delimited format seems to have been the safest for me. Comma delimited gave me big problems if there was a stray comma in any text values in the result set. 3:55:14 PM    comment []

© Copyright 2003 mcgyver5.