Re: How to obtain HIT COUNT together with query results?

It depends on the environment in which you executing the SELECT.

In SQL*Plus I think you're stuck with two SELECTs. You can speed up the count SELECT by using:

SELECT count(1)
FROM xyz

or

SELECT count(rowid)
FROM xyz

instead of SELECT count(*)

If you're doing this in PL/SQL you'll have the variable %ROWCOUNT available after you empty your cursor to tell you the total number of rows returned.

To get a small sample of rows you can do this:

SELECT col_1, col_2, ...
FROM my_big_table
WHERE this = 'this' AND
that = 'that' AND
rownum < 51

The rule based optimizer allows for small result sets based on a rownum so this should be quite fast. It will quit after retrieving the number of rows you asked for. Received on Thu Apr 22 1999 - 22:40:09 CDT