First run a query on the big table with out joining the small table but with all other predicates on table1.
tamilselvan:

I run the query on the big table without join with the small table, here is the explain plan:

Code:
SELECT STATEMENT, GOAL = FIRST_ROWS			Cost=87763	Cardinality=1	Bytes=20
 SORT AGGREGATE				Cardinality=1	Bytes=20
  TABLE ACCESS FULL	Object owner=joe	Object name=table1	Cost=87763	Cardinality=1462134	Bytes=29242680
The Cardinality and Cost are pretty much the same as the original query.

Do you think it will help if I add an index on table1.col3?

Please advice.

Thanks