Optimize same inner clause in multiple select statements

Question

I develop procedure in Oracle I need write something like this:

--
open curr1 for
select *
from table1 
where key_field in (select key_field from tbl_keys where type = 1);
--
open curr2 for
select *
from table2 
where key_field in (select key_field from tbl_keys where type = 1);
--
open curr3 for
select *
from table3 
where key_field in (select key_field from tbl_keys where type = 1);
--

Is there a better way to do this? Any optimization for inner select? Thanks!

"Better" depends on what you're doing. You could combine the queries into one cursor, for example - that would improve some procedures, and make others worse. Without any context, your code looks fine to me. — kfinity, May 01 '18 at 12:55
Thanks for answer. I mean improve performance. Using cursor will improve performance? — flap13, May 01 '18 at 13:11
The answer depends on what you want to do with the result of these queries. — Rene, May 01 '18 at 13:15
Just call this procedure from DOT.NET application for show data on the form... Thanks — flap13, May 01 '18 at 13:32

score 0 · Answer 1 · answered May 01 '18 at 14:14

I think Oracle will try to cache the results of your tbl_keys subquery, so if it returns a small number of rows, your queries are probably fine the way they are.

I don't know if it's better, but if you're having performance problems an alternate method you could try is to join the tables, e.g.

open curr1 for
select t.*
from table1 t
join tbl_keys k 
  on k.key_field = t.key_field 
    and k.type = 1;

This might improve performance if your tbl_keys table is very large.

Personally, I prefer using implicit cursor loops whenever possible - they're simple and can be very fast - but I don't know if it would work for your procedure, since you didn't show the rest of it.

for r in (select t.*
    from table1 t
    join tbl_keys k 
      on k.key_field = t.key_field 
        and k.type = 1)
loop
  -- output the row somehow
end loop;

for r in (select t.*
    from table2 t
    join tbl_keys k 
      on k.key_field = t.key_field 
        and k.type = 1)
loop
  -- output the row somehow
end loop;

...etc

Optimize same inner clause in multiple select statements

1 Answers1