B树索引
B树索引基础知识：
表中的行标示符（rowid）和相关的列值存储在一个平衡的树状结构中。

使用B树索引的原因：
1）提供sql语句的性能
2）强制执行主键和唯一键约束的唯一性
3）减少通过主键和外键约束关联的父表和子表间潜在的锁表问题，即就是外键列上缺少B树索引可能导致表锁定问题发生

注意事项：
1）建议为索引创建单独的索引表空间便于管理，创建时建议使用统一大小的区减少对象创建和删除时的碎片；如果该索引表空间的索引倾向于扫描类的索引，建议选用大的数据块，以减少I/O。
2）从oracle11gr2开始，在创建表的时候还没有向表中插入数据时，相关的段和区会被推迟创建。
3）使一个索引不可见（invisible）在oracle11g开始支持。而且不可见索引仍然由oracle维护，但是查询优化器在确定执行计划的时候不考虑她。
4）如果需要外键约束，则必须使用主键或唯一键约束。
5）删除主键索引，必须先删除或禁用主机约束。在删除或禁用主键约束时，可以选择不删除相关索引。语法如下：
alter table test drop constraint test_pk keep index;
6）使一个索引不可用（unusable），oracle将不会再维护这个索引，并且优化器也不会考虑该索引。如果需要再次将一个不可用的索引再次可用，那么必须将其重建。
7）外键必须创建B树索引，不然当对子表操作时，父表会被全锁定。如何查看数据库哪些外键缺失索引，可见脚本：scripts:查看外键索引是否存在（http://www.yallonking.com/tools-scripts/2013/02/737.html）以及《外键缺失索引导致锁表的问题》（http://www.yallonking.com/database/troubleshooting/2013/02/739.html）

oracle如何使用B树索引：
1）所有数据都在索引块中
2）索引中不包含所有信息
3）只有表被访问

创建索引前，估算B树索引的大小：

SQL> sho user
USER is "SYS"
SQL> conn test/test 
Connected.
SQL> sho user
USER is "TEST"
SQL> select * from test;

no rows selected

SQL> drop table test;

Table dropped.

SQL> create table test as select rownum id,'yallonking' name from dual connect by level <= 1000;

Table created.

SQL> select * from test where rownum<10;

		ID NAME
---------- ----------
		1 yallonking
		2 yallonking
		3 yallonking
		4 yallonking
		5 yallonking
		6 yallonking
		7 yallonking
		8 yallonking
		9 yallonking

9 rows selected.

SQL> conn /as sysdba
Connected.
SQL> set serverout on
SQL> exec dbms_stats.gather_table_stats('TEST','TEST');

PL/SQL procedure successfully completed.

SQL> select to_char(LAST_ANALYZED,'yyyy/mm/dd hh24:mi:ss') from dba_tables where TABLE_NAME ='TEST';

TO_CHAR(LAST_ANALYZ
-------------------
2013/03/24 07:51:44
SQL> variable used_bytes number
SQL> variable alloc_bytes number
SQL> exec dbms_space.create_index_cost('create index test_id on test(id)',:used_bytes,:alloc_bytes);
BEGIN dbms_space.create_index_cost('create index test_id on test(id)',:used_bytes,:alloc_bytes); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 1731
ORA-06512: at "SYS.DBMS_SPACE", line 1752
ORA-06512: at line 1

注：此处版本不支持

SQL> select * from gv$version;

INST_ID BANNER
---------- ----------------------------------------------------------------
		1 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
		1 PL/SQL Release 10.2.0.1.0 - Production
		1 CORE 10.2.0.1.0      Production
		1 TNS for Linux: Version 10.2.0.1.0 - Production
		1 NLSRTL Version 10.2.0.1.0 - Production

重新在11g版本中重复以上步骤

SQL> select * from gv$version;

INST_ID BANNER
---------- --------------------------------------------------------------------------------
		1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
		1 PL/SQL Release 11.2.0.3.0 - Production
		1 CORE 11.2.0.3.0      Production
		1 TNS for Linux: Version 11.2.0.3.0 - Production
		1 NLSRTL Version 11.2.0.3.0 - Production

SQL> create table test as select rownum id,'yallonking' name from dual connect by level <= 1000;

Table created.

SQL> select * from test where rownum<10;

		ID NAME
---------- ----------
		1 yallonking
		2 yallonking
		3 yallonking
		4 yallonking
		5 yallonking
		6 yallonking
		7 yallonking
		8 yallonking
		9 yallonking

9 rows selected.

SQL> set serverout on
SQL> exec dbms_stats.gather_table_stats('SYS','TEST');

PL/SQL procedure successfully completed.

SQL> select to_char(LAST_ANALYZED,'yyyy/mm/dd hh24:mi:ss') from dba_tables where TABLE_NAME ='TEST';

TO_CHAR(LAST_ANALYZ
-------------------
2013/03/24 22:58:48

SQL> variable used_bytes number
SQL> variable alloc_bytes number
SQL> exec dbms_space.create_index_cost('create index test_id on test(id)',:used_bytes,:alloc_bytes);

PL/SQL procedure successfully completed.

SQL> print :used_bytes

USED_BYTES
----------
	4000

SQL> print :alloc_bytes

ALLOC_BYTES
-----------
	65536

SQL> declare
2  l_index_ddl varchar(1000);
3  l_used_bytes number;
4  l_allocated_bytes number;
5  begin
6  dbms_space.create_index_cost(
7  ddl =>'create index test_id on test(id)',
8  used_bytes=>l_used_bytes,
9  alloc_bytes =>l_allocated_bytes);
10  dbms_output.put_line('used ='||l_used_bytes||'bytes'
11  ||'  allocated= '|| l_allocated_bytes || 'bytes');
12  end;
13  /
used =4000bytes  allocated= 65536bytes

PL/SQL procedure successfully completed.

以下在索引创建完成后，在10g和11g中均可查看oracle给该索引分配的空间大小：

SQL> create index test_id on test(id);

Index created.

SQL> COL SEGMENT_NAME FOR A20
SQL> select a.segment_name,a.tablespace_name,b.table_name,a.bytes/1024/1024 mbytes,a.blocks
2  from dba_segments a, dba_indexes b
3  where a.segment_name = b.index_name
4  and a.segment_type = 'INDEX'
5  and b.index_name='TEST_ID'
6  group by a.tablespace_name,b.table_name,a.segment_name,a.blocks,a.bytes/1024/1024
7  order by a.bytes/1024/1024 desc,a.tablespace_name,table_name;

SEGMENT_NAME         TABLESPACE_NAME                TABLE_NAME                         MBYTES     BLOCKS
-------------------- ------------------------------ ------------------------------ ---------- ----------
TEST_ID              SYSTEM                         TEST                                .0625          8

SQL> SELECT 0.0625*1024*1024 FROM DUAL;

0.0625*1024*1024
----------------
		65536