「>=」&「<=」より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 'TEST' (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 'TEST' (TABLE) 35 INDEX (RANGE SCAN) OF 'SYS_C0010384' (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 'TEST' (TABLE) 35 INDEX (RANGE SCAN) OF 'SYS_C0010384' (INDEX (UNIQUE)) ********************************************************************************