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

You could do something like

SQL> desc test1

 Name                                      Null?        Type
 ----------------------------------------- --------
---------------------------
 N1                                        NOT NULL NUMBER
 V1                                                             VARCHAR2(100)
 D1                                                             DATE

SQL> select a.n1, a.v1, a.d1,count(b.n1) no_rows   2 from test1 a, test1 b
  3 group by a.n1, a.v1, a.d1
  4 /

       N1
V1
D1 NO_ROWS

        1
1

22-APR-1999         5
        2
2
22-APR-1999         5
        3
3
22-APR-1999         5
       10
10
22-APR-1999         5
       11
11
22-APR-1999         5

5 rows selected.

Or if your Oracle version allows it use In-Line views:

SQL> select a.n1, a.v1, a.d1, b.no_rows   2 from test1 a, (select count(*) no_rows from test1) b   3 /

You must address performing problems through using the right optimizer (eg Cost Based and ANALYZE your objects regularly) and introduce the usage of HINTS in you SELECT statements.

Good luck!

Lars Bo

Bogdan Stepien wrote:

> I need to obtain a number of rows affected by the query and the query
> results in one step. Right now I am executing two queries:
> - select * from mytable where ... order ...
> - select count(*) from mytable where ...
> But the query is very complicated and time consuming, so it lasts twice
> slower. The other problem is how to get, for example, 50-60 results of
> 10000 matching the first query in a efficient way. Any ideas or
> suggestions?
>
> Regards,
> Bogdan Stepien
> mailto:stepien_at_tpg.pl
>
> PS. The queries are executed via JDBC, I used to scroll the result set to
> the end to find out how many rows returned the executed query. This turned
> out to be very fast on small result sets (up to few hunderds of rows), but
> recently the database has been enlarged to over 1GB...
Received on Thu Apr 22 1999 - 05:44:09 CDT