Needed DB Query optomized?

Hi All,

Last few days i have encountered a database issue that needed to optimize the queries. please assist me it would be really praised to you.

I am using on the joins on around 5-6 tables to fetch the data. and each of them have huge amount of data around in 10-50 millions. i got success to optimize the query following the indexing as hint. But the next issue is that we are unable to place the order by clause in query because it leads to machine halt.

Could you suggest me any radical approach in oracle 9i or later version.

how much memory does the database use? Have you tried the following?

ALTER SYSTEM SET timed_statistics=TRUE;
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';

Then run your queries and use tkprof on the output to see what the explain plans are.

http://marist89.blogspot.com/2005/07/trace-it_04.html

Without seeing explain plans, the query and knowing your environment its just not possible to give a reasoned answer.

I can however say hash partition your tables according to the one field that you most use to access the data. But I can't say that that would be correct.