实现B树索引
这里将介绍使用B树索引时会遇到的典型任务。典型的任务包括。
.创建索引
.报告索引
.显示重新创建索引需要的代码
.删除索引
1 创建B树索引
下面给出的是一个示例脚本，它创建一个表，并在单独的表空间创建与之相关的索引。表和索引从表空间继承存储属性，这是因为在create table或create index语句中没有指定存储参数。此外，你希望主键和唯一键约束自动创建B树索引。
SQL> create table cust1( 2 cust_id number, 3 last_name varchar2(30), 4 first_name varchar2(30) 5 ) 6 tablespace reporting_data; Table created. SQL> alter table cust1 add constraint cust_pk primary key(cust_id) using index tablespace reporting_index; Table altered. SQL> alter table cust1 add constraint cust_uk1 unique(last_name,first_name) using index tablespace reporting_index; Table altered. SQL> create table address( 2 address_id number, 3 cust_id number, 4 street varchar2(30), 5 city varchar2(30), 6 state varchar2(30) 7 ) 8 tablespace reporting_data; Table created. SQL> alter table address add constraint addr_fk1 foreign key(cust_id) references cust1(cust_id); Table altered. SQL> create index addr_fk1 on address(cust_id) tablespace reporting_index; Index created.
此脚本创建了两个表。父表是cust1，它的主键是cust_id。子表是address，它的主键是address_id。在address表中，cust_id列作为外键存在，它映射到cust1表的cust_id列。
此脚本也创建了三个B树索引。其中第一个是创建主键约束时自动创建的。第二个索引是创建唯一约束时自动创建的。第三个索引是明确创建在address表中的cust_id外键列上的。所有这三个索引都是在reporting_index表空间中创建的，而表是在reporting_data表空间中创建的。
2 报告索引
上面的例子中创建的索引的详细信息可以通过查询数据字典来验证。
SQL> select index_name,index_type,table_name,tablespace_name,status from user_indexes where table_name in('CUST1','ADDRESS');
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME STATUS
-------------------------------------------------- --------------------------- -------------------------------------------------- -------------------------------------------------- --------
ADDR_FK1 NORMAL ADDRESS REPORTING_INDEX VALID
CUST_PK NORMAL CUST1 REPORTING_INDEX VALID
CUST_UK1 NORMAL CUST1 REPORTING_INDEX VALID
运行以下查询来验证创建了索引的列:
SQL> select index_name,column_name,column_position from user_ind_columns where table_name in('CUST1','ADDRESS') order by index_name,column_position;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------------------------------------- ------------------------------ ---------------
ADDR_FK1 CUST_ID 1
CUST_PK CUST_ID 1
CUST_UK1 LAST_NAME 1
CUST_UK1 FIRST_NAME 2
要显示区的数目和已使用的空间，可以运行以下查询:
SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST1','ADDRESS');
no rows selected
请注意，这个例子的输出结果显示，没有为索引分配段，区或空间。
从Oracle 11g第2版开始，在创建表时，如果还没有往表中插入数据，相关的段(和区)将会初步推迟创建。这意味着直到数据行被插入到相关的表之后，才会为相关的索引创建段。为了说明这一点，给CUST1表插入一行，也给ADDRESS表插入一行，如下所示:
SQL> insert into cust1 values(1,'STARK','JIM'); 1 row created. SQL> insert into address values(100,1,'Vacuum Ave','Portland','OR'); 1 row created. SQL> commit; Commit complete.
重新运行这个查询(段的使用报告)产生的输出如下:
SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST1','ADDRESS');
SEGMENT_NAME SEGMENT_TYPE EXTENTS BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
ADDR_FK1 INDEX 1 131072
CUST_PK INDEX 1 131072
CUST_UK1 INDEX 1 131072
3 显示创建索引的代码
有时候可能需要删除一些索引。这些索引可能是由过时的应用程序建立的，也可能是你自己以前建立的，但已经用不到了。在删除索引之前，建议你首先生成重新创建索引所需的数据定义语言(DDL)。如果删除索引对性能有不利影响而需要重新创建它，就可以重新创建索引(就像没有删除它一样)。
可以使用dbms_metadata.get_ddl函数来显示对象的DDL。确保为LONG变量设置适当的值，使用返回的CLOB值能全部显示出来。例如:
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;
下面是输出结果:
DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')
--------------------------------------------------------------------------------
CREATE INDEX "JY"."ADDR_FK1" ON "JY"."ADDRESS" ("CUST_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "REPORTING_INDEX"
这段代码显示了重新创建索引需要的所有内容。这些代码中的许多值反映了从索引表空间继承的默认设置或存储参数。
如果想要显示当前连接的用户的所有索引元数据，可以运行下面的代码:
SQL> select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes;
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------
CREATE INDEX "JY"."CUST_IDX1" ON "JY"."CUST" ("LAST_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
CREATE INDEX "JY"."CUST_IDX2" ON "JY"."CUST" ("FIRST_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
CREATE UNIQUE INDEX "JY"."CUST_PK" ON "JY"."CUST1" ("CUST_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
CREATE UNIQUE INDEX "JY"."CUST_UK1" ON "JY"."CUST1" ("LAST_NAME", "FIRST_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "REPORTING_INDEX"
CREATE INDEX "JY"."ADDR_FK1" ON "JY"."ADDRESS" ("CUST_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "REPORTING_INDEX"
如果当前连接的用户有很多索引，这个查询将会产生大量的输出。
4 删除B树索引
如果确定不再使用某个索引了，那么应该删除它。在删除索引之前，应采取必要的预防措施，以确保不会对性能产生不利影响。如果可能的话，最好的办法是在与生产环境同等条件(在硬件，数据，负载等方面)的测试环境中删除索引，确定对性能的不利影响。如果不可能进行彻底的测试，那么在删除前考虑先做以下工作。
.启用对索引的监测。
.使用索引不可见。
.使用索引不可用。
这样做是为了在实际删除之前，先确定该索引没有用于任何目的。监控索引会让你了解应用程序的select语句是否使用了它。但索引监控不会告诉你该索引是否被用于其他内部用途，如用来强制执行某个约束或防止锁定问题。
使用一个索引不可见需要Oracle 11g及以上版本。不可见索引仍然由Oracle维护，但查询优化器确定执行计划时不考虑它。请注意，不可见的索引仍然可以由Oracle在内部使用，用来避免锁定问题或强制执行约束。所以，使用索引不可见并不是用来确定该索引是否被使用的完全可靠的方法。
下面是使用索引 不可见的一个例子:
SQL> alter index addr_fk1 invisible; Index altered.
此代码使用索引对查询优化器不可见，因此，它不能在查询中用来检索行。然而，当修改表中的记录时，该索引结构仍然由Oracle维护。如果确定该索引对性能非常关键，那么可以通过如下命令很容易地使用它再次对优化器可见。
SQL> alter index addr_fk1 visible; Index altered.
删除索引之前的另一种选择是使其不可用。
SQL> alter index addr_fk1 unusable; Index altered.
此代码使得索引不可用，但不会删除它。不可用表示，不但优化器不会使用索引，而且当DML语句操作它的表时，Oracle也不会维护该索引。此外，不可用的索引不能在内部使用，用于强制执行约束或避免锁定问题。
如果需要重新启用不可用的索引，那么就必须重建它。而重建一个大型的索引，会消耗大量的时间和资源。
SQL> alter index addr_fk1 rebuild; Index altered.
当确信不需要某个索引后，就可以使用drop index语句来删除它。这个语句将永久删除该索引，找回该索引的唯一办法是重新创建它。
SQL> drop index addr_fk1; Index dropped.