Oracle Tuning: SQL Performance Tuning-Nested Queries

Nested Subqueries
Using nested sub queries instead of joining tables in a single query can lead to dramatic performance gains. Only certain queries will meet the criteria for making this modification. When you find the right one, this trick will take performance improvement to an exponentially better height.  The conditions for changing a query to a nested sub query occur when:
Tables are being joined to return the rows from ONLY one table.
Conditions from each table will lead to a reasonable percentage of the rows to be retrieved (more than 10%)
The original query:
SELECT     A.COL1, A.COL2
FROM     TABLE1 A, TABLE2 B
WHERE     A.COL3 = VAR
AND     A.COL4 = B.COL1
AND     B.COL2 = VAR;

The new query:
SELECT     A.COL1, A.COL2
FROM     TABLE1 A
WHERE     A.COL3 = VAR
AND     EXISTS
(SELECT     ‘X’
FROM     TABLE B
WHERE     A.COL4 = B.COL1
AND     B.COL2 = VAR);

A real life example:
SELECT    ORDER.ORDNO, ORDER.CUSTNO
FROM    ORDER_LINE OL, ORDER
WHERE    ORDER.ORDNO = OL.ORDNO
AND    ORDER.CUSTNO = 5
AND     OL.PRICE = 200;

Execution Time: 240 Minutes

The solution:
SELECT    ORDNO, CUSTNO
FROM    ORDER
WHERE    CUSTNO = 5
AND EXISTS
(SELECT     ‘X’
FROM     ORDER_LINE OL
WHERE     ORDER.ORDNO = OL.ORDNO
AND OL.PRICE = 200);