Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - ProductionSQL> set autotrace trace explain
SQL> select count(*) from sms_his_200310
2 where pay_addr = '13051290000'
3 /Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'SMS_HIS_200310_PAY_ADDR_IDX'
(NON-UNIQUE) (Cost=4 Card=24127 Bytes=265397)
SQL> select * from sms_his_200310
2 where pay_addr = '13051290000'
3 /Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7782 Card=24127 Byte
s=5139051) 1 0 TABLE ACCESS (FULL) OF 'SMS_HIS_200310' (Cost=7782 Card=24
127 Bytes=5139051)
SQL> 我不明白为什么第二次不用索引了!!!!
SP2-0734: 未知的命令开头 "我不明白为..." - 忽略了剩余的行。
SQL>
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - ProductionSQL> set autotrace trace explain
SQL> select count(*) from sms_his_200310
2 where pay_addr = '13051290000'
3 /Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'SMS_HIS_200310_PAY_ADDR_IDX'
(NON-UNIQUE) (Cost=4 Card=24127 Bytes=265397)
SQL> select * from sms_his_200310
2 where pay_addr = '13051290000'
3 /Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7782 Card=24127 Byte
s=5139051) 1 0 TABLE ACCESS (FULL) OF 'SMS_HIS_200310' (Cost=7782 Card=24
127 Bytes=5139051)
SQL> 我不明白为什么第二次不用索引了!!!!
SP2-0734: 未知的命令开头 "我不明白为..." - 忽略了剩余的行。
SQL>
解决方案 »
- oracle的clob字段转换成字符时的缓冲区溢出问题
- 在串行化事务中对于一个cluster的表插入一条数据再立即select,经常第一次出错,第二次正常,为何?
- 请教goto errors用法
- 连接
- 如何增加缺少的序号?
- SQL Plus中输入的主机字符串应该输入什么啊,在线等待
- 问几个简单的入门问题
- 下载Oracle9i Application Server Enterprise Edition for Win2000后的问题
- 如何让同一个表中两个列的部分值相等?
- *****求一SQL语句******
- 痛苦的选择: 我有一个大型中文数据库, 现在需要增加英文版, 因此数据库面临分家的问题? 黑头想成了白头?
- 有关oracle对order by的查询然后的分页
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - ProductionSQL> set autotrace trace explain
SQL> select count(*) from sms_his_200309 where pay_addr='13051290000'
2 /Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'SMS_HIS_200309_PAY_ADDR_IDX' (NON
-UNIQUE) (Cost=1 Card=11 Bytes=121)
SQL> select * from sms_his_200309 where pay_addr='13051290000'
2 /Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=11 Bytes=2398
) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SMS_HIS_200309' (Cost=2
Card=11 Bytes=2398) 2 1 INDEX (RANGE SCAN) OF 'SMS_HIS_200309_PAY_ADDR_IDX' (NON
-UNIQUE) (Cost=1 Card=11)
SQL> 这是在别一台服务器上的结果有什么不一样吗?为什么这台服务器就可以用索引
SP2-0734: 未知的命令开头 "这是在别一..." - 忽略了剩余的行。
SQL>
from sms_his_200310 where pay_addr = '13051290000' select /*+ FIRST_ROWS INDEX_RS(sms_his_200309) */
from sms_his_200310 where pay_addr = '13051290000'
看一下查询计划,如果cost比全表扫描高,则说明CBO
的选择是对的. BTW: 两台机器上执行相同的查询,得出的查询计划不同是很正常的.
表和索引是否统计过,初始化参数文件中的相关参数是否相同 都会
影响查询计划.
select /*+ ALL_ROWS INDEX_RS(sms_his_200309) */ *
from sms_his_200310 where pay_addr = '13051290000' select /*+ FIRST_ROWS INDEX_RS(sms_his_200309) */ *
from sms_his_200310 where pay_addr = '13051290000'