Re: Crude SQL - recommend improvement

Randy, you have full table scans because Oracle is using hash joins instead of nested loop joins to solve the query. Make sure all the table statistics are current and properly computed. You said the tables are small and if the hash area size is in fact large enough to hold the row set then this may be better than indexed access.

To find out you can set hash joins off in your session, run a plan to verify nested loops are now in use, then run a time test. Remember that blocks cached into the buffer pool by one test may affect the performance of followup tests.

Also prior to 10g an outer join pretty much guarentees that the table being outer joined to has to be the driving table in the join so that every row from that table can be produced. In some cases this can mean an index from A into B you thought was available is unusable in solving the query since the path has to be B to A and the needed indexed access does not exist.

HTH -- Mark D Powell -- Received on Fri Jan 14 2005 - 09:16:09 CST