The Oracle (tm) Users' Co-Operative FAQ

Can I find out how many rows will be affected by an SQL statement without actually running it ?

Author's name: Raj Jamadagni

Author's Email: rajendra dot jamadagni at espn dot com

Date written: 08/20/2001

Oracle version(s): Oracle 8i  and later

Here is a possible solution to this problem.

Back to index of questions

Disclaimer: I haven’t implemented this solution, but this is an idea. YMMV

 The idea is to write a stored procedure that accepts a SQL string (preferably a SQL statement or a small pl/sql block of code) and executes this code as an autonomous transaction. At the end of execution, remember the time it took and/or number of rows it affected, perform a rollback, and return the accumulated information.

This solution is not suitable, if you perform a commit in your code or execute any DDL statements (which execute an implicit commit) in your code. Also this technique is more appropriate for simple DML statements or small blocks of code. This is a labour intensive method and can only supply a ‘guesstimate’ if executed in normal db load conditions. If you run this on a development database, the times will probably be way off target in actual production environment.

For further reading, Autonomous Transactions are described in ‘Oracle Application Developers Guide’, ‘PLSQL Manual’ and ‘SQL Reference Manual’.

Back to top

Back to index of questions