Oracle - Tuning SQL Statements

Answers collected from the below site.Please refer to the site for details.
Tuning SQL Statements
1.Oracle SQL Analyzer:statement TopSQL:Hint Wizard:SQL Tuning Wizard
2.Index:full table scans:Oracle cost-based optimizer
3.specifying Hints:

Oracle SQL Analyze applies these "rules-of-thumb" when you tune a statement with the Tuning Wizard, and supplies alternative SQL statements when possible.

Use NOT EXISTS instead of NOT IN ( NOT IN uses full Table scan)
Use NOT EXISTS or NOT IN with hints instead of MINUS (MINUS does not use Indexes)
Use TRUNC differently to enable indexes ,Use operators differently to enable indexes
Do not use columns on both sides of operator,Use WHERE in place of HAVING,
Use UNION ALL instead of UNION

The logic of the NOT EXISTS clause tells Oracle not to return the row if it finds a match in both tables.

NOT IN" vs "MINUS": "NOT IN" is much slower than "MINUS" as for "NOT IN" for each row the subquery all rows are scanned.In "MINUS" at one time both table Full Scan is done.

SELECT mod_code FROM SRS.Table1 WHERE mod_code NOT IN (SELECT mod_code FROM SRS.Table2);

SELECT mod_code FROM SRS.Table1 MINUS SELECT mod_code FROM SRS.Table2;