Fetch query count?

[Patrick Holmes]

This is an enhancement request (unless you already have this feature but I couldn't find it):
Let's say I run a query in a SQL window like "select count(*) from customers" and customers has 1,000 rows. The SQL window currently will show the first page of records, for example let's say 15 rows. The user has the option of fetching next-page or last-page (i.e. all rows). What would be nice is if we could also have a button or a right-mouse option on the results window so that we could "fetch query count" or something like that. In other words, I'd like to be able to press a button or choose a right-mouse-click option that tells me that *if I were to fetch all pages* that the query count would be 1,000 rows.
thanks,
pat

 

[Patrick Holmes]

In other words, I want to know how many rows my query would return, without actually having to fetch and display all rows.

 

[Hilarion]

To get that information from Oracle, you'd have to fetch all rows. That either means that PL/SQL Developer would have to load all those pages into memory anyway or into oblivion.

 

[T-Gergely]

To get that information from Oracle, you'd have to fetch all rows.

Or
a) you could use scrollable cursors (not recommended);
b) embed the query in a "select count(*) from (query)" statement.

 

[Hilarion]

The first one would make Oracle store all those "pages" in memory (and PL/SQL Developer would have to use those all the time in case user want's to do what is stated here or execute second statement - in that case read what's below).
The second one would require executing additional statement (in separate session), that:
- would require Oracle to "retrieve" (internally) all that rows (to count them), including all those you already got,
- could give different results than the original (if data changed in between or if data was available in the original session only - eg. uncommited).

 

[T-Gergely]

The second is basically what COUNT_QUERY does in Oracle Forms.
Yes, the results may differ when the db changes, so would the result of the original query. The client does NOT have to use a different session, though: you can have multiple cursors open in a session.
The RDBMS may not retrieve all the rows (eg. index fast full scan instead of table full scan).

 

[Hilarion]

You are right (about no need for separate session and Oracle not necessarily fetch all the data and do all the calculations the original query required, to count the rows - that depends on the query).

There's still potentially one problem. I'm not sure if what Patrick wanted, meant row count for exactly the query execution he initiated (and got initial rows fetched) or only row count for the same query (accepting the re-execution of that query, with potentially different results - row count).

 

[Patrick Holmes]

Yes, what I was looking for is similar to COUNT_QUERY in Oracle Forms. The reason I asked is I have a new employee who's used TOAD for years. This is something that TOAD does apparently that PL/SQL Developer does not. So if the user right-clicks (over the query? or whichever query was run last?), then there is an option to get the count of the rows if the query were to run to completion. He thinks what TOAD is doing is taking the last query in memory, behind-the-scenes replacing all select columns with a "COUNT(*)" and re-running the query so it is now a "SELECT COUNT(*) FROM ..." where the from, where and other clauses are the same, then display the resulting count() somewhere. We can do this manually of course by copying our query and modifying the SELECT clause, but it would be a nice little feature if we could just right-click and choose "Count" instead.

 

[uros.r.d]

I wote for this 'new' feature!
I miss this too.
And as on table object 'Count' wold be handy too.