「>=」&「<=」よりBETWEENを使ったほうが効率的

ということが分かった。
こんなテーブルで20マン件つくって検証。

CREATE TABLE TEST
(
    START_NO                       NUMBER(8,0) NOT NULL,
    END_NO                         NUMBER(8,0) NOT NULL,
    NOTE                           VARCHAR2(20) NOT NULL
)
/
ALTER TABLE TEST
    ADD(PRIMARY KEY (START_NO, END_NO) USING INDEX)
/


実行計画は以下。

********************************************************************************

select *
from scott.test t
where t.start_no >=35
and t.end_no <= 35

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.03       0.25         35        756          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.03       0.28         35        758          0          35

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  (SYSTEM)

Rows     Row Source Operation
-------  ---------------------------------------------------
     35  TABLE ACCESS FULL TEST (cr=756 pr=35 pw=0 time=250644 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
     35   TABLE ACCESS (FULL) OF &#39;TEST&#39; (TABLE)

********************************************************************************
********************************************************************************

select /*+INDEX(t, sys_c0010384)*/ *
from scott.test t
where t.start_no >=35
and t.end_no <= 35

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.05          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.06       0.17         16        506          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.06       0.22         16        508          0          35

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  (SYSTEM)

Rows     Row Source Operation
-------  ---------------------------------------------------
     35  TABLE ACCESS BY INDEX ROWID TEST (cr=506 pr=16 pw=0 time=178163 us)
     35   INDEX RANGE SCAN SYS_C0010384 (cr=502 pr=16 pw=0 time=178088 us)(object id 55027)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
     35   TABLE ACCESS (BY INDEX ROWID) OF &#39;TEST&#39; (TABLE)
     35    INDEX (RANGE SCAN) OF &#39;SYS_C0010384&#39; (INDEX (UNIQUE))

********************************************************************************
********************************************************************************

select *
from scott.test
where 35 between start_no and end_no

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.23          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0         11          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.01       0.23          0         13          0          35

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  (SYSTEM)

Rows     Row Source Operation
-------  ---------------------------------------------------
     35  TABLE ACCESS BY INDEX ROWID TEST (cr=11 pr=0 pw=0 time=187 us)
     35   INDEX RANGE SCAN SYS_C0010384 (cr=7 pr=0 pw=0 time=126 us)(object id 55027)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
     35   TABLE ACCESS (BY INDEX ROWID) OF &#39;TEST&#39; (TABLE)
     35    INDEX (RANGE SCAN) OF &#39;SYS_C0010384&#39; (INDEX (UNIQUE))

********************************************************************************