Oracle 19C学习

2024-07-10 01:02| 来源: 网络整理| 查看: 265

什么是索引

索引的主要作用就是加速表的查询。索引可以显示创建，执行索引命令创建。索引也可以隐式创建，主键和唯一键都是隐式创建索引。

索引的特征索引是模式Schema Object，高于衍生数据对象。和表一样，索引也有自己的段结构。衍生数据对象：视图、同义词都是从表衍生出来的。表和索引都具有自己的物理对象，段所对应的数据文件。 Oracle使用索引来进行检索加速，索引可以提升SELECT语句的查询速度。使用快速路径访问的方法快速查找数据，减少磁盘IO操作。快速访问实际上就是用ROWID加速的。索引与索引所在的表无关，索引都是对象，索引和他所在的表都是独立的。视图是表衍生出来的，视图和表是有关系的。索引是由Oracle自身进行更新和维护的。当表插入数据的时候，会自动更新表中列对应的索引数据。不需要人工干预。索引的原理

索引的数据包含了索引列和Rowid。 rowid是每条记录的唯一ID，rowid和索引列对应起来，找到索引列的rowid，然后查找这一行的内容。

SELECT rowid, employee_id FROM employees; ROWID EMPLOYEE_ID ------------------ ----------- AAAR6BAADAAALWLAAA 100 AAAR6BAADAAALWLAAB 101 AAAR6BAADAAALWLAAC 102 索引的创建。隐式创建索引

定义了主键或者唯一键约束的时候，会自动创建一个唯一索引。

显示创建

用户可以手工通过创建索引的命令，在列上创建唯一或者非唯一索引，加速对数据的访问。默认创建的索引是B平衡树索引，采用的BTREE算法。MYSQL使用的B+TREE索引。

Oracle索引的类型唯一索引：既可以隐式创建，也可以显式创建。 B*TREE索引：默认值位图索引：数据仓库用的比较多复合索引：将多个列做成一个索引函数索引：当索引里需要函数转换的时候，可以预先创建一个函数索引。反向索引：为了避免热块争用(批量Insert的时候) 分区索引：分区表中使用的索引。本地前缀分区索引、本地无前缀分区索引、全局前缀分区索引创建索引的场合

不建议表中的索引过度，增加维护成本，影响查询速度。尤其是大量插入或更新的表，索引越多越危险。

列中包含的较大范围的值列中包含了大量的值。在where子句或者联结条件中频繁使用的一个列或者多个列。使用多个列的时候，创建符合索引或者给每个列创建索引。表很大，但一般检索的数据不到表的2%至4%时。按照列中数据的重复度去选择：重复度较低或者无重复，选择B*TREE索引或者唯一索引。数据仓库的数据有一定重复度的话，一般创建位图索引。加快分区表的查询速度的话，一般创建分区索引。为了避免热块争用，一般创建反向索引。不适合创建索引的场合表比较小，全盘扫描的速度快于索引参与查询的速度。查询中不常使用的列。语句检索的数据超过表的2%至4%时。表更新比较频繁，造成索引会跟着频繁更新。被索引的列将被函数引用，比如对这个列进行函数操作，函数会造成索引失败。如果列被函数操作的话，建议使用函数索引。创建索引的命令

CREATE （索引种类） INDEX 索引名[表名_列名_IDX] ON [模式SCHEMA.]表名(列名);

主键索引和唯一键索引

主键约束和唯一键约束是逻辑上控制的约束，而唯一键索引是具备物理结构的对象。唯一键索引对字段进行唯一性检查，禁止该字段出现重复值，但是允许null插入。

create table test_index (id number); CREATE UNIQUE INDEX TEST_INDEX_ID_IDX ON HR.TEST_INDEX(ID); INSERT INTO test_index VALUES (1); COMMIT; INSERT INTO test_index VALUES (1); ORA-00001: 违反唯一约束条件 (HR.TEST_INDEX_ID_IDX) 创建函数索引

正常来说，当查询的时候，对一个列的数据进行函数转换的时候，将不会走索引。如果做了函数索引，对应的函数出现时，仍将走索引查询。

-- 创建一个depts表 CREATE TABLE depts AS SELECT * FROM departments where 1=1; -- 创建一个大写字段名的函数索引 CREATE INDEX upper_depts_department_name ON depts(UPPER(department_name)); -- 创建一个大写字段名的函数索引 CREATE INDEX upper_depts_department_name ON depts(UPPER(department_name)); -- 以下查询将通过索引查找 SELECT * FROM depts WHERE UPPER(department_name) = 'SALES'; 创建复合索引

用户可以在多个列上建立索引，这种索引叫作符合索引。符合索引在数据库操作期间所需的开销更小，可以替代多个单列索引。

窄索引：索引列为1~2列的索引。宽索引：索引列超过两列的索引。

设计索引的一个重要原则就是能用窄索引不用宽索引，拥有更多的窄索引，能给优化程序更多的选择余地，有助于提高性能。

CREATE INDEX 索引名 ON 表名(列名1，列名2，列名3) 列名1 是前导列；列名2，列名3是非前导列。尽量把关系紧密的列创建为复合索引。

CREATE INDEX DEPTS_DEPTID_DEPTNAME_IDX ON depts(DEPARTMENT_ID, DEPARTMENT_NAME); -- Index DEPTS_DEPTID_DEPTNAME_IDX 已创建。确认索引

USER_INDEXES数据字典视图包含了索引的名称,表名和索引的唯一性。 USER_IND_COLUMNS 数据字典视图包含了索引名、表名和列名。

-- 创建实验表 CREATE TABLE EMP1 AS SELECT * FROM EMPLOYEES; -- 创建基于employee_id的索引 CREATE INDEX EMP1_EMPLOYEE_ID ON EMP1(EMPLOYEE_ID); -- SELECT IDX.INDEX_NAME, IDX.TABLE_NAME, IDXCOL.COLUMN_NAME, IDX.UNIQUENESS FROM USER_INDEXES IDX INNER JOIN USER_IND_COLUMNS IDXCOL ON IDX.INDEX_NAME = IDXCOL.INDEX_NAME AND IDX.TABLE_NAME = IDXCOL.TABLE_NAME WHERE IDX.TABLE_NAME = 'EMP1'; INDEX_NAME TABLE_NAME COLUMN_NAME UNIQUENESS -------------------- -------------------- -------------------- -------------------- EMP1_EMPLOYEE_ID EMP1 EMPLOYEE_ID NONUNIQUE 删除索引

DROP INDEX 索引名字 ;

SELECT INDEX_NAME, TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'DEPTS'; -- UPPER_DEPTS_DEPARTMENT_NAME DEPTS DROP INDEX UPPER_DEPTS_DEPARTMENT_NAME; -- Index UPPER_DEPTS_DEPARTMENT_NAME已删除。

删除表的时候，索引也同样被删除。

DROP TABLE 表名 -> 会删除索引。

【本文地址】

公司简介

联系我们