I have a table with below structure :

Test1(c_num  number ,c_first_name varchar2(50), c_last_name varchar2(50))

1)There is a normal index on c_num column.

2)The table has nearly 5 million records.

I have a procedure as you can see below. I want to display the Count(*) along with other columns.I want to know if there are better ways of doing this so that we can have better performance.

create or replace procedure get_members(o_result out sys_refcursor) 

open o_result for
  select c_num,
         (select count(*) from test1) members_cnt -->Is there a better way instead of doing this?
  from test1;


Thanks in advance


What you used there should not cause a problem, because it is a scalar subquery, meaning its result would be automatically cached, and as it has only 1 distinct value, even if test1 has 5 million rows, the inner query would not be executed 5 million times.

But you can use the analytic version of count:

select c_num,
       count(*) over () as members_cnt
from test1;
  • Thanks. So your point is that "count() over()" is better than "(select count(*) from...)" in terms of performance ? – Pantea Aug 3 '20 at 9:58
  • 1
    @Pani No, and these are not generally exchangable. But in this case, using the subquery requires 1 more table access than the analytic function. The performance difference is rather negligible. – Balazs Papp Aug 3 '20 at 10:31
  • 1
    In the case as you have presented it, what's the point? You will get 5 million row, and each will show exactly the same value MEMBERS_COUNT. The count will have no relationship at all to the member shown on the individual row. I strongly suspect your actual case is more complex, in which case you should present it if you want the best possible advice. – EdStevens Aug 3 '20 at 12:48
  • @EdStevens This is a procedure which is being used by the Application in order to show the result. The application shows all the column of the table and also the count of the result.So I need to give the Application , The count of records in this procedure so it can use this . – Pantea Aug 3 '20 at 13:33
  • 1
    @Pani: Sorry, but this looks to me like another Technical Solution in search of a Problem. It is highly /unlikely/ that the application will want all 5 million records - how long would that even take to display? Also, the Application should be perfectly capable of counting what it retrieves for itself. – Phill W. Aug 3 '20 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.