大型数据库技术（四）oracle模式对象下

写在开头，一些必要的理论知识：

聊聊SQL语句中 DDL 、DML 、DQL 、DCL 分别是什么_ddl dml dcl dql的区别-CSDN博客

一、索引对象

为什么要有索引：

在关系型数据库中，⽤户查找数据与⾏的物理位置⽆关紧要。为了能够找到数据，表中的每⼀⾏均⽤⼀个ROWID 来标识， ROWID 能够标识数据库中某⼀⾏的具体位置。当Oracle 数据库中存储海量的记录时，就意味着有⼤量的ROWID 标识， 这样Oracle如何能够快速找到指定的ROWID呢？这时就需要使⽤索引对象，它可以提供服务器在表中快速查找记录的功能。

1、索引概述

如果要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，⽽有了索引之后，只需要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID 快速找到表中对应的记录。例如，如果将表看做⼀本书，索引的作⽤则类似于书中的⽬录。在没有⽬录的情况下，要在书中查找指定的内容必须阅读全书，⽽有了⽬录之后，只需要通过⽬录就可以快速找到包含所需内容的⻚码（相当于ROWID ）。

建议：

将索引和对应的表分别放在不同硬盘的不同的表空间中能够提⾼查询的速度，因为Oracle 能够并⾏读取不同硬盘的数据，这样可以避免产⽣I/O 冲突。

限制索引个数。索引会降低DML的速度

不要在小表上建立索引

多表连接时应该在连接列上建立索引

分类：

按索引数据存储方式：B树索引、位图索引、反向键索引、基于函数的索引

按索引列的唯一性：唯一索引、非唯一索引

按索引列的个数：单列索引、复合索引

2、创建索引

使用create index语句创建索引

①创建B树索引（Balanced tree index）

B 树索引是 Oracle 数据库最常⽤的索引类型（也是默认的），它是以B 树结构组织并存放索引数据的。默认情况下， B 树索引中的数据是以升序⽅式排列的。如果表包含的数据⾮常多，并且经常在 WHERE⼦句中引⽤某列或某⼏个列，则应该基于该列或这⼏个列建⽴B 树索引。 B 树索引由根块、分⽀块和叶块组成，其中主要数据都集中在叶⼦节点，

［√］根块：索引顶级块，它包含指向下⼀级节点的信息。

［√］分⽀块：它包含指向下⼀级节点（分⽀块或叶块）的信息。

［√］叶块：通常也称为叶⼦，它包含索引⼊⼝数据，索引⼊⼝包含索引列的值和记录⾏对应的物理地址ROWID

在 B 树索引中⽆论⽤户要搜索哪个分⽀的叶块，都可以保证所经过的索引层次是相同的。Oracle 采⽤这种⽅式的索引，可以确保⽆论索引条⽬位于何处，都只需要花费相同的I/O 即可获取它，这就是为什么被称为B 树索引（ B 是英⽂ BALANCED 的缩写）。

语法：

create index @indexname on @tablename(@columnname) 
pctfree n tablespace @tablespacename;

如果在where子句中要经常应用某列或某几列，应该基于这些列建立b树索引

例:

在SCOTT模式下，为emp表的deptno列创建索引，
代码及运⾏结果如下。

SQL> create index emp_deptno_index on emp(deptno)
 2 pctfree 25
 3 tablespace users;

②建立位图索引

当列的基数很低时，为其建⽴ B 树索引显然不合适。 “ 基数低 ” 表⽰在索引列中，所有取值的数量⽐表中⾏的数量少。如“ 性别 ” 列只有两个取值；再⽐如某个拥有10000 ⾏的表，它的⼀个列包含 100 个不同的取值，则该列仍然满⾜低基数的要求，因为该列与⾏数的⽐例为1%。 Oracle 推荐当⼀个的基数⼩于 1% 时，这些列不再适合建⽴ B树索引，⽽适⽤于位图索引。但在表中低基数的列上建⽴位图索引时，系统将对表进⾏⼀次全⾯扫描，为遇到的各个取值构建“ 图表 ”.

– 如果某一列上的数据属于 低基数 （ Low - Cardinality ）列的时

候可以利用位图索引来提升查询的性能。

– 为索引列的每个取值创建一个位图（ bit 位），对表中的每行

使用 1 位取值为 0 或 1 ）来表示该行是否包含该位图的索引列的

取值。

例：

在 HR 模式下，在 employees 的 salary 列创建位图索引

 create bitmap index emp_salary_bmp on employees(salary)
 tablespace users;

初始化参数 CREATE_BITMAP_AREA_SIZE ⽤于指定建⽴位图索引时分配的位图区⼤⼩，默认值为8MB ，该参数值越⼤，建⽴位图索引的速度越快。为了加快创建位图索引的速度，应将该参数设置为更⼤的值。因为该参数是静态参数，所以修改后必须重新启动数据库才能⽣效。

alter system set create_bitmap_area_size =8388608 scope =spfile;

更详细阅读：

索引：位图索引理解-CSDN博客

③建立反向键索引

在Oracle中，系统会⾃动为表的主键列建⽴索引，这个默认是普通的B树索引。

