开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