PLSQL Function used in where clause - Performance Issue
I have a query which performs quite ok for a limited number of ROWNUMS but once it exceeds this particular ROWNUM it performs very miserable.
Below is the query:
SELECT p.col1,pv.col1
FROM tab1 p, tab2 tv, tab3 pv
WHERE myFunc(pv.col1) = 1
AND tv.col1 = pv.col1
AND p.col1 = tv.col2
AND ROWNUM <开发者_JAVA百科 4500
All these tables have huge (more than a million) records.
The above query executes at a very decent speed.
If ROWNUM < 5000 or more then it takes quite a lot of time.
Is there any way I can improve the performance of the above query?
Check that there is indexes on each join columns on each table, and after that, you can use functional indexes, see for example : http://techonthenet.com/oracle/indexes.php
精彩评论