*Please Check The Table size is more than > 1g than Used list portion or
range portion and Sp-red the Data Multiple Table space , Add Db Writer
Process More Than 1 also check the PGA and Temporary Tablespace Size For
Particular User, Check Index Are Using Or Not , By Using object_usage,Index
monitoring ect*.

You are going to be generating a Cartesian product on the records in the two tables, so the query will take a long time given you have so many records in each table. What are you trying to achieve? Do you need an extra join between the two tables?

The reason why index is not being used may be because you may be using some function on those id column in where clause or your select statement contains an aggregate function. There can be other reasons also so I request you to forward complete query to me.

If everything is correct than I would suggest as a lost resort take your query in a function that returns ref cursor and call this function in from clause using table(). This function should you Bulk Collect feature of oracle.

If you need further clearance about it through an example or else send me the query and i will send a detailed answer containing an example of how to do it.

Nasreen you are absolutely right but just wanted to add one more thing on it.

Well if the size of your table is > 2GB then oracle recommends you to use partition table.

Therefore I suggest that by using DBMS_METADATA.GET_DDL('TABLE','table_name','user_s c hema_name') function get the table creation script. See the script for wether your tables have been created as PARTITION TABLE or not.

If they are not partition table make them partition table because by converting them to partition table you will be able to assign each partition of the table a different tablespace meaining a different datafile meaning a different phsical storage drive thus reducing the I/O dramatically other than this each partiotion will contain the subset of the table data so your data look up time will also be reduced. In short it is a sure way of optimizing performance.

Thanks for your info.
There are no functions used in the where clause condition.
There are functions used in select statement columns, but those are only
UPPER function or CASE WHEN statements.
All conditions are equi joins with tables.
Since the table has less than million records, i don't think this needs to
be resolved only with partitions.