Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Tuesday, April 29, 2008

Like '%' and ROWNUM

I've found many of developers using like with '%' and allowing application user to fetch all rows in table. For example consider query like this:

select * from ms_account where customer_name like '%'||:p_1||'%'

When the users submit the query using a,b or % only, the database will query all the records and could cause very high consumption server resources (Memory, I/O, CPU)

Using rownum could help.

select * from ms_account where customer_name like '%'||:P_1||'%' and rownum <= 10. This will fetch the query with maximum 10 records.

Learn more about rownum : http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

You should limit your query (espesially the one with LIKE)

No comments: