title: Oracle select with "in (multiple values)" does not use index
body: <p>Oracle (12.1) does not use an index where it is really advantageous to do so.<br>
  A manuel query rewrite using <code>union all</code> helps but is really ugly.<br>
  Any ideas?</p>
  
  <p>Schema:</p>
  
  <ul>
  <li>Table "umsatz", partitioned by each value of "monat" (= year + month as number).</li>
  <li>Simple non-unique index on (monat, kundengruppe).  </li>
  <li>Statistics are available and up to date.  </li>
  <li>"monat" is not very selective (a lot of values for each month), but column "kundengruppe" is quite selective for most of its values.<br>
  "kundengruppe" has a hybrid histogram and the concrete used values here do <em>not</em> appear in the table (i.e. very selective) - changing them to seldom occuring values does not change anything. Querying for a single frequently occuring value leads to a full table/partition scan - which is okay in this case.</li>
  </ul>
  
  <p>Query (reduced to the relevant, minimal part):</p>
  
  <pre><code> SELECT SUM(u.umsatz_euro)
    FROM umsatz u
   WHERE u.monat BETWEEN 201701 AND 201712
     AND u.kundengruppe IN (123,456,987)
  </code></pre>
  
  <p>And the execution plan for it:</p>
  
  <pre><code>------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                                        | Name                       | Rows | Bytes | Cost | Time     |
  ------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                                 |                            |    1 |    14 | 3074 | 00:00:01 |
  |   1 |   SORT AGGREGATE                                 |                            |    1 |    14 |      |          |
  |   2 |    MAT_VIEW ACCESS BY GLOBAL INDEX ROWID BATCHED | UMSATZ_BUDGETBLATT         |    1 |    14 | 3074 | 00:00:01 |
  | * 3 |     INDEX RANGE SCAN                             | UMSATZ_BUDGETBLATT_GRP_NUI |    1 |       | 3073 | 00:00:01 |
  ------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ------------------------------------------
  * 3 - access("UB"."MONAT"&gt;=201701 AND "UB"."MONAT"&lt;=201712)
  * 3 - filter("UB"."KUNDENGRUPPE"=123 OR "UB"."KUNDENGRUPPE"=456 OR "UB"."KUNDENGRUPPE"=987)
  </code></pre>
  
  <p>As you can see, only "monat" is used via access predicate (i.e. as index access), "kundengruppe" is used via filter.</p>
  
  <p>.<br>
  If I change <code>WHERE</code> to a <strong>simple <code>AND u.kundengruppe = 123</code></strong> the index is used for both values and the cost is reduced a lot (3000 -> 4):</p>
  
  <pre><code>------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                                        | Name                       | Rows | Bytes | Cost | Time     |
  ------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                                 |                            |    1 |    14 |    4 | 00:00:01 |
  |   1 |   SORT AGGREGATE                                 |                            |    1 |    14 |      |          |
  |   2 |    MAT_VIEW ACCESS BY GLOBAL INDEX ROWID BATCHED | UMSATZ_BUDGETBLATT         |    1 |    14 |    4 | 00:00:01 |
  | * 3 |     INDEX RANGE SCAN                             | UMSATZ_BUDGETBLATT_GRP_NUI |    1 |       |    3 | 00:00:01 |
  ------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ------------------------------------------
  * 3 - access("UB"."MONAT"&gt;=201701 AND "UB"."KUNDENGRUPPE"=123 AND "UB"."MONAT"&lt;=201712)
  * 3 - filter("UB"."KUNDENGRUPPE"=123)
  </code></pre>
  
  <p>.<br>
  I can <strong>manually rewrite</strong> the multiple-value-IN with a sematically equivalent <strong><code>UNION ALL</code></strong>:</p>
  
  <pre><code>SELECT SUM(u.umsatz_euro)
    FROM 
    (select * from umsatz u
      where u.kundengruppe = 123
     union all
     select * from umsatz u
      where u.kundengruppe = 456
     union all
     select * from umsatz u
      where u.kundengruppe = 987
    ) u
   WHERE u.monat BETWEEN 201701 AND 201712
  </code></pre>
  
  <p>The execution plan is using the index three times with cost <em>a lot</em> below the first plan (3000 -> 14), Oracle even pushed the "monat" predicate down into each select:</p>
  
  <pre><code>--------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                                          | Name                       | Rows | Bytes | Cost | Time     |
  --------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                                   |                            |    1 |    26 |   12 | 00:00:01 |
  |   1 |   SORT AGGREGATE                                   |                            |    1 |    26 |      |          |
  |   2 |    VIEW                                            |                            |    3 |    78 |   12 | 00:00:01 |
  |   3 |     UNION-ALL                                      |                            |      |       |      |          |
  |   4 |      MAT_VIEW ACCESS BY GLOBAL INDEX ROWID BATCHED | UMSATZ_BUDGETBLATT         |    1 |    14 |    4 | 00:00:01 |
  | * 5 |       INDEX RANGE SCAN                             | UMSATZ_BUDGETBLATT_GRP_NUI |    1 |       |    3 | 00:00:01 |
  |   6 |      MAT_VIEW ACCESS BY GLOBAL INDEX ROWID BATCHED | UMSATZ_BUDGETBLATT         |    1 |    14 |    4 | 00:00:01 |
  | * 7 |       INDEX RANGE SCAN                             | UMSATZ_BUDGETBLATT_GRP_NUI |    1 |       |    3 | 00:00:01 |
  |   8 |      MAT_VIEW ACCESS BY GLOBAL INDEX ROWID BATCHED | UMSATZ_BUDGETBLATT         |    1 |    14 |    4 | 00:00:01 |
  | * 9 |       INDEX RANGE SCAN                             | UMSATZ_BUDGETBLATT_GRP_NUI |    1 |       |    3 | 00:00:01 |
  --------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ------------------------------------------
  * 5 - access("UB"."MONAT"&gt;=201701 AND "UB"."KUNDENGRUPPE"=123 AND "UB"."MONAT"&lt;=201712)
  * 5 - filter("UB"."KUNDENGRUPPE"=123)
  * 7 - access("UB"."MONAT"&gt;=201701 AND "UB"."KUNDENGRUPPE"=456 AND "UB"."MONAT"&lt;=201712)
  * 7 - filter("UB"."KUNDENGRUPPE"=456)
  * 9 - access("UB"."MONAT"&gt;=201701 AND "UB"."KUNDENGRUPPE"=987 AND "UB"."MONAT"&lt;=201712)
  * 9 - filter("UB"."KUNDENGRUPPE"=987)
  </code></pre>
  
  <p>.<br>
  This is really cumbersome, especially if you have a List in Java and comfortably bind it via Hibernate to a single bind variable (<code>IN (:kgrpList)</code>).</p>
  
  <p>The query hint <code>use_concat</code> does not help.<br>
  Any ideas to improve this situation?</p>
  
  <hr>
  
  <p><strong>Another strange thing</strong> in this situation:</p>
  
  <p>If I use a bind variable for the number</p>
  
  <pre><code> SELECT SUM(u.umsatz_euro)
    FROM umsatz u
   WHERE u.monat BETWEEN 201701 AND 201712
     AND u.kundengruppe = to_number(:a)    -- also for fixed  to_number('a123')
  </code></pre>
  
  <p>the plan uses a (slow) index skip scan:</p>
  
  <pre><code>------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                                        | Name                       | Rows | Bytes | Cost | Time     |
  ------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                                 |                            |    1 |    14 |  955 | 00:00:01 |
  |   1 |   SORT AGGREGATE                                 |                            |    1 |    14 |      |          |
  |   2 |    MAT_VIEW ACCESS BY GLOBAL INDEX ROWID BATCHED | UMSATZ_BUDGETBLATT         | 1769 | 24766 |  955 | 00:00:01 |
  | * 3 |     INDEX SKIP SCAN                              | UMSATZ_BUDGETBLATT_GRP_NUI | 1769 |       |   36 | 00:00:01 |
  ------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ------------------------------------------
  * 3 - access("UB"."MONAT"&gt;=201701 AND "UB"."KUNDENGRUPPE"=TO_NUMBER(:A) AND "UB"."MONAT"&lt;=201712)
  * 3 - filter("UB"."KUNDENGRUPPE"=TO_NUMBER(:A))
  </code></pre>
  
  <p>Why the hell is Oracle doing this??</p>