Oracle Spool command

The Oracle Spool command is a nice little feature that allows you to record what goes on in your sqlplus session on the fly. Enabling it is simple and can be a very good logging/debugging tool.

To record your sqlplus session commands, simply type:

SQL> spool /tmp/myfile.txt

where myfile.txt is the name you want to give to your log file.
The file will not appear or be usable until you turn off spooling:

SQL> spool off


Voila!

EXISTS is your friend

I recenty came across a problem that required me to retrieve data from a table based on certain criteria. I decided to use the SQL 'IN' condition. This, as I learned later, was a poor choice given the fact that the subquery in the 'IN' clause returned several hundred thousand records. 

My query construct was the following: 

SELECT foo, bar FROM table1 

WHERE foo IN (SELECT foo FROM table2 WHERE condition1 AND condition2)

AND bar IN (SELECT bar FROM table2 WHERE condition1 AND condition2);

Each of those two subqueries returned ~260,000 records, which, needless to say, took forever and a day to complete. I later discovered that the IN clause should mostly be used for small result sets, basically for when the subquery returns less than 1024 records. For result sets as massive as the ones I was getting, it is better to use the EXISTS clause. My query above could easily be modified to use EXISTS in the following way:

SELECT table1.foo, table1.bar FROM table1

WHERE EXISTS (SELECT table2.foo, table2.bar from table2 

                                WHERE  table1.foo = table2.foo 

AND table1.bar = table2.bar

AND condition1

AND condition2);

As you can see, you have to join the two tables being used in order for it to work, otherwise EXISTS will return true and the query will just return the results of the master query.

Remember, when dealing with large result sets, EXISTS is your friend!