Better way of displaying ‘Count’ of records beside other columns in a select query

oracleoracle-11g-r2plsqlquery-performancestored-procedures

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) 
  is
begin

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

end;

Thanks in advance

Best Answer

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,
       c_first_name,
       c_last_name,
       count(*) over () as members_cnt
from test1;

Related Solutions

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

Your procedure is missing two OUT parameters:

PROCEDURE myprocedure (myinput      IN Varchar2, 
                       myretcursor  OUT sys_refcursor,
                       p_count      OUT NUMBER,
                       p_message    OUT VARCHAR2) IS
BEGIN
   OPEN myretcursor FOR
      SELECT * FROM MYTABLE;
   p_count := 123;
   p_message := 'message';
END myprocedure

You would also need to modify your C# code to register the new OUT parameters accordingly.

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

How to commit transaction on an after update event trigger

Related Question