1. Both the tables have around 1.5 (a little over 15 Lakhs of records.)

2. Explain plan is as follows.

1.2632 SELECT STATEMENT skd cost =2632
2.1 SORT AGGREGATE
3.1 HASH JOIN
4.1 INDEX FAST FULL SCAN I2 NON-UNIQUE
4.2 INDEX FAST FULL SCAN I1 NON-UNIQUE

3. These two tables on that particular two fields we need to use the where clause, becuae we need some statistics.

These two tables will not be be required after our work is done. So after taking statistics we will drop it.

4. Is it natural to hang, the query which is having varchar2(20) on which index is there and joind with where clause...?

If that is the case I will think of some other way, such as writing stored procedure etc..