选择和优化索引

理解B树索引(BANLANCE)

1建表

createtablecust(

cust_idnumber,

last_namevarchar2(30),

first_namevarchar2(30));

2确定多个SQL查询会频繁在where子句使用last_name列，创建下列索引：

createindexindex_idx1oncust(last_name);

插入数据

insertintocustvalues(7,'ACER','SCOTT');

insertintocustvalues(4,'KAN','JIM');

insertintocustvalues(2,'FAN','BOB');

insertintocustvalues(9,'STAR','KIM');

...

insertintocustvalues(117,'ACER','SID');

3更新表统计信息，为查询优化器提供足够信息

SQL>execdbms_stats.gather_table_stats(ownname=>'mlq',tabname=>'CUST',cascade=>true);

插入更多的数据，从表及索引中查询数据，有三种可能的块景：

A所有数据全在索引数据块

发生索引范围描扫索引快速全扫描

selectlast_namefromcustwherelast_name='ACER';范围扫描

自动跟踪：

setautotraceon

selectcount(last_name)fromcust;

发生索引快速扫描，

通过查看执行计划及统计信息，可以得到更详细的结果

B并不是所有信息都包含在索引中

selectlast_name，first_namefromcustwherelast_name='ACER';

访问first_name通过rownid

C仅访问表数据块

select*fromcust;

全扫描

B树索引是oracle默认的索引类型，对于大多数oltp来说足够了。

它是很高效的，恰当使用，会使查询快很多，如果索引结构本身含含查询要获取的列值，

那么就不需要访问表数据块。要理解这个原理，写出高效索引，确定要创建索引的列，

以及判断对某些查询来说组合查询是否更为高效。

估算一个索引所需要的空间

创建一个索引前，可以通过存储过程来估算所需的空间

setsererouton

execdbms_stats.gather_table_stats(user,'CUST');

variableused_bytesnumber

variablealloc_bytesnumber

execdbms-space.create_index_cost('createindexcust_idx2oncust(first_name)',:used_bytes,:alloc_bytes);)

print:used_bytes

print:alloc_bytes

查看输出结果