how to make oracle for loop fast

Question

Query below takes 20 seconds to run. user_table has 40054 records. other_table has 14000 records

select count(a.user_id) from user_table a, other_table b 
where a.user_id = b.user_id;

our restriction is that any query running more than 8 seconds gets killed...>_< I've ran explain plans, asked questions here but based on our restrictions I can not get this query to run in less than 8 secs. So I made a loop out of it.

begin
FOR i IN role_user_rec.FIRST .. role_user_rec.LAST LOOP
            SELECT COUNT (a.user_id) INTO v_into FROM user_table a
            WHERE TRIM(role_user_rec(i).user_id) = TRIM(a.user_id);
          v_count := v_count + v_into;
END LOOP;

I know restrictions suck and this is not effecient way to do things but is there any other way to make this loop run faster?

Answer 1

Can you get around the loop? I agree with Janek, if the query itself takes too long you may have to do a different method to get it. And to agree with Mark, if you can do it in one query then by all means do so. But if you cannot, drop the loop as below

But try it something like this; drop the loop:

/*
--set up for demo/test
Create Type Testusertype As Object(User_Id Number , User_Name Varchar2(500));
CREATE TYPE TESTUSERTYPETABLE IS TABLE OF TESTUSERTYPE;
*/

Declare
  Tutt Testusertypetable;
  TOTALCOUNT NUMBER ;
Begin 

    Select Testusertype(Object_Id,Object_Name)
       bulk collect into TUTT
      From User_Objects
    ;

Dbms_Output.Put_Line(Tutt.Count);

Select Count(*) Into Totalcount 
  From User_Objects Uu 
       Inner Join Table(Tutt) T
       ON T.User_Id = Uu.Object_Id;

Dbms_Output.Put_Line(Tutt.Count);
Dbms_Output.Put_Line(Totalcount);

End ;