Oracle: Function vs Stored Procedure

Difference between procedure and funtion:

1. Function always have a returns value where as procedure does not have a return value, but you can use OUT parameter for this purpose.
2. Function can be used in SELECT statement where as procedure can not. eg. SELECT GetName(v_id) FROM DUAL;
3. You can have DML(Insert, Update, Delete) DDL(TRUNCATE, Drop), TCL(COMMIT, ROLLBACK) in a function but then you can not call that function in any SQL query.
   

Formatting SQL Code

Tired of formatting your code manually. Just try this online tool, its free!!
http://www.dpriver.com/pp/sqlformat.htm

Oracle: EXISTS vs IN

We have a general perception that EXISTS is faster then IN. This is not always true. There can be some cases where IN performs better then EXISTS.

This depends on the size of the tables (and data queried) Using "IN", the optimizer generally runs the subquery first, then joins with the main dataset (nested loops, merge, whatever). If the table in the subquery is small, and the table in the main query is large, then IN usually makes sense.

Using "EXISTS", the optimizer generally runs the main query first, then applies that dataset to the subquery. If the table in the subquery is large, and the table in the main query is small, then EXISTS usually makes sense.

By the way, Full Table Scan are not necessarily bad. If you are querying more than, say 15 - 20%, of the data in a table the Full Table Scan may be faster. Two Full Table Scans with a Hash Join are frequently gobs faster than a Nested Loops type of plan.