B树索引
简介
B树索引是Oracle默认索引，B树索引可以提高SQL语句的性能，强制执行主键和唯一键约束的唯一性，减少通过主键和外键约束关联的父表和子表间潜在都锁定问题。
创建索引步骤
- 估算索引大小
- 指定表空间
- 允许对象从他们的表空间继承存储参数
- 定义创建索引要使用的命名标准
索引的创建
创建数据表空间
create tablespace reporting_data
datafile '+DATA/reporting_data01.dbf'
size 1G
extent management local
uniform size 1M
segment space management auto;创建索引表空间
create tablespace reporting_index
datafile '+DATA/reporting_index01.dbf'
size 500M
extent management local
uniform size 128K
segment space management auto
nologging;创建表，并指定表空间
CREATE TABLE cust(
cust_id NUMBER
,last_name VARCHAR2(30)
,first_name VARCHAR2(30))
TABLESPACE reporting_data;创建索引并指定索引表空间
create index cust_idx1 on cust(last_name) tablespace reporting_index online;对于新创建的表，进行一次统计信息收集
SQL> exec dbms_stats.gather_table_stats(ownname=>'testidx',tabname=>'CUST',cascade=>true);
PL/SQL procedure successfully completed.配置主键
ALTER TABLE cust ADD CONSTRAINT cust_pk PRIMARY KEY (cust_id) USING INDEX TABLESPACE reporting_index;配置唯一性约束
ALTER TABLE cust ADD CONSTRAINT cust_uk1 UNIQUE (last_name, first_name) USING INDEX TABLESPACE reporting_index;创建地址表，并指定表空间
CREATE TABLE address(
address_id NUMBER
,cust_id NUMBER
,street VARCHAR2(30)
,city VARCHAR2(30)
,state VARCHAR2(30))
TABLESPACE reporting_data;给地址表添加外键约束，外键索引对应cust表的cust_id列
ALTER TABLE address ADD CONSTRAINT addr_fk1 FOREIGN KEY (cust_id) REFERENCES cust(cust_id);创建address表的外键索引
CREATE INDEX addr_fk1 ON address(cust_id) TABLESPACE reporting_index;报告索引
set linesize 300
col index_name for a30
col INDEX_TYPE for a10
col TABLE_NAME for a20
col TABLESPACE_NAME for a30
col status for a20
select index_name, index_type, table_name, tablespace_name, status
from user_indexes
where table_name in ('CUST','ADDRESS');
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ ----------------------------------------------------------- ------------------------
CUST_IDX1 NORMAL CUST REPORTING_INDEX VALID
CUST_PK NORMAL CUST REPORTING_INDEX VALID
CUST_UK1 NORMAL CUST REPORTING_INDEX VALID
ADDR_FK1 NORMAL ADDRESS REPORTING_INDEX VALIDcol index_name for a30
col column_name for a30
col column_position for 99999999999999
select index_name, column_name, column_position
from user_ind_columns
where table_name in ('CUST','ADDRESS')
order by index_name, column_position;
SQL> col index_name for a30
SQL> col column_name for a30
SQL> col column_position for 99999999999999
SQL> select index_name, column_name, column_position
2 from user_ind_columns
3 where table_name in ('CUST','ADDRESS')
4 order by index_name, column_position;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
ADDR_FK1 CUST_ID 1
CUST_IDX1 LAST_NAME 1
CUST_PK CUST_ID 1
CUST_UK1 LAST_NAME 1
CUST_UK1 FIRST_NAME 2col segment_name for a30
col segment_type for a30
col extents for 999999999999
col bytes for 999999999999
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 ('CUST','ADDRESS');
SQL> col segment_name for a30
SQL> col segment_type for a30
SQL> col extents for 999999999999
SQL> col bytes for 999999999999
SQL>
SQL> select a.segment_name, a.segment_type, a.extents, a.bytes
2 from user_segments a, user_indexes b
3 where a.segment_name = b.index_name
4 and b.table_name in ('CUST','ADDRESS');
SEGMENT_NAME SEGMENT_TYPE EXTENTS BYTES
------------------------------ ------------------------------ ------------- -------------
CUST_IDX1 INDEX 1 131072
CUST_PK INDEX 1 131072
CUST_UK1 INDEX 1 131072
索引场景简介
当向表中插入行时，Oracle将分配由无路数据库块组成的区，Oracle还将为索引分配块，对于每个插入到表中的记录，Oracle还将创建一个包含Rowid和列值的索引条目。
每个索引项的rowid指向存储该表的列值的数据文件和块。
当从一个表及其对应的索引选择数据时，有三种情况
- SQL查询所需要的所有表的数据都在索引中，因此只需要访问索引块，不需要从表中读取块。
- 查询所需的所有信息没有都包含在索引块中，因此查询优化器选择既访问索引块也访问表块来检索需要的数据，以满足查询的结果。
- 查询优化器选择不访问索引。因此只访问表块。
场景一：所有数据位于索引块。
有两种情况，在每种情况下，执行查询需要的所有数据，包括返回给用户的数据，以及在where字句中被评估的数据，都位于该索引。
- 索引范围扫描（index range scan） :如果优化器确定它使用索引结构检索查询所需的多个行是有效的，那么就使用这种索引。 索引范围扫描被广泛应用在各种各样的情况
- 索引快速全扫描（index fast full scan）如果优化器确定表中大部分行所需要进行检索，那么就使用这种扫描。但是所有需要的信息都存储在索引中。由于索引结构通常比表结构小，优化器确定全索引扫描（比全表扫描）更高效。这种情况对于统计（count）值的查询比较常见演示1 索引范围扫描
场景二： 索引中不包含所有信息
场景三： 只有表被访问信息
在创建前估算索引表大小
显示创建索引的代码
删除B树索引
管理带约束的B树索引
输入一个模式，查询是否存在相应的外键约束
set linesize 300
col CHECKER for a20
col INDEX_TYPE for a20
col OWNER for a20
col TABLE_NAME for a20
col INDEX_NAME for a20
col CONSTRAINT_NAME for a20
col COLS for a20
SELECT
CASE WHEN ind.index_name IS NOT NULL THEN
CASE WHEN ind.index_type IN ('BITMAP') THEN
'** Bitmp idx **'
ELSE
'indexed'
END
ELSE
'** Check idx **'
END checker
,ind.index_type
,cons.owner, cons.table_name, ind.index_name, cons.constraint_name, cons.cols
FROM (SELECT
c.owner, c.table_name, c.constraint_name
,LISTAGG(cc.column_name, ',' ) WITHIN GROUP (ORDER BY cc.column_name) cols
FROM dba_constraints c
,dba_cons_columns cc
WHERE c.owner = cc.owner
AND c.owner = UPPER('&&schema')
AND c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
GROUP BY c.owner, c.table_name, c.constraint_name) cons
LEFT OUTER JOIN
(SELECT
table_owner, table_name, index_name, index_type, cbr
,LISTAGG(column_name, ',' ) WITHIN GROUP (ORDER BY column_name) cols
FROM (SELECT
ic.table_owner, ic.table_name, ic.index_name
,ic.column_name, ic.column_position, i.index_type
,CONNECT_BY_ROOT(ic.column_name) cbr
FROM dba_ind_columns ic
,dba_indexes i
WHERE ic.table_owner = UPPER('&&schema')
AND ic.table_owner = i.table_owner
AND ic.table_name = i.table_name
AND ic.index_name = i.index_name
CONNECT BY PRIOR ic.column_position-1 = ic.column_position
AND PRIOR ic.index_name = ic.index_name)
GROUP BY table_owner, table_name, index_name, index_type, cbr) ind
ON cons.cols = ind.cols
AND cons.table_name = ind.table_name
AND cons.owner = ind.table_owner
ORDER BY checker, cons.owner, cons.table_name;
set linesize 300
col CHECKER for a20
col INDEX_TYPE for a20
col OWNER for a20
col TABLE_NAME for a20
col INDEX_NAME for a20
col CONSTRAINT_NAME for a20
col COLS for a20
SQL> SELECT
2 CASE WHEN ind.index_name IS NOT NULL THEN
3 CASE WHEN ind.index_type IN ('BITMAP') THEN
4 '** Bitmp idx **'
5 ELSE
6 'indexed'
7 END
8 ELSE
9 '** Check idx **'
10 END checker
11 ,ind.index_type
12 ,cons.owner, cons.table_name, ind.index_name, cons.constraint_name, cons.cols
13 FROM (SELECT
14 c.owner, c.table_name, c.constraint_name
15 ,LISTAGG(cc.column_name, ',' ) WITHIN GROUP (ORDER BY cc.column_name) cols
16 FROM dba_constraints c
17 ,dba_cons_columns cc
18 WHERE c.owner = cc.owner
19 AND c.owner = UPPER('&&schema')
20 AND c.constraint_name = cc.constraint_name
21 AND c.constraint_type = 'R'
22 GROUP BY c.owner, c.table_name, c.constraint_name) cons
23 LEFT OUTER JOIN
24 (SELECT
25 table_owner, table_name, index_name, index_type, cbr
26 ,LISTAGG(column_name, ',' ) WITHIN GROUP (ORDER BY column_name) cols
27 FROM (SELECT
28 ic.table_owner, ic.table_name, ic.index_name
29 ,ic.column_name, ic.column_position, i.index_type
30 ,CONNECT_BY_ROOT(ic.column_name) cbr
31 FROM dba_ind_columns ic
32 ,dba_indexes i
33 WHERE ic.table_owner = UPPER('&&schema')
34 AND ic.table_owner = i.table_owner
35 AND ic.table_name = i.table_name
36 AND ic.index_name = i.index_name
37 CONNECT BY PRIOR ic.column_position-1 = ic.column_position
38 AND PRIOR ic.index_name = ic.index_name)
39 GROUP BY table_owner, table_name, index_name, index_type, cbr) ind
40 ON cons.cols = ind.cols
41 AND cons.table_name = ind.table_name
42 AND cons.owner = ind.table_owner
43 ORDER BY checker, cons.owner, cons.table_name;
Enter value for schema: testidx
old 19: AND c.owner = UPPER('&&schema')
new 19: AND c.owner = UPPER('testidx')
old 33: WHERE ic.table_owner = UPPER('&&schema')
new 33: WHERE ic.table_owner = UPPER('testidx')
CHECKER INDEX_TYPE OWNER TABLE_NAME INDEX_NAME CONSTRAINT_NAME COLS
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
indexed NORMAL TESTIDX ADDRESS ADDR_FK1 ADDR_FK1 CUST_ID