目录
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');