反向键索引是⼀种特殊类型的 B 树索引，在顺序递增列上建⽴索引时⾮常有⽤。反向键索引的⼯作原理⾮常简单，在存储结构⽅⾯它与常规的B 树索引相同。然⽽，如果⽤户使⽤序列在表中输⼊记录，则反向键索引⾸先指向每个列键值的字节，然后在反向后的新数据上进⾏索引。例如，如果⽤户输⼊的索引列为2011 ，则反向转换后为 1102； 9527 反向转换后为 7259 。需要注意，刚才提及的两个序列编 号是递增的，但是当进⾏反向键索引时却是⾮递增的。这意味如果将其添加到⼦叶节点，可能会在任意的⼦叶节点中进⾏。这样就使得新数据在值的范围上的分布通常⽐原来的有序数更均匀。

语句：

create index @indexname on @tablename(@columname) reverse
tablespace @tablespacename;

例子：

为表 emp 的 job 列创建了反向键索引

 create index emp_job_reverse on emp(job) reverse tablespace users;

将普通B树索引修改为反向键索引

alter index @indexname rebuild reverse; 

④基于函数的索引

⽤户在使⽤ Oracle 数据库时，最常遇到的问题之⼀就是它对字符⼤⼩写敏感。例如，在emp 表中存有职位（ job ）为 MANAGER 的记录，当⽤户使⽤⼩写搜索时，将⽆法找到该⾏记录，如图10.5 所⽰。 另外，如果⽤户不能确定输⼊数据的格式，甚⾄会产⽣⼀个严重的错

误。

对于上⾯出现的这种情况，可以通过使⽤ Oracle 字符串函数对其进⾏转换，然后再使⽤转换后的数据进⾏检查。

SQL> select empno,ename,sal from emp
 2 where job = upper('manager');

采⽤这种⽅法后，⽆论⽤户输⼊数据时所使⽤的字符的⼤⼩写如何组合，都可以使⽤该语句检索到数据。但是，使⽤这样的查询时，⽤户不是基于表中存储的记录进⾏搜索的。即如果搜索的值不存在于表中，那么它就⼀定也不会在索引中，所以即使在job 列上建⽴索引，Oracle也会被迫执⾏全表搜索，并为所遇到的各个⾏计算 UPPER 函数。

所以建立函数索引– 索引的取值不直接来自列，而来自包含有列的函数或表达式
例：

为表 emp 的 job 列创建函数索引

 create index emp_job_fun2 on emp(lower(job));

在创建该函数索引之后，如果在查询条件中包含相同的函数，则系统会利⽤它来提⾼查询的执⾏效率。

3、修改索引

为表建⽴索引后，随着对表不断进⾏更新、插⼊和删除操作，索 引中会产⽣越来越多的存储碎⽚，这将对索引的⼯作效率产⽣负⾯影响。这时可以采取两种⽅式来清除碎⽚—— 重建索引或合并索引。

合并操作：

alter index emp_deptno_index 2 coalesce deallocate unused;

重建索引：

消除索引碎⽚的另⼀个⽅法是重建索引，重建索引可以使⽤ALTER INDEX...REBUILD语句。重建操作不仅可以消除存储碎⽚，还可以改变索引的全部存储参数设置，以及改变索引的存储表空间。 重建索引实际上是在指定的表空间中重新建⽴⼀个新的索引，然后再删除原来的索引。

 alter index emp_deptno_index rebuild;

在使⽤ ALTER INDEX...REBUILD 语句重建索引时，还可以在其中使⽤REVERSE ⼦句将⼀个反向键索引更改为普通索引，反之可以将⼀个普通的B 树索引转换为反向键索引。另外，也可以使⽤

TABLESPACE ⼦句指定重建索引的存放位置。

alter index emp_deptno_index rebuild tablespace example;
alter index emp_deptno_index rebuild reverse;

4、删除索引

drop index @indexname

5、显示索引信息

①显示表的所有索引

索引是⽤于加速数据存储的数据库对象。通过查询数据字典视图 DBA_INDEXES，可以显⽰数据库的所有索引；通过查询数据字典视图ALL_INDEXES ，可以显⽰当前⽤户可访问的所有索引；查询数据字典视图USER_INDEXES ，可以显⽰当前⽤户的索引信息

 select index_name,index_type from dba_indexes where owner = 'HR';

②显示索引列

创建索引时，需要提供相应的表列。通过查询数据字典视图DBA_IND_COLUMNS，可以显⽰所有索引的表列信息；通过查询数据字典视图ALL_IND_COLUMNS ，可以显⽰当前⽤户可访问的所有 索引的表列信息；通过查询数据字典视图USER_IND_COLUMNS ，

例：

查 询 SCOTT ⽤ 户 的EMP_DEPTNO_INDEX索引的列信息

select column_name ,column_length  from user_ind_columns 
where index_name = 'EMP_DEPTNO_INDEX';

③显示索引段位置及其大小

（users_segment?)

建⽴索引时， Oracle 会为索引分配相应的索引字段，索引数据被 存放在索引段中，并且段名与索引名完全相同。通过查询数据字典视 图DBA_IND_EXPRESSIONS ，可以显⽰数据库所有函数索引所对应 的函数或表达式；通过查询数据字典USER_IND_EXPRESSIONS ，可以显⽰当前⽤户函数索引所对应的函数或表达式。

例：

查 询 索 引 段EMP_DEPTNO_INDEX的位置、段类型和段⼤⼩

select tablespace_name,segment_type,bytes from user_segments
 where segment_name = 'EMP_DEPTNO_INDEX';

④显示函数索引

建⽴函数索引时， Oracle 会将函数索引的信息存放到数据字典中。通过查询数据字典视图DBA_ IND_EEXPRESSIONS ，可以显⽰数据库所有函数索引所对应的函数或表达式；通过查询数据字典 USER_IND_EXPRESSIONS，可以显⽰当前⽤户函数索引所对应的函数或表达式。

例：

查询函数索引 EMP_JOB_FUN 的表达式信息

select column_expressionfrom user_ind_expressions
 where index_name = 'EMP_JOB_FUN';

二、视图

视图建⽴在关系表上，也可以在其他视图上，或者同时建⽴在两者之上。视图看上去⾮常像数据库中的表，甚⾄可以在视图中进⾏INSERT、 UPDATE 和 DELETE 操作。通过视图修改数据时，实际上就 是在修改基本表中的数据。与之相对应，改变基本表中的数据也会反映到由该表组成的视图中。

1、创建视图

语法：

create [or replace] view <view_name> [alias[,alias]...) ]
as <subquery>
[with check option] [constraint constraint_name]
[with read only]

［√］alias：⽤于指定视图列的别名。
［√］subquery：⽤于指定视图对应的⼦查询语句。
［√］with check option：该⼦句⽤于指定在视图上定义的
CHECK约束。
［√］with read only：该⼦句⽤于定义只读视图。

①简单视图

简单视图是指基于单个表建⽴的，不包含任何函数、表达式和分组数据的视图，下⾯来看⼀个例⼦。

例：

在 SCOTT 模式下，创建⼀个查询部门编号为 20 的视图

create or replace view emp_view as select empno,ename,job,deptno
 from emp where deptno = 20;

对于简单视图⽽⾔，不仅可以执⾏ SELECT 操作，⽽且还可以执

⾏ INSERT 、 UPDATE 、 DELETE 等操作

例：

insert into emp_view values(9527,'东⽅'，'MANAGER',20);

update emp_view set ename = '⻄⽅' where empno = 9527;

delete from emp_view where empno=9527;

②建立只读视图

create or replace view emp_view_readonly as select * from dept
where deptno != 88 with read only

③复杂视图

复杂视图是指包含函数、表达式或分组数据的视图，使⽤复杂视图的主要⽬的是为了简化查询操作。需要注意的是，当视图⼦查询包含函数或表达式时，必须为其定义列别名。

例：

创建⼀个视图，要求能够查询每个部门的⼯资情况

 create or replace view emp_view_complex 
as select deptno 部门编号,max(sal) 最⾼⼯资,min(sal) 最低⼯资,avg(sal) 平均⼯资 
from emp group by deptno;

select * from emp_view_complex order by 部门编号;

④连接视图

连接视图是指基于多个表所建⽴的视图，使⽤连接视图的主要⽬的是为了简化连接查询。需要注意的是，建⽴连接视图时，必须使⽤WHERE⼦句中指定有效的连接条件，否则结果就是毫⽆意义的卡迪尔积。

例：

创建⼀个 dept 表与 emp 表相互关联的视图，并要求该视图只能查询部门编号为20 的记录信息

create or replace view emp_view_union as
  select d.dname,d.loc,e.empno,e.ename
  from emp e,dept d
  where e.deptno = d.deptno and d.deptno = 20;


select * from emp_view_union;

2、管理视图

在创建视图后，⽤户还可以对视图进⾏管理，主要包括：查看视图定义、修改视图定义、重新编译视图和删除视图。

①查看视图定义

desc user_views;

例：

通过数据字典 USER_VIEWS 查看视图 user_views 的定义

select text from user_views where view_name = upper('emp_view_union');

②修改视图定义

建⽴视图后，如果要改变视图所对应的⼦查询语句，则可以执⾏CREATE OR REPLACE VIEW语句，来看下⾯的例⼦。

例：

修改视图 emp_view_union ，使该视图实现查询部门编号为30 的功能（原查询信息是部门编号为 20 的记录）---跟创建视图差不多

SQL> create or replace view emp_view_union as
 2 select d.dname,d.loc,e.empno,e.ename
 3 from emp e,dept d
 4 where e.deptno = d.deptno and d.deptno = 30;

说明
在上⾯代码中，起到⾄关重要作⽤的关键字是replace，它表
⽰使⽤新的视图定义替换掉旧的视图定义。

③重新编译视图

视图被创建后，如果⽤户修改了视图所依赖的基本表定义，则该视图会被标记为⽆效状态。当⽤户访问视图时，Oracle 会⾃动重新编译视图。除此之外，⽤户也可以⽤ALTER VIEW 语句⼿动编译视图

alter view @view_name compile;

④删除视图

drop view @view_name