if query contains some joins and order by clause, then execution speed slows down, Is there any solution to execute the query fast.
asked Oct 07, 2010 at 03:01 AM in Default
Performance tuning is a vast subject, and this can't possibly be answered simply.
In general, please refer to the Oracle Performance Tuning Guide: http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/toc.htm
If you provide more details, then I (or others) might be able to help in the specific case.
answered Oct 19, 2010 at 10:02 AM
Andrew's suggestion is the correct one, though probably totally unsatisfying for those looking for a quick answer.
Here's my attempt at some possible avenues to investigate to get you thinking about what might be going wrong.
1) First check for Cartesian Products in your joins, that is joined tables you haven't identified reasonable values to join over, resulting in getting ALL rows from both tables. This is the most-common culprit for slow queries.
e.g. SELECT * from emp, dept; -- not including WHERE emp.dept_id = dept.dept_id
2) The next place to look would be to see if you can join over fields that are indexed in one or more of the tables.
3) Or if your WHERE clause is trying to limit values based on the result of a function (especially a non-built-in SQL function).
e.g. SELECT * from emp where my_plsql_func(emp.dept_id) > 500
answered Nov 03, 2010 at 04:36 PM
create indexes on tables which is frequently used define datatypes properly and lru tables keep in instance
answered Nov 22, 2010 at 07:12 AM