[Oracle数据库学习]十二、其他数据库对象

D7

常见的数据库对象

对象	描述
表	基本的数据存储集合，由行和列组成
视图	从表中抽出的逻辑上相关的数据集合
序列	提供有规律的数值
索引	提高查询的效率
同义词	给对象起别名

 

序列

什么是序列

序列:
1）自动提供唯一的数值
2）共享对象
3）主要用于提供主键值
4）代替应用代码
5）将序列值装入内存可以提高访问效率

 

创建序列CREATE SEQUENCE

CREATE SEQUENCE sequence
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE n| NOMAXVALUE}]
    [{MINVALUE n| NOMINVALUE}]
    [{CYCLE | NOCYCLE}]
    [{CACHE n| NOCACHE}];

创建序列DEPT_DEPTID_SEQ为表DEPARTMENTS提供主键，不使用CYCLE选项

CREATE SEQUENCE dept_deptid_seq
  INCREMENT BY 10
  START WITH 120
  MAXVALUE 9999
  NOCACHE
  NOCYCLE;

 

查询序列

查询数据字典视图USER_SEQUENCES获取序列定义信息。

如果指定NOCACHE 选项，则列LAST_NUMBER显示序列中下一个有效的值。

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;

SEQUENCE_NAME	MIN_VALUE	MAX_VALUE	INCREMENT_BY	LAST_NUMBER
DEPT_DEPTID_SEQ	1	9999	10	120

 

NEXTVAL和CURRVAL伪列

1）NEXTVAL返回序列中下一个有效的值，任何用户都可以引用；
2）CURRVAL中存放序列的当前值；
3）NEXTVAL应在CURRVAL之前指定，二者应同时有效。

 

使用序列

1）使用序列生成主键department_id值。

INSERT INTO departments(department_id, department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);

查询：

SELECT * FROM departments;

DEPARTMENT_ID	DEPARTMENT_NAME	LOCATION_ID
120	Support	2500

 

2）查看序列的当前值

SELECT dept_deptid_seq.CURRVAL
FROM dual;

CURRVAL

130

注：序列定义为120开始，间隔10。

 

将序列值装入内存可提高访问效率。

序列在下列情况下出现裂缝：
1）回滚
2）系统异常
3）多个表同时使用同一序列

如果不将序列的值装入内存(NOCACHE)，可使用表USER_SEQUENCES查看序列当前的有效值。

 

修改序列ALTER SEQUENCE

修改序列的增量、最大值、最小值、循环选项或是否装入内存。

ALTER SEQUENCE dept_deptid_seq
    INCREMENT BY 20
    MAXVALUE 999999
    NOCACHE
    NOCYCLE;

 

修改序列时需要注意：

1）用户必须是序列的拥有者，或对序列有ALTER权限；
2）只有将来的序列值会被改变；
3）改变序列的初始值：只能通过删除序列之后重建序列的方法实现；
4）其它的一些限制。

 

删除序列DROP SEQUENCE

使用DROP SEQUENCE语句删除序列。
删除之后，序列不能再次被引用。

DROP SEQUENCE dept_deptid_seq;

 

索引

什么是索引

1）一种数据库对象
2）通过指针加速Oracle服务器的查询速度
3）通过快速定位数据的方法，减少磁盘I/O
4）索引与表相互独立
5）Oracle服务器自动使用和维护索引

 

创建索引

自动创建：在定义PRIMARY KEY或UNIQUE约束后系统自动在相应的列上创建唯一性索引；
手动创建：用户可以在其它列上创建非唯一的索引，以加速查询。

 

在一个或多个列上创建索引

CREATE INDEX index
ON table(column[, column]...);

示例：

CREATE INDEX emp_manager_id_idx
ON employees(manager_id);

 

错误示例：

CREATE INDEX emp_employee_name_idx
ON employees(employee_name);

结果：ORA-01408: such column list already indexed

 

什么时候创建索引

以下情况可以创建索引:
1）列中数据值分布范围很广
2）列中包含大量空值
3）列经常在WHERE子句或连接条件中出现
4）表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%

 

什么时候不要创建索引

下列情况不要创建索引:
1）表很小
2）列不经常作为连接条件或出现在WHERE子句中
3）查询的数据大于2%到4%
4）表经常更新
5）加索引的列包含在表达式中

 

查询索引

可以使用数据字典视图USER_INDEXES和USER_IND_COLUMNS查看索引的信息。

SELECT ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';

INDEX_NAME	COLUMN_NAME	COL_POS	UNIQUENESS
EMP_EMPLOYEE_NAME_UK	EMPLOYEE_NAME	1	UNIQUE
EMP_MANAGER_ID_IDX	MANAGER_ID	1	NONUNIQUE

 

基于函数的索引

基于函数的索引是一个基于表达式的索引。
索引表达式：列、常量、SQL 函数和用户自定义的函数。

CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));

查询：（体现查询性能）

SELECT *
FROM departments
WHERE UPPER(department_name) = 'TRADE';

DEPARTMENT_ID	DEPARTMENT_NAME	LOCATION_ID
2	Trade	2

 

删除索引

使用DROP INDEX命令删除索引

语法：

DROP INDEX index_name;

示例：

DROP INDEX upper_dept_name_idx;

 

规则：

只有索引的拥有者或拥有DROP ANY INDEX权限的用户才可以删除索引。

 

同义词

使用同义词访问相同的对象:
1）方便访问其它用户的对象
2）缩短对象名字的长度

 

创建同义词

CREATE [PUBLIC] SYNONYM synonym
FOR object;

为视图创建同义词：

CREATE SYNONYM d_sum
FOR dept_sum_vu;

 

删除同义词

DROP SYNONYM d_sum;

 

总结：

本节介绍序列、索引、同义词：

1）序列：为什么使用序列；创建（CREATE SEQUENCE）、更新（ALTER SEQUENCE）、删除（DROP SEQUENCE）序列；查询序列定义和当前值（USER_SEQUENCES）等；

2）索引：为什么使用索引；创建（CREATE INDEX）、删除（DROP INDEX）索引；查询索引（USER_INDEXES和USER_IND_COLUMNS）；

3）同义词：创建（CREATE SYNONYM）、删除（DROP SYNONYM）同义词。