關於繫結變數的使用

HR 09-12月-07 >desc user_indexes;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)

HR 09-12月-07 >select INDEX_NAME,TABLE_NAME from user_indexes where TABLE_NAME=upper('bi
g_table')j;
select INDEX_NAME,TABLE_NAME from user_indexes where TABLE_NAME=upper('big_table')j
*
第 1 行出現錯誤:
ORA-00933: SQL 命令未正確結束

HR 09-12月-07 >select INDEX_NAME,TABLE_NAME from user_indexes where TABLE_NAME=upper('bi
g_table');

執行計劃
----------------------------------------------------------
ERROR:
ORA-01039: 檢視基本物件的許可權不足

SP2-0612: 生成 AUTOTRACE EXPLAIN 報告時出錯

統計資訊
----------------------------------------------------------
254 recursive calls
0 db block gets
108 consistent gets
19 physical reads
0 redo size
472 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed

HR 09-12月-07 >var a number
HR 09-12月-07 >var b number
HR 09-12月-07 >exec :a:=1;

PL/SQL 過程已成功完成。

HR 09-12月-07 >exec :b:=9999999;

PL/SQL 過程已成功完成。

HR 09-12月-07 >select * from big_table where id between :a and :b;

已選擇39023行。

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=98 Bytes=98
00)

1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (TABLE) (Co
st=5 Card=98 Bytes=9800)

3 2 INDEX (RANGE SCAN) OF 'BIG_TABLE_PK' (INDEX (UNIQUE))
(Cost=2 Card=176)

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
3168 consistent gets
0 physical reads
116 redo size
2054349 bytes sent via SQL*Net to client
29123 bytes received via SQL*Net from client
2603 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39023 rows processed

HR 09-12月-07 >set timing on
HR 09-12月-07 >select * from big_table where id between :a and :b;

已選擇39023行。

已用時間: 00: 00: 02.09

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=98 Bytes=98
00)

1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (TABLE) (Co
st=5 Card=98 Bytes=9800)

3 2 INDEX (RANGE SCAN) OF 'BIG_TABLE_PK' (INDEX (UNIQUE))
(Cost=2 Card=176)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
3167 consistent gets
0 physical reads
0 redo size
2054349 bytes sent via SQL*Net to client
29123 bytes received via SQL*Net from client
2603 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39023 rows processed

HR 09-12月-07 >select /*+ full(big_table)*/ * from big_table where id between :a and :b;

已選擇39023行。

已用時間: 00: 00: 02.03

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=132 Card=98 Bytes=
9800)

1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (TABLE) (Cost=132 Car
d=98 Bytes=9800)

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
3167 consistent gets
0 physical reads
0 redo size
2054349 bytes sent via SQL*Net to client
29123 bytes received via SQL*Net from client
2603 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39023 rows processed

HR 09-12月-07 >select * from big_table where id between :a and :b;

已選擇39023行。

已用時間: 00: 00: 02.01

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=98 Bytes=98
00)

1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (TABLE) (Co
st=5 Card=98 Bytes=9800)

3 2 INDEX (RANGE SCAN) OF 'BIG_TABLE_PK' (INDEX (UNIQUE))
(Cost=2 Card=176)

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
3167 consistent gets
0 physical reads
0 redo size
2054349 bytes sent via SQL*Net to client
29123 bytes received via SQL*Net from client
2603 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39023 rows processed

HR 09-12月-07 >exec :b:=9;

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.00
HR 09-12月-07 >print :b;

HR 09-12月-07 >print b;

HR 09-12月-07 >print :b

HR 09-12月-07 >select * from big_table where id between :a and :b;

已選擇9行。

已用時間: 00: 00: 00.01

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=98 Bytes=98
00)

1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (TABLE) (Co
st=5 Card=98 Bytes=9800)

3 2 INDEX (RANGE SCAN) OF 'BIG_TABLE_PK' (INDEX (UNIQUE))
(Cost=2 Card=176)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
594 consistent gets
0 physical reads
0 redo size
1659 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

HR 09-12月-07 >select * from big_table where id>=32001;

已選擇7026行。

已用時間: 00: 00: 00.54

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=119 Card=7024 Byte
s=702400)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (TABLE) (Cost
=119 Card=7024 Bytes=702400)

2 1 INDEX (RANGE SCAN) OF 'BIG_TABLE_PK' (INDEX (UNIQUE)) (C
ost=16 Card=7024)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7026 rows processed

HR 09-12月-07 >select * from big_table where id>=3;

已選擇39024行。

已用時間: 00: 00: 02.15

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=133 Card=39019 Byt
es=3901900)

1 0 TABLE ACCESS (FULL) OF 'BIG_TABLE' (TABLE) (Cost=133 Card=
39019 Bytes=3901900)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39024 rows processed

HR 09-12月-07 >select * from big_table where id between 1 and 99999;

已選擇39023行。

已用時間: 00: 00: 02.06

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=133 Card=39021 Byt
es=3902100)

1 0 TABLE ACCESS (FULL) OF 'BIG_TABLE' (TABLE) (Cost=133 Card=
39021 Bytes=3902100)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39023 rows processed

HR 09-12月-07 >select * from big_table where id between 1 and 9;

已選擇9行。

已用時間: 00: 00: 00.03

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=8 Bytes=800
)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (TABLE) (Cost
=3 Card=8 Bytes=800)

2 1 INDEX (RANGE SCAN) OF 'BIG_TABLE_PK' (INDEX (UNIQUE)) (C
ost=2 Card=8)

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

HR 09-12月-07 >

以上實驗證明：

原則上，如果查詢的資料量超過10%，一般會走全表掃描，但是我們使用的繫結變數，oracle無法分析是否真正應當走索引，因此在最後我們選擇的資料基本是是全部表的資料了，按照不使用繫結變數的情況，他應當走全表掃描，它還是走的索引range index scan。有的時候繫結變數其實不是fast=true的開關。

如果一個查詢使用繫結變數，除非你真正能估計到大多數使用的情況是查詢的少量資料，否則速度可能適得其反。

[@more@]