介绍B树索引
16lz
2021-03-30
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 300col index_name for a30col INDEX_TYPE for a10col TABLE_NAME for a20col TABLESPACE_NAME for a30col status for a20select index_name, index_type, table_name, tablespace_name, statusfrom user_indexeswhere table_name in ('CUST','ADDRESS');INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME STATUS------------------------------ ---------- ------------------------------ ----------------------------------------------------------- ------------------------CUST_IDX1 NORMAL CUST REPORTING_INDEX VALIDCUST_PK NORMAL CUST REPORTING_INDEX VALIDCUST_UK1 NORMAL CUST REPORTING_INDEX VALIDADDR_FK1 NORMAL ADDRESS REPORTING_INDEX VALIDcol index_name for a30col column_name for a30col column_position for 99999999999999select index_name, column_name, column_positionfrom user_ind_columnswhere table_name in ('CUST','ADDRESS')order by index_name, column_position;SQL> col index_name for a30SQL> col column_name for a30SQL> col column_position for 99999999999999SQL> 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 1CUST_IDX1 LAST_NAME 1CUST_PK CUST_ID 1CUST_UK1 LAST_NAME 1CUST_UK1 FIRST_NAME 2col segment_name for a30col segment_type for a30col extents for 999999999999col bytes for 999999999999select a.segment_name, a.segment_type, a.extents, a.bytesfrom user_segments a, user_indexes bwhere a.segment_name = b.index_nameand b.table_name in ('CUST','ADDRESS');SQL> col segment_name for a30SQL> col segment_type for a30SQL> col extents for 999999999999SQL> col bytes for 999999999999SQL> 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 131072CUST_PK INDEX 1 131072CUST_UK1 INDEX 1 131072索引场景简介
当向表中插入行时，Oracle将分配由无路数据库块组成的区，Oracle还将为索引分配块，对于每个插入到表中的记录，Oracle还将创建一个包含Rowid和列值的索引条目。
每个索引项的rowid指向存储该表的列值的数据文件和块。
当从一个表及其对应的索引选择数据时，有三种情况
- SQL查询所需要的所有表的数据都在索引中，因此只需要访问索引块，不需要从表中读取块。
- 查询所需的所有信息没有都包含在索引块中，因此查询优化器选择既访问索引块也访问表块来检索需要的数据，以满足查询的结果。
- 查询优化器选择不访问索引。因此只访问表块。
场景一：所有数据位于索引块。
有两种情况，在每种情况下，执行查询需要的所有数据，包括返回给用户的数据，以及在where字句中被评估的数据，都位于该索引。- 索引范围扫描（index range scan） :如果优化器确定它使用索引结构检索查询所需的多个行是有效的，那么就使用这种索引。 索引范围扫描被广泛应用在各种各样的情况- 索引快速全扫描（index fast full scan）如果优化器确定表中大部分行所需要进行检索，那么就使用这种扫描。但是所有需要的信息都存储在索引中。由于索引结构通常比表结构小，优化器确定全索引扫描（比全表扫描）更高效。这种情况对于统计（count）值的查询比较常见演示1 索引范围扫描
场景二： 索引中不包含所有信息
场景三： 只有表被访问信息
在创建前估算索引表大小
显示创建索引的代码
删除B树索引
管理带约束的B树索引
输入一个模式，查询是否存在相应的外键约束
set linesize 300col CHECKER for a20col INDEX_TYPE for a20col OWNER for a20col TABLE_NAME for a20col INDEX_NAME for a20col CONSTRAINT_NAME for a20col COLS for a20SELECT 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.colsFROM (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) consLEFT 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) indON cons.cols = ind.colsAND cons.table_name = ind.table_nameAND cons.owner = ind.table_ownerORDER BY checker, cons.owner, cons.table_name;set linesize 300col CHECKER for a20col INDEX_TYPE for a20col OWNER for a20col TABLE_NAME for a20col INDEX_NAME for a20col CONSTRAINT_NAME for a20col COLS for a20SQL> 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: testidxold 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每一份赞赏源于懂得
赞赏
0人进行了赞赏支持
更多相关文章
- 索引不是越多越好，理解索引结构原理，才有助于我们建立合适的索引！
- Kubernetes 部署Redis主从服务(StatefulSet)
- jenkins分布式节点添加
- Lindorm原理 | Lindorm全文索引技术介绍
- python自动创建每周周报模板
- Hudi原理 | 在Apache Hudi中为快速更删配置合适的索引原创
- Oracle索引整理
- gitlab迁移及迁移后500问题
- 如何使用iMovie模板创建新预告片？