Need help for query tuning

Hi All,

 

I was trying to tune a query which is using 4 tables and 1 table very huge in size (more than 3 crore of records).If I create a table similar to the big table with only 1000 records than the query is executing in seconds.

 

Example:  Tab1 is having more than 3 crore of records and I have created Tab2 similar to Tab1 with 1000 records only and I have replaced Tab1 to Tab2 in the query, then the query is executing in few seconds.

 

I have tried following steps to tune the query:

 

• Hash Partitioned the table
• Created index on the columns used in where clause and even tried with INDEX HINT.