Hello All,
Can you please guide me to the equivalent to this query in Oracle:
SELECT SQL_CALC_FOUND_ROWS * FROM tableName
SELECT FOUND_ROWS() as cnt
Thanks
views:
70answers:
2
+1
A:
The query retrieves all rows from tableName, and then retrieves the number of rows in tableName. Doing that with SQL_CALC_FOUND_ROWS is just a performance optimization: it saves you from doing two queries.
If performance is not an issue, an equivalent for Oracle would be:
SELECT * FROM tableName
SELECT count(*) from tableName
If you are in a position to rewrite the client, you could do both in one query:
SELECT *
, (SELECT count(*) from tableName) as totalRows
FROM tableName
Andomar
2010-08-02 16:38:11
The following SQL does the second option in a single pass: `select a.*, count(*) over () as total_rows from [table_name] a`;
Adam Musch
2010-08-02 17:13:03
@Adam Musch: Doing it in a single pass isn't all it's cracked up to be. In the OP's version, a full scan of the table and a fast full scan on the index (assuming there is one) is required. For your version, the window function has to be applied to the result of the full table scan. Under limited testing against a table with over 9 million rows, the OP's version is significantly faster (particularly with the first rows hint).
Allan
2010-08-03 15:02:17
@Allan -- Ones mileage may always vary, but it's best to do it in a single pass unless one has a compelling reason (limited sort memory, for example) not to.
Adam Musch
2010-08-03 17:58:47
+1
A:
For Oracle 9i+, use:
SELECT COUNT(*) over () found_rows,
t.*
FROM TABLE t
Be aware that it is faster to run separate queries than to use SQL_CALC_ROUND_ROWS in MySQL.
OMG Ponies
2010-08-02 18:06:32
Updated to mention that `OVER` is an analytic function, which isn't supported on Oracle until 9i.
OMG Ponies
2010-08-02 20:27:54