Oracle 11g, how to speed up an 'in' query

Question

I was trying to create a view and needed to create a column that shows if the 'somenumber' column exists on other table or not. The code below worked but very slowly. Is it possible to declare a table as (select someNumber from veryYugeTable) and check on that one instead of sending this query for every single record or using some other way to speed up the view?

case 
         when someOtherTable.someNumber in(select someNumber from veryYugeTable) then 'exists' 
         else 'doesn't exist' 
end as "someColumn"

use left join veryYugeTable on someNumber = someOtherTable.someNumber then change the casewhen clause to veryYugeTable.Column is null then 'doesn't exist' else 'exits' — Dr. Stitch, May 18 '16 at 07:41
Please post the view query and the explain plan of it. Without seeing the problem is not easy to provide a solution. see [here](http://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=active#tab-top) some hints. — Marmite Bomber, May 18 '16 at 08:38

score 1 · Answer 1 · answered May 18 '16 at 08:04

The query looks fine. You should have an index on veryYugeTable.someNumber.

Sometimes the optimizer handles correlated subqueries better than non-correlated ones, so you can try:

case
  when exists 
  (
    select * 
    from veryYugeTable
    where veryYugeTable.someNumber = someOtherTable.someNumber
  ) then 'exists' 
  else 'doesn''t exist' 
end as "someColumn"

(Well, as this query does exactly the same as yours, the optimizer should get to the same execution plan, but this is not always the case.)

But as mentioned: Make sure first to have that index.

Thanks, index seems a fine idea. – whaletail May 18 '16 at 10:48 — whaletail, May 18 '16 at 10:48

score 0 · Answer 2 · answered May 18 '16 at 07:43

0

use left join instead of putting the in clause in select:

left join veryYugeTable on someNumber = someOtherTable.someNumber

adjust your case when statement as follow:

case 
         when veryYugeTable.OtherColumn is null then 'doesn''t exist' 
         else 'exist' 
end as "someColumn"

answered May 18 '16 at 07:43

Dr. Stitch

908
6
15

This will fail (= produce dups) if the `someNumber` is not a PK (unique) in the table `veryYugeTable` – Marmite Bomber May 18 '16 at 08:33
Sadly someNumber isn't PK at veryYugeTable. – whaletail May 18 '16 at 10:47

score 0 · Accepted Answer · answered May 18 '16 at 08:37

You might find you get some benefit with scalar subquery caching if you do something like:

coalesce((select 'exists'
          from   veryyugetable vyt
          where  vyt.somenumber = someOtherTable.someNumber
          and    rownum = 1),
         'doesn''t exist') somecolumn

N.B. the and rownum = 1 is not necessary if vyt.somenumber is a unique column. Also, I nth the suggest to index the vyt.somenumber column.

Oracle 11g, how to speed up an 'in' query

3 Answers3