2

I'm trying to figure out an efficient (Oracle) SQL statement that verifies whether a column contains a specific list of values at least once.

One option would be to filter for that list, output all distinct values and then count them. So, something like this:

SELECT count(*)
FROM (
      SELECT DISTINCT columnname
      FROM table
      WHERE columnname in ('a', 'b', 'c')
     ) 
;

(And then check whether count(*) returns the number 3)

The problem with this is that the DISTINCT statement looks at the whole table, which is very bad performance-wise. All three values of my list could be at the very beginning, so i don't need to look at the millions of other rows. I only want to know that the column contains 'a', 'b' and 'c'.

Does anyone has an idea to solve this efficiently?

Thanks in advance!

Bader
  • 23
  • 4

2 Answers2

2

It might be more efficient to look for each value individually:

select (case when exists (select 1 from t where col = 'a') and
                  exists (select 1 from t where col = 'b') and
                  exists (select 1 from t where col = 'c')
             then 1 else 0
        end) as has_all_three_flag
from dual;

This will be better particularly with an index on t(col).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

If you wish to get rid of distinct, then try the below, Group by has better performance than distinct, See here.

SELECT count(*)
FROM (
      SELECT columnname
      FROM table
      WHERE columnname in ('a', 'b', 'c')
      GROUP BY columnname
     ) 
;

Or you can avoid the usage of subquery by 

SELECT count(DISTINCT columnname)
      FROM table
      WHERE columnname in ('a', 'b', 'c');
Tom J Muthirenthi
  • 3,028
  • 7
  • 40
  • 60
  • This query still must read all rows from the table, while the Gordon's answer picks at most 3 rows using an index. – krokodilko Dec 08 '17 at 11:29