select
b.no,
b.NAME,b.chi_name,
b.tel_no,b.tel_no2,s.sts,b.sex
from basic_info b,status s where b.no = s.no and s.sts = 'I'1.上面这条sql,两个表得数据均在100万左右,分析发现status表的索引(no,sts)已经用上,而basic_info(主键是--no)的索引没有起作用.2.什么原因使这个表basic_info 得索引没有起到作用,如何才能让索引有用!!---多谢大家!!!
解决方案 »
- eclipse6 连oracle10g时出错。
- PowerDesigner怎么导入Oracle
- 请帮我看看我的存储过程哪里写错了吧
- 人数统计脚本出错。请wildwave 或者其他高手帮忙看看
- 各位高手,关于字符集问题
- ORA_02266Unique/Primary key in table1 referenced by enable foreign keys
- 导数据
- 我想建立一个触发器,可是编译时总报错!
- 问?
- oracle 9.2.0 在Redhat 7.3 下安装出错
- oracle 時間小問題~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- 大家好,对于 如下ORACLE语句,oracle 为什么删除的是client_capital表的数据?
basic_info(主键是--no)的索引没有起作用这是当然了,basic_info为全表扫描如果,要使basic_info(主键是--no)的索引起作用,status表的索引(no,sts)不可能用上
这时,status是全表扫描
sql总得定一个主表吧
LZ的情况,也没说表中满足查询SQL结果的数据量有多大。他需要搜集下信息,再贴出执行计划看看,才能看出是否用索引的效率不及不用。
1 Plan hash value: 2459259292
2
3 ----------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 853K| 70M| | 12870 (2)| 00:02:35 |
7 |* 1 | HASH JOIN | | 853K| 70M| 21M| 12870 (2)| 00:02:35 |
8 |* 2 | INDEX FAST FULL SCAN| INDEX_AWB_POLICY_STATUS3 | 853K| 11M| | 674 (4)| 00:00:09 |
9 | 3 | TABLE ACCESS FULL | BASIC_info | 854K| 58M| | 7711 (2)| 00:01:33 |
10 ----------------------------------------------------------------------------------------------------------
11
12 Predicate Information (identified by operation id):
13 ---------------------------------------------------
14
15 1 - access("B"."NO"="S"."NO")
16 2 - filter("S"."STS"='I')
create index idx_basic_info_no on basic_info(no);select/*+index(b,idx_basic_info_no)*/
b.no,
b.NAME,b.chi_name,
b.tel_no,b.tel_no2,s.sts,b.sex
from basic_info b,status s where b.no = s.no and s.sts = 'I';
再看看执行计划和sql运行时间
INDEX_AWB_POLICY_STATUS3 854K这2个表join之后的结有853K.
如果统计信息正确,找全表扫描是正确的。select count(1) from BASIC_info
select count(1) from status
select count(1) from status s where s.sts = 'I' 这3个数分别是多少?
LZ先看看怎么收集统计信息:http://topic.csdn.net/u/20080908/17/6fca3591-3daf-4cce-9939-7197163e3958.html
再运行你的查询SQL看看执行计划,如果还用不上索引,说明你查询返回的数据量很大,CBO认为不用索引还快些。这时你可以按13L说的用HINT试试,加了索引是不是还慢些。
而索引未必用了会快
我单位里也遇到过,用hint强制用索引,一句语句50S,去掉hint,1S
像如果你要取的数据占表中数据很大的话,这时候还是全表扫描快
同意,并不是用了索引列速度肯定快。
大数据量情况下,如果查询结果数据集<=10%总记录集,索引的作用很明显,如果超过30%,用了索引反而慢,而且,会慢的很多。
(ps:10%与30% 是两个经验值,没有理论依据)
接下来是SQL语句,然后再是数据库参数的调整(这个前提是参数设置正常的前提下)如果数据量大,可以考虑用分区表,这样会减少全表扫描的量
如果status表sts上建索引也很慢的话,
只能说明select * from status s where s.sts = 'I' 数据量太大
那么就不可能快了
create index idx_basic_info_no on basic_info(no);select/*+index(b,idx_basic_info_no)*/
b.no,
b.NAME,b.chi_name,
b.tel_no,b.tel_no2,s.sts,b.sex
from basic_info b,status s where b.no = s.no and s.sts = 'I';
我抄袭的
=========================
Oracle Index 三讲
2004-11-09 作者:BitsCN整理 来源:中国网管联盟 点评 投稿 收藏
索引( Index )是常见的数据库对象,它的设置好坏、使用是否得当,极大地影响数据库应用程序和Database 的性能。虽然有许多资料讲索引的用法, DBA 和 Developer 们也经常与它打交道,但笔者发现,还是有不少的人对它存在误解,因此针对使用中的常见问题,讲三个问题。此文所有示例所用的数据库是 Oracle 8.1.7 OPS on HP N series ,示例全部是真实数据,读者不需要注意具体的数据大小,而应注意在使用不同的方法后,数据的比较。本文所讲基本都是陈词滥调,但是笔者试图通过实际的例子,来真正让您明白事情的关键。
一讲,索引并非总是最佳选择
如果发现Oracle 在有索引的情况下,没有使用索引,这并不是Oracle 的优化器出错。在有些情况下,Oracle 确实会选择全表扫描(Full Table Scan),而非索引扫描(Index Scan)。这些情况通常有:
1, 表未做statistics, 或者 statistics 陈旧,导致 Oracle 判断失误。
网管论坛bbs_bitsCN_com
2, 根据该表拥有的记录数和数据块数,实际上全表扫描要比索引扫描更快。
对第1种情况,最常见的例子,是以下这句sql 语句:
select count(*) from mytable;
在未作statistics 之前,它使用全表扫描,需要读取6000多个数据块(一个数据块是8k), 做了statistics 之后,使用的是 INDEX (FAST FULL SCAN) ,只需要读取450个数据块。但是,statistics 做得不好,也会导致Oracle 不使用索引。
第2种情况就要复杂得多。一般概念上都认为索引比表快,比较难以理解什么情况下全表扫描要比索引扫描快。为了讲清楚这个问题,这里先介绍一下Oracle 在评估使用索引的代价(cost)时两个重要的数据:CF(Clustering factor) 和 FF(Filtering factor).
CF: 所谓 CF, 通俗地讲,就是每读入一个索引块,要对应读入多少个数据块。
FF: 所谓 FF, 就是该sql 语句所选择的结果集,占总的数据量的百分比。
大约的计算公式是:FF * (CF + 索引块个数) ,由此估计出,一个查询, 如果使用某个索引,会需要读入的数据块块数。需要读入的数据块越多,则 cost 越大,Oracle 也就越可能不选择使用 index. (全表扫描需要读入的数据块数等于该表的实际数据块数)
网管u家u.bitscn@com 其核心就是, CF 可能会比实际的数据块数量大。CF 受到索引中数据的排列方式影响,通常在索引刚建立时,索引中的记录与表中的记录有良好的对应关系,CF 都很小;在表经过大量的插入、修改后,这种对应关系越来越乱,CF 也越来越大。此时需要 DBA 重新建立或者组织该索引。
如果某个sql 语句以前一直使用某索引,较长时间后不再使用,一种可能就是 CF 已经变得太大,需要重新整理该索引了。
FF 则是Oracle 根据 statistics 所做的估计。比如, mytables 表有32万行,其主键myid的最小值是1,最大值是409654,考虑以下sql 语句:
Select * from mytables where myid>=1; 和
Select * from mytables where myid>=400000
这两句看似差不多的 sql 语句,对Oracle 而言,却有巨大的差别。因为前者的 FF 是100%, 而后者的 FF 可能只有 1%。如果它的CF 大于实际的数据块数,则Oracle 可能会选择完全不同的优化方式。而实际上,在我们的数据库上的测试验证了我们的预测. 以下是在HP 上执行时它们的 explain plan:
网管网www.bitscn.com 第一句:
SQL> select * from mytables where myid>=1;
已选择325917行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3132 Card=318474 Byt es=141402456)
1 0 TABLE ACCESS (FULL) OF 'MYTABLES' (Cost=3132 Card=318474 Byt es=141402456)
Statistics
----------------------------------------------------------
7 recursive calls
89 db block gets
41473 consistent gets
19828 physical reads
0 redo size
131489563 bytes sent via SQL*Net to client
1760245 bytes received via SQL*Net from client
21729 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
325917 rows processed
网管联盟bitsCN@com
第二句:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=346 Card=663 Bytes=2 94372)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYTABLES' (Cost=346 Card=663
Bytes=294372)
2 1 INDEX (RANGE SCAN) OF 'PK_MYTABLES' (UNIQUE) (Cost=5 Card=663)
Statistics
----------------------------------------------------------
1278 recursive calls
0 db block gets
6647 consistent gets
292 physical reads
0 redo size
3544898 bytes sent via SQL*Net to client
42640 bytes received via SQL*Net from client
524 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7838 rows processed
显而易见,第1句没有使用索引,第2句使用了主键索引pk_mytables. FF的巨大影响由此可见一斑。由此想到,我们在写sql 语句时,如果预先估计一下 FF, 你就几乎可以预见到 Oracle 会否使用索引。 网管网www_bitscn_com
二讲,索引也有好坏
索引有 B tree 索引, Bitmap 索引, Reverse b tree 索引, 等。最常用的是 B tree 索引。 B 的全称是Balanced , 其意义是,从 tree 的 root 到任何一个leaf ,要经过同样多的 level. 索引可以只有一个字段(Single column), 也可以有多个字段(Composite),最多32个字段,8I 还支持 Function-based index. 许多developer 都倾向于使用单列B 树索引。
所谓索引的好坏是指:
1,索引不是越多越好。特别是大量从来或者几乎不用的索引,对系统只有损害。OLTP系统每表超过5个索引即会降低性能,而且在一个sql 中, Oracle 从不能使用超过 5个索引。
2,很多时候,单列索引不如复合索引有效率。
3,用于多表连结的字段,加上索引会很有作用。
那么,在什么情况下单列索引不如复合索引有效率呢?有一种情况是显而易见的,那就是,当sql 语句所查询的列,全部都出现在复合索引中时,此时由于 Oracle 只需要查询索引块即可获得所有数据,当然比使用多个单列索引要快得多。(此时,这种优化方式被称为 Index only access path)
中国网管论坛bbs.bitsCN.com
除此之外呢?我们还是来看一个例子吧:
在 HP(Oracle 8.1.7) 上执行以下语句:
select count(1) from mytabs where coid>=130000 and issuedate >= to_date ('2001-07-20', 'yyyy-mm-dd')。
一开始,我们有两个单列索引:I_mytabs1(coid), I_mytabs2(issuedate), 下面是执行情况:
COUNT(1)
----------
6427
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=384 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_MYTABS' (Cost=384 Card
=126 Bytes=1386)
3 2 INDEX (RANGE SCAN) OF 'I_MYTABS2' (NON-UNIQUE) (Cost=11
Card=126)
Statistics
----------------------------------------------------------
172 recursive calls
1 db block gets
5054 consistent gets
2206 physical reads
0 redo size
网管u家u.bitsCN.com
293 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到,它读取了7000个数据块来获得所查询的 6000多行。
现在,去掉这两个单列索引,增加一个复合索引I_mytabs_test ( coid, issuedate), 重新执行,结果如下:
COUNT(1)
----------
6436
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'I_MYTABS_TEST' (NON-UNIQUE) (Cost=3 Card=126 Bytes=1386)
Statistics
----------------------------------------------------------
806 recursive calls
5 db block gets
网管网www.bitscn.com
283 consistent gets
76 physical reads
0 redo size
293 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到,这次只读取了300个数据块。
7000块对300块,这就是在这个例子中,单列索引与复合索引的代价之比。这个例子提示我们, 在许多情况下,单列索引不如复合索引有效率。
可以说,在索引的设置问题上,其实有许多工作可以做。正确地 【转自www.bitsCN.com】
select count(1) from status ;
select count(1) from status s where s.sts = 'I' ;
select count(distinct sts) from stats;这4个数分别是多少?
---------------------------看在这么多分的份上,我再问一次-------------------------------------你的表中数据100W,取了85W出来?(这个问题是要看统计信息对不对)如果真是这样,不走索引是对的。走索引会更慢OK,如果真是从100W中取了85W, 要加快速度,那好,用并行(要看你的机器怎么样了)。sql> alter table basic_info parallel;
sql> alter table stats paralle;然后再执行你的查询,看看是不是快了--------------------------------------------------------------------------------------------
select blevel, leaf_blocks, num_rows, clustering_factor from user_indexes where table_name=upper('basic_info');
select blocks from user_tables where table_name=upper('status');
select blevel, leaf_blocks, num_rows, clustering_factor from user_indexes where table_name=upper('status');把这些查询的结果都show出来看看
1. blocks
34847
2. blevel leaf_blocks num_rows clustering_factor
2 3699 854455 171569
2 2267 854455 502243
2 3673 854455 732016
2 5346 854455 171569
2 4168 854455 171569'status'表:
1. blocks
5352
2. blevel leaf_blocks num_rows clustering_factor
2 5923 841141 24453
2 2977 854455 19265
而如果多取一个字段则表basic_info表的索引就用不上,并查询很慢(25秒).
收藏了:)