在Oracle执行select count(*) from xxfl where jllx='0' and scbj=0 and (jgsj between to_date('2008-08-27 00:00:09','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-27 01:00:09','yyyy-mm-dd hh24:mi:ss')) and sbbh=6053;
执行结果: COUNT(*)
----------
45
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=663 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'XXFL' (Cost=663
Card=1 Bytes=17) 3 2 INDEX (RANGE SCAN) OF 'JGSJ_IDX' (NON-UNIQUE) (Cost=7
Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1445 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
执行select count(*) from xxfl where jllx='0' and scbj=0 and (jgsj between to_date('2008-08-27 00:00:09','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-27 10:00:09','yyyy-mm-dd hh24:mi:ss')) and sbbh=6053;
结果为:
COUNT(*)
----------
940
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2593 Card=1 Bytes=17
) 1 0 SORT (AGGREGATE)
2 1 PARTITION HASH (ALL)
3 2 TABLE ACCESS (FULL) OF 'XXFL' (Cost=2593 Card=1 Bytes=
17)
Statistics
----------------------------------------------------------
0 recursive calls
61 db block gets
19980 consistent gets
17496 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed请问红色参数分别代表什么意思?有位朋友这样讲:符号条件的字段通常超过5%-10%,oracle会选择全表扫描。
现在数据表里有67万记录,940/670000<5%怎么还是全表扫描!?
即使select count(*) from xxfl where (jgsj between to_date('2008-08-27 00:00:09','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-27 10:00:09','yyyy-mm-dd hh24:mi:ss'))除以670000也是小于5%的
执行结果: COUNT(*)
----------
45
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=663 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'XXFL' (Cost=663
Card=1 Bytes=17) 3 2 INDEX (RANGE SCAN) OF 'JGSJ_IDX' (NON-UNIQUE) (Cost=7
Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1445 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
执行select count(*) from xxfl where jllx='0' and scbj=0 and (jgsj between to_date('2008-08-27 00:00:09','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-27 10:00:09','yyyy-mm-dd hh24:mi:ss')) and sbbh=6053;
结果为:
COUNT(*)
----------
940
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2593 Card=1 Bytes=17
) 1 0 SORT (AGGREGATE)
2 1 PARTITION HASH (ALL)
3 2 TABLE ACCESS (FULL) OF 'XXFL' (Cost=2593 Card=1 Bytes=
17)
Statistics
----------------------------------------------------------
0 recursive calls
61 db block gets
19980 consistent gets
17496 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed请问红色参数分别代表什么意思?有位朋友这样讲:符号条件的字段通常超过5%-10%,oracle会选择全表扫描。
现在数据表里有67万记录,940/670000<5%怎么还是全表扫描!?
即使select count(*) from xxfl where (jgsj between to_date('2008-08-27 00:00:09','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-27 10:00:09','yyyy-mm-dd hh24:mi:ss'))除以670000也是小于5%的
解决方案 »
- oracle分页查询问题
- 简单的查询问题
- 奇怪,奇怪!!同一个数据库,不同oracle_sid 中文显示不一样?
- windowsxp 专业版上能不能安装orcale 8.0版呢
- 专业的oracle人才请进来看看
- 我是ORACLE初学,在表下面的HR,OE,OEM,CTXSYS等,这些下面的表是来干什么??
- 一个统计查询的问题
- 向懂ORACLE的求助!!
- ■■谁能回答谁就是oracle高手!!在oracle 网站上下载的工具都可以正常使用吗?
- Oracle中怎样用存储过程将一段字符串以逗号为界截取,并将得到的子串存到另一张表中
- 一个表复制的问题.
- 大批量导入数据问题(ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'')
是成本,不是时间,同样的SQL,一般来说cost低好还要看数据的分布,表的分析情况,都会影响CBO是否选择全表扫描
不过一般说来CBO的选择基本上都是最优的(基于经常对表做分析)
2)940是最后的结果,不是仅仅用你的索引能得到的,我倒是很有兴趣想知道下面语句的结果和执行计划:
select count(*) from xxfl where (jgsj between to_date('2008-08-27 00:00:09','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-27 10:00:09','yyyy-mm-dd hh24:mi:ss'));
3)简单的说,Cost 是预计的成本,Card 是预计返回的记录数,Bytes 是预计返回的字节数。
4)Oracle选择采用何种执行路径是由预计的成本来决定的,Oracle会自动选择预计成本最小的一种路径。对表经常做分析是保证Oracle计算成本准确的前提,建议对表做分析后再试试。
5)如果你有兴趣,可以用加hint的方式强制使用索引,看看成本是多少。
6)从你的语句看,你可以定义出更好的,更符合你的程序和语句的索引,而不是仅仅jgsj一个字段。