有关Oracle索引的使用

Question

对于数据库索引的概念和工作方式有待进一步了解，求指点：如何使用索引？比如在一张有10万条记录的表tenth中，建立了例如id, date这两个字段的索引，查询更快的SQL语句要怎么写呢？小白一点，没有建立索引的时候我是这样写的：
select id, date from tenth where id = 's008' and date = to_date('2008-11-12', 'yyyy-mm-dd hh24-mi-ss');
那建立索引之后要该写什么东西吗？因为我感觉速度一点也没有快:(，所以我肯定是那里还没搞清楚，特别是发现了index关键字不会用之后。

Answer 1

关键看看你的id和date中的不同数据是多少，如果很多，并且比较平均，建立index就会很快；如果很少，那么在比较集中的记录中就不会起什么作用。

Answer 2

你的SQL语句写法没有问题,如果分别在ID和DATE这两个字段上建立了索引,那么在符合一定的条件下就会自动用到索引;如果建了索引却没有用到有很多原因决定.比如:
1: 可能你的ORACLE优化器没有选择走索引这个执行计划,而且选择走其他的.可能没有分析表.
2: 如果SELECT出的记录数占全部数据的30%-40%,那么速度会明显增加;
   但如果SELECT出的记录数占全部数据越接近90%-100%,那么效率会下降得越快.
3: 小表不建议走索引, 因为可能全表扫描会比你走索引的执行计划效率更高.如果想强制使用索引,可以使用ORACLE中的hint(暗示):
SELECT /*+INDEX(TENTH INDEX_NAME)*/ ID, DATE 
FROM TENTH 
WHERE ID = 'S008' 
AND DATE = TO_DATE('2008-11-12', 'YYYY-MM-DD HH24-MI-SS');

Answer 3

    B*Tree索引是“传统的”索引。它是迄今为止在Oracle和大部分其他数据库中使用最多的索引。其结构上和二叉树相似，根据码提供对单个行或一系统行的快速访问，通常需要很少的读取就能找到正确的行。    如果LZ删除以id, date这两个字段的索引，而仅用id做索引可能更合适些。另外语句可更改为：
select id, date from tenth where id = 's008' and to_char(date,'yyyy-mm-dd') = '2008-11-12';

Answer 4

楼上几位分析了索引的使用。
我补充一点，如果id字段是主键，那默认就是索引了。
还有，LZ说没有明显变化，最好用具体的执行时间来说话 set timing on  ，看看前后时间差异多大。

Answer 5

如果SELECT出的记录数占全部数据的30%-40%,那么速度会明显增加; ----------------------
这个比例值得商榷，没那么高LZ 的情况 ： create index idx_test on test(id,date) compute statistics;

Answer 6

最近 在优化sql 想起用索引，我也遇到了楼主的问题，学习了

Answer 7

to_char(date,'yyyy-mm-dd') = '2008-11-12'　改成这样更慢

Answer 8

B*树是一个绝佳的通用索引机制，无论是大表还是小表都很适用，随着底层表大小的增长，获取数据的性能只会稍有恶化（或者根本不会恶化）。如果有太多的索引，DML的性能就会受到影响。如果索引太少，又会影响查询（包括插入、更新和删除）的性能。要找到一个合适的平衡点，这对于应用的性能至关重要。B*树索引中不存在非惟一（nonunique）条目。在一个非惟一索引中，Oracle会把rowid作为一个额外的列（有一个长度字节）追加到键上，使得键惟一。B*树的特点之一是：所有叶子块都应该在树的同一层上。这一层也称为索引的高度（height），这说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。