|
if query contains some joins and order by clause, then execution speed slows down, Is there any solution to execute the query fast.
(comments are locked)
|
|
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.
(comments are locked)
|
|
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 Good luck!
(comments are locked)
|
|
create indexes on tables which is frequently used define datatypes properly and lru tables keep in instance
(comments are locked)
|
We will need a much more specific problem to work on.