甲骨文计划与"in"和"between"的差异

Question

你能帮我这个我不明白的计划吗？我正在运行一个非常简单的请求：

SELECT *
FROM  BOOKING.BOOKING_GRID BG
WHERE BG.hotel=128 and day between 8600 and 8602;

和另一个版本：

SELECT *
FROM  BOOKING.BOOKING_GRID BG
WHERE BG.hotel=128 and day in (8600,8601,8602);

我不明白为什么"之间"版本使用BOOKING_GRID_INDEX5而"in"版本没有？性能非常不同！是因为物联网索引无法进行"范围扫描"吗？

感谢您的帮助！

---

表索引为：

BOOKING BOOKING_GRIDPK  UNIQUE  VALID   IOT - TOP   N   NO      NO  HOTEL, DAY, BOOKINGSTATE    
BOOKING BOOKING_GRID_UNIQ   UNIQUE  VALID   NORMAL  N   NO      NO  HOTEL, DAY, BOOKING, VALIDITYSTART  
BOOKING BOOKING_GRID_INDEX5 NONUNIQUE   VALID   NORMAL  N   NO      NO  HOTEL, BOOKINGSTATUS, ISDAYUSE, DAY 
BOOKING BOOKING_GRID_INDEX7 NONUNIQUE   VALID   NORMAL  N   NO      NO  HOTEL, BOOKING, VALIDITYSTART   
BOOKING BOOKING_GRID_INDEX10    NONUNIQUE   VALID   NORMAL  N   NO      NO  HOTEL, ISDAYUSE, BOOKINGSTATUS, DAY 

这就是我得到的：

SQL> SELECT *
  2  FROM  BOOKING.BOOKING_GRID BG
  3  WHERE BG.hotel=128 and day between 8600 and 8602;
3584 ligne(s) sÚlectionnÚe(s).

Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 1060169596
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                     |  6504 |   508K|   845   (5)| 00:00:11 |
|*  1 |  INDEX UNIQUE SCAN| BOOKING_GRIDPK      |  6504 |   508K|   845   (5)| 00:00:11 |
|*  2 |   INDEX SKIP SCAN | BOOKING_GRID_INDEX5 |   464 |       |   480   (7)| 00:00:06 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("BG"."HOTEL"=128 AND "DAY">=8600 AND "DAY"<=8602)
   2 - access("BG"."HOTEL"=128 AND "DAY">=8600 AND "DAY"<=8602)
       filter("DAY">=8600 AND "DAY"<=8602)

Statistiques
----------------------------------------------------------
         58  recursive calls
          0  db block gets
       6961  consistent gets
          0  physical reads
          0  redo size
     200030  bytes sent via SQL*Net to client
       3142  bytes received via SQL*Net from client
        240  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3584  rows processed


SQL> SELECT *
  2  FROM  BOOKING.BOOKING_GRID BG
  3  WHERE BG.hotel=128 and day in(8600,8601,8602);
3584 ligne(s) sÚlectionnÚe(s).

Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 1233826949
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |  1869 |   146K|   681   (0)| 00:00:09 |
|   1 |  INLIST ITERATOR  |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| BOOKING_GRIDPK |  1869 |   146K|    36   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("BG"."HOTEL"=128 AND ("DAY"=8600 OR "DAY"=8601 OR "DAY"=8602))

Statistiques
----------------------------------------------------------
         57  recursive calls
          0  db block gets
        687  consistent gets
          0  physical reads
          0  redo size
     200192  bytes sent via SQL*Net to client
       3142  bytes received via SQL*Net from client
        240  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3584  rows processed

---

已添加： 09/09/2013

每个索引的聚类因子详细信息：

Table       Index            Cluster. factor     Table NRows
BOOKING_GRID    BOOKING_GRID_INDEX7  276 816 902     313 290 258   
BOOKING_GRID    BOOKING_GRID_UNIQ    131 055 933     313 290 258   
BOOKING_GRID    BOOKING_GRID_INDEX10     121 702 397     313 290 258   
BOOKING_GRID    BOOKING_GRIDPK       108 115 708     313 290 258   
BOOKING_GRID    BOOKING_GRID_INDEX5   24 238 404     313 290 258 

Answer 1

我会从这个开始：IN 处理一个离散列表（这里只有 3 个值），其中 BETWEEN 必须考虑连续范围。（检查返回的预期行：IN 示例为 1869，BETWEEN 为 6504。