复合索引和一般索引的问题

[碧水幽幽泉]

--创建表
create table test1(id number, varchar2(10));
insert into test1 values(1,'索引测试1');
commit;

--创建索引
create index ix_id on test1(id);
create index ix_id_name on test1(id, name);
drop index ix_id_name;
drop index ix_id;

--查看解释计划
select * from test1 where id = 1;       --where条件中只有id列
--1.当ix_id、ix_id_name都存在时，使用的是：ix_id_name。 解释计划如下：
SELECT STATEMENT, GOAL = ALL_ROWS			1	1	25
 INDEX RANGE SCAN	MYTEST	IX_ID_AND_NAME	1	1	25

--2.当只有ix_id存在时，使用的是：ix_id 解释计划如下：
SELECT STATEMENT, GOAL = ALL_ROWS			2	1	25
 TABLE ACCESS BY INDEX ROWID	CMS0322_EN	TEST1	2	1	25
  INDEX RANGE SCAN	MYTEST	IX_ID	1	1	

我的问题是:
1.如果一般索引和复合索引均包含某一列时(如:ix_id_name和ix_id有共同的列:id),当where条件中只有id列时，使用的索引一定是复合索引吗？
2.复合索引和一般索引的效率，哪个高些？为什么？
3.复合索引和一般索引所占的空间哪个大些？
4.如果有复合索引了，还有创建一般索引的必要吗？
这里我使用：
select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('ix_id_name')

select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('ix_id');

结果是一样大，这样测试有误吗？

[iqlife]

1:肯定不是,ORALCE按照索引使用规则进行判断走哪个索引
2:没有绝对的,当你觉得使用单索引或者使用复合索引效率高的话,可以加/* */提示,强行按照自己的意图走所以
3:复合索引空间占用大,
4:当你觉得一般索引的效率比当前复合索引的效率高的时候就可以建立一般索引

[codearts]

表： test1(id number, name varchar2(10))

这个表只有2个字段。

1）
create index ix_id on test1(id);
上面这个索引，只有id一个字段
select * from test1 where id = 1; --where条件中只有id列
可见，ORACLE扫描了索引，但是索引段中没有NAME字段，所以得通过索引中的ROWID访问以得到NAME这个字段的数据

2）
create index ix_id_name on test1(id, name);
上面这个索引，有ID，NAME
select * from test1 where id = 1; --where条件中只有id列
这个时候，索引段中已经有id,name2个字段，可以直接从索引段中找到全部数据了，所以无需再访问表的数据段

通过比较1）和2）的执行计划，你就可以发现1）多做了一步从表段中取数据的步骤。

显示，2）的效率要比1）快，而且快很多

[碧水幽幽泉]

codearts.
再请教下：
按你说的，如果只有ix_id存在时。
select id from test1 where id = 1比select id，name from test1 where id = 1

要快吗？

[Paladin_china]

正是如此，因为直接可以从索引中得到要查的数据而不需要再去查表了

[codearts]

[Quote=引用 5 楼 qq646748739 的回复:]

codearts.
再请教下：
按你说的，如果只有ix_id存在时。
select id from test1 where id = 1比select id，name from test1 where id = 1

要快吗？
[/Quote]

那是必然的

[dingjun123]

看oracle优化文档，CBO啊，要收集统计信息
如果查询表的数据全能从索引中获取，那么会直接读索引不用读表了，当然要符合一定的条件，比如至少有一个索引列not null

[tangren]

1.不一定
2.关键看你的条件中是否使用到复合索引中的其它列。
3.一般来说，复合索引
4.针对复全索引的首列没必要再建单索引

[etsilence]

1:复合索引有引导列的概念，一般来说，只有where条件中包含引导列才可能使用符合索引(索引跳跃扫描除外），另外，要看你查询的所有列是否包含在复合索引中，如果包含，则走复合索引，否则应该是走单列索引。
2：索引的效率高不高要看你的具体应用，一般来说，能用单列索引实现的就不要用复合索引，复合索引占空间、维护麻烦、效率还低。
3：当然是复合索引占空间大
4：如果你的where条件始终是id，那么只需要一种索引就可以。

测试是对的，不过测试之前最好分析一下索引，analyze index index_name validate structure;
你的数据量太小，测试不出差别。