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. |
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’.
