We are running a summary report and it took like 40 secs get the data back. We have 2 group by properties. The table is properly indexed. We run the query in Oracle directly and it still 40 secs to get the data. Our observation, it uses NESTED LOOP instead of HASH JOIN that causing the slowness to get the data.
We tried adding Optimizer Hint in the query and it get the data in just 2 secs.
Are there any options in Report Definition to use a Optimizer Hint or any configuration needed in Oracle DB to use HASH JOIN instad of NESTED LOOP?