ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 应用开发 > IT综合 > 关于绑定变量的使用

关于绑定变量的使用

原创 IT综合 作者：jixuewen 时间：2007-12-09 16:26:41 0 删除 编辑

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@]

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/564597/viewspace-990672/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 最近又在搞没有意义的技能认证了
下一篇： 最近网上盛传唐总的去向
请登录后发表评论 登录
全部评论
jixuewen
  • 博文量
    95
  • 访问量
    2007550

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593