LLL的数据库培训-81-第五部分—Oracle11g-Oracle SQL对象管理—第8讲—索引之B*Tree索引

2023年9月21日 73点热度 0人点赞 0条评论

LLL的数据库培训-81-第五部分—Oracle11g-Oracle SQL对象管理—第8讲—索引之创建与维护—B*Tree索引

一、B树索引

1、表和B树索引的物理布局

（1）上图

该图的顶部显示了表CUST和一些数据。这个表的数据存储在两个独立的数据文件中，而每个数据文件含有两个块。

（2）下图

该图显示了均衡的、树形结构的B树索引CUST_IDX1，这个索引创建在表CUST的LAST NAME列上。

该索引存储在一个数据文件中并由4个块组成。

（3）索引的定义与表和列有关。

B树索引只包含两个值，一个是ROWID，一个是列值。

索引结构映射了表的ROWID和用于创建索引的列数据。

ROWID通常是数据库中行的唯一标识，它含有行的物理位置信息(数据文件、块和行在块中的位置)。

图中通过两条虚线，展示了ROWID(索引结构中的)与表中列值ACER所在行物理位置的对应关系。B树索引拥有树形的层次结构。

当Oracle访问这种索引时，会从顶端节点开始，调用根(或头)块。

Oracle通过这个块确定要读取的第二层块(分支块)。第二层块指向多个第三层块(叶节点)，其中含有ROWID和名称值。

在这种结构中，要找到ROWID需要花费3次IO操作。确定了ROWID后Oracle就会通过它读取含有该ROWID的表数据块。

关于:Oracle中ROWID的介绍，见：https://www.topunix.com/post-11303.html

2、创建索引的语法

（1）语法

CREATE [UNIQUE] [BITMAP | DOMAIN] INDEX index_name 
ON table_name (column1 [ASC | DESC], column2 [ASC | DESC], ...) 
[TABLESPACE tablespace_name] 
[PARALLEL integer] 
[NOLOGGING | LOGGING];

（2）解释

UNIQUE：                      可选项，用于创建唯一索引。
BITMAP：                      可选项，用于创建位图索引。
DOMAIN：                      可选项，用于创建索引域索引。
index_name：                  指定要创建的索引的名称。
table_name：                  指定要在其上创建索引的表的名称。
column1, column2, ...：       指定要在其上创建索引的列的名称。可以指定多个列，以逗号分隔。
ASC 或 DESC：                 可选项，用于指定每个列的排序顺序，默认是升序 (ASC)。
TABLESPACE tablespace_name：  可选项，用于指定索引存储的表空间。
PARALLEL integer：            可选项，用于指定并发创建索引的级别。
NOLOGGING 或 LOGGING：        可选项，用于指定是否执行日志记录。

3、创建表（1000条测试数据）

（1）创建测试表

CREATE TABLE test (
ID NUMBER primary key,
name VARCHAR2(50),
age NUMBER,
identification VARCHAR2(18)
);

（2）创建姓名随机函数

CREATE OR REPLACE FUNCTION generate_random_name RETURN VARCHAR2 IS
v_first_names VARCHAR2(10000) := 'James,John,Robert,Michael,William,David,Richard,Joseph,Charles,Thomas,Christopher,Daniel,Matthew,Anthony,Donald,Mark,Paul,Steven,Andrew,Kenneth,George,Joshua,Edward,Brian,Ronald,Timothy,Jason,Jeffrey,Frank,Scott,Eric,Stephen,Andrew,Gary,Patrick,Michael,Terry,Brandon,Jacob,Sean,Ryan,Nicholas,Gregory,Peter,Jonathan,Justin,Scott,Benjamin,Walter,Marry,Rebecca,Jennifer,Linda,Patricia,Amy,Elizabeth,Susan,Karen,Nancy,Margaret,Dorothy,Helen,Sandra,Donna,Carol,Ruth,Sharon,Michelle,Laura,Sarah,Kimberly,Deborah,Jessica,Melissa';
v_last_names VARCHAR2(10000) := 'Smith,Johnson,Williams,Brown,Jones,Garcia,Miller,Davis,Rodriguez,Martinez,Hernandez,Lopez,Gonzalez,Wilson,Anderson,Thomas,Taylor,Moore,Jackson,Thompson,White,Lee,Lopez,Harris,Clark,Lewis,Robinson,Walker,Hall,Young,Allen,Wright,King,Scott,Green,Baker,Hill,Carter,Mitchell,
Turner,Adams,James,Powell,Nelson,Campbell,Parker,Cooper,Ross,Ortiz,Brooks,Jenkins,Hughes,Reyes,Morgan,Rogers,Peterson,Bell,Boyd,Bailey,Reed,James,Coleman,Gray,Ramirez,Gray,Ramirez,Kelly,Howard,Ward,Cox,Diaz,Richardson,Wood,Watson,Brooks,Bennett,Gray,James,Reed,Foster,Gonzalez';
v_first_name VARCHAR2(50);
v_last_name VARCHAR2(50);
BEGIN
v_first_name := REGEXP_SUBSTR(v_first_names, '[^,]+', 1, ROUND(DBMS_RANDOM.VALUE(1, REGEXP_COUNT(v_first_names, ',') + 2)));
v_last_name := REGEXP_SUBSTR(v_last_names, '[^,]+', 1, ROUND(DBMS_RANDOM.VALUE(1, REGEXP_COUNT(v_last_names, ',') + 2)));

RETURN v_first_name || ' ' || v_last_name;
END;
/

（3）生成测试数据

DECLARE
i NUMBER := 1;
random_age NUMBER;
random_identification VARCHAR2(18);
COUNT_ID number;
BEGIN
FOR i IN 1..10000 LOOP

random_age := ROUND(DBMS_RANDOM.VALUE(18, 80));  --生成一个随机的年龄值（18到80之间），用于测试

LOOP  -- 生成一个随机的18位身份证号码，用于测试 
  random_identification := LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 999999999999999999)), 18, '0');
  SELECT COUNT(*) INTO COUNT_ID FROM test WHERE identification = random_identification; -- 检查生成的身份证号码是否已存在于表中
  EXIT WHEN COUNT_ID = 0 AND SUBSTR(random_identification, 1, 1) <> '0';
END LOOP;

INSERT INTO test (ID, name, age, identification) VALUES (10000 + i, generate_random_name(), random_age, random_identification);
IF MOD(i, 1000) = 0 THEN      --每1000条数据提交一次事务
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/

4、查看已经有的索引

select * from user_indexes;

二、索引创建

有时一张大表（几千万或几亿）的索引占用空间会比较大，我们需要在创建前评估其占用的资源。

1、创建前需要考虑的问题

--评估索引的占用空间的大小
--考虑是否对索引的表空间与表的表空间进行分类（易于管理维护）
--是否允许对象从它们的表空间集成存储参数（如果默认，则表和索引会继承表空间的存储参数）
--定义创建索引时要使用的命名标准

2、评估索引占用空间

（1）方法1：测试机测试

在测试环境中创建，根据测试表的数据占用与创建列的索引的数据占用比例，预测生产的索引数据占用。

通常情况下，生产数据是不断增长的，测试库一般数据量比较小，根据测试库中表与索引的空间占用比，可以大概推断出生产库中索引的空间占用。

（2）方法2：根据存储过程在估算

set serverout on;
exec dbms_stats.gather_table_stats('TEST','TEST');
variable used_bytes number;
variable alloc_bytes number;
exec dbms_space.create_index_cost('create index idx_test on test(name)',:used_bytes,:alloc_bytes);
print :used_bytes
print :alloc_bytes

used_bytes: 数值型参数，表示索引当前实际使用的字节数。它用来指定索引的已使用空间大小。
alloc_bytes: 数值型参数，表示为索引分配的总字节数。它用来指定索引的总分配空间大小。

--测试创建索引

create index idx_test on test(name);

--查看索引分配空间情况(bytes)

col segment_name for a20
col segment_type for a20
col tablespace_name for a20
select segment_name,segment_type,tablespace_name,bytes from user_segments where segment_name='IDX_TEST';

3、为索引创建单独的表空间

考虑到数据的表空间增长问题，以及后期索引管理问题，我们可以将索引创建时单独创建表空间。

create tablespace indx_tbsp datafile '/oracle/app/oracle/oradata/mesorcl/indx_data01' size 10m autoextend on;
create index idx_test on test(name) tablespace indx_tbsp;

4、命名标准

在创建和管理索引时，制定些命名标准是非常可取的。考虑以下动机。

当错误消息中包含表示表、索引类型等的信息时，简化了对问题的诊断。

显示索引信息的报告更容易被分组，因此更具可读性并更容易地发现其中的规律和问题

鉴于这些需求，这里有一些示例索引命名指南：

--主键索引名称应包含表名和一个后缀，如PK。
--唯一键索引名称应包含表名和一个后缀，如_UKN,其中N是一个数字。
--外键列上的索引应包含外键表和一个后缀，如_FKN,其中N是一个数字。
--对于不用于约束的索引，使用表名和一个后缀，如_IDXN,其中N是一个数字 。
--基于函数的索引的名称应包含表名和一个后缀，如_FCN,其中N是一个数字。

三、创建维护

1、创建新的索引

create index idx_name on test(identification);

2、查看索引

select index_name,index_type,table_owner,table_name,tablespace_name,status from user_indexes;

3、显示创建索引的代码

select dbms_metadata.get_ddl('INDEX','IDX_TEST') from dual;

4、删除索引

drop index idx_test;

5、收集表统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST', 'TEST');

本作品采用知识共享署名 4.0 国际许可协议进行许可