How to add indexes to the following examples？

Question

SELECT UNIT_PRICE
FROM ORDER_DETAIL
WHERE QUANTITY IN (100, 200, 300) OR
DISCOUNT = 0.01;

My query:

EXPLAIN PLAN FOR SELECT UNIT_PRICE FROM ORDER_DETAIL WHERE QUANTITY IN (100, 200, 300) OR DISCOUNT = 0.01;`
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
CREATE INDEX OD_IDX_QD ON ORDER_DETAIL(QUANTITY, DISCOUNT);
EXPLAIN PLAN FOR SELECT UNIT_PRICE FROM ORDER_DETAIL WHERE QUANTITY IN (100, 200, 300) OR DISCOUNT = 0.01;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
DROP INDEX OD_IDX_QD;

How to add index correctly in this case？

Answer 1

Your query contains OR that's why you can't get index range scan. You can create one more index for DISCOUNT and add hint OR_EXPAND, in this case you will get UNION ALL with 2 index range scans (or-expansion transformation)

(Oracle 12.2+: https://blogs.oracle.com/optimizer/optimizer-transformations:-or-expansion )

CREATE INDEX OD_IDX_D ON ORDER_DETAIL(DISCOUNT);

EXPLAIN PLAN FOR 
SELECT/*+ or_expand */ UNIT_PRICE FROM ORDER_DETAIL WHERE QUANTITY IN (100, 200, 300) OR DISCOUNT = 0.01;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- output
Plan hash value: 4033578183
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |     4 |    52 |     5   (0)| 00:00:01 |
|   1 |  VIEW                                  | VW_ORE_1606201E |     4 |    52 |     5   (0)| 00:00:01 |
|   2 |   UNION-ALL                            |                 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_DETAIL    |     1 |    26 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                   | OD_IDX_D        |     1 |       |     1   (0)| 00:00:01 |
|   5 |    INLIST ITERATOR                     |                 |       |       |            |          |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_DETAIL    |     3 |    90 |     3   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN                  | OD_IDX_QD       |     3 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("DISCOUNT"=0.01)
   7 - access("QUANTITY"=100 OR "QUANTITY"=200 OR "QUANTITY"=300)
       filter(LNNVL("DISCOUNT"=0.01))

Or if you oracle version is pretty old, you can use hint use_concat:

CREATE INDEX OD_IDX_D ON ORDER_DETAIL(DISCOUNT);

EXPLAIN PLAN FOR 
SELECT/*+ use_concat */ UNIT_PRICE 
FROM ORDER_DETAIL OD
WHERE QUANTITY IN (100, 200, 300) OR DISCOUNT = 0.01;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 819751077
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |     4 |   120 |     5   (0)| 00:00:01 |
|   1 |  CONCATENATION                        |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_DETAIL |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                   | OD_IDX_D     |     1 |       |     1   (0)| 00:00:01 |
|   4 |   INLIST ITERATOR                     |              |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_DETAIL |     3 |    90 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | OD_IDX_QD    |     3 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DISCOUNT"=0.01)
   6 - access("QUANTITY"=100 OR "QUANTITY"=200 OR "QUANTITY"=300)
       filter(LNNVL("DISCOUNT"=0.01))

Update: comments are not convenient to answer for you additional question, so I'm answering here:

Because your query contains OR:

WHERE QUANTITY IN (100, 200, 300) OR DISCOUNT = 0.01;

If you analyze your query, you can find out that is the same as

select UNIT_PRICE 
from (
    SELECT rowid, UNIT_PRICE FROM ORDER_DETAIL WHERE QUANTITY IN (100, 200, 300) 
    union
    SELECT rowid, UNIT_PRICE FROM ORDER_DETAIL WHERE DISCOUNT = 0.01
)

where first part of union all can use your index, because it starts from that column but not second part, since you didn't provide range for the first column of the index.In this case oracle can use INDEX SKIP SCAN, but it's not effective in this case. So you need another index for that.