文章目录
备注：本文中使用到的sql为HR用户提供的数据和表。HR用户介绍查看以下链接。
https://blog.csdn.net/weixin_45842494/article/details/122528264
数据库中的序列、索引和同义词
1. 序列
1.1 什么是序列
序列是用户创建的数据库对象，序列会产生唯一的整数。序列的一个典型的用途是创建一个主键的值，它对于每一行必须是唯一的。序列由一个Oracle内部程序产生并增加或减少。
序列是一个节省时间的对象,因为它可以减少应用程序中产生序列程序的代码量。序列号独立于表被存储和产生，因此，相同的序列可以被多个表使用。
1.2 创建序列
1.2.1 通过DDL语句创建序列
在语法中:
- sequence是序列发生器的名字
- INCREMENT BY n指定序列号之间的间隔，在这儿n是一个整数(如果该子句被省略，序列增量为1)
- START WITH n指定要产生的第一个序列数 (如果该子句被省略，序列从1开始)
- MAXVALUE n指定序列能产生的最大值
- NOMAXVALUE对于升序序列指定10^27 为最大值，对于降序序列指定-1为最大值(这是默认选项)
- MINVALUE n指定最小序列值
- NOMINVALUE对于升序序列指定1为最小值，对于降序序列指定-(10^26)为最小值(这是默认选项)
- CYCLE | NOCYCLE指定序列在达到它的最大或最小值之后，是否继续产生(NOCYCLE是默认选项)
- CACHE n|NOCACHE指定Oracle 服务器预先分配多少值，并且保持在内存中(默认情况下，Oracle 服务器缓冲20个值)
示例：
创建一个序列名为：dept_seq，增长间隔为10，从120开始，最大值为9999，不缓存。不循环使用。
create sequence dept_seq increment by 10 start with 120 maxvalue 9999 nocache nocycle;
注意：属性之间不需要逗号（，）分隔，直接写。
1.2.2 使用工具创建序列
1.3 使用序列
1.3.1 查询序列
序列信息被存放在user_sequences数据字典表中。
select s.SEQUENCE_NAME,s.INCREMENT_BY,s.MAX_VALUE,s.MIN_VALUE,s.LAST_NUMBER from user_sequences s;
返回：
SEQUENCE_NAME INCREMENT_BY MAX_VALUE MIN_VALUE LAST_NUMBER
------------------------------ ------------ ---------- ---------- -----------
DEPARTMENTS_SEQ 10 9990 1 280
DEPT_SEQ 10 9999 1 120
DEPT_SEQ2 10 9999 1 120
EMPLOYEES_SEQ 1 1E28 1 207
LOCATIONS_SEQ 100 9900 1 3300
说明：这里LAST_NUMBER显示的是下一次使用的时候该序列的值，对于一个新创建的序列，并没有使用过，所以下一次使用的时候就是它的初始值，这里是120。
1.3.2 使用序列
NEXTVAL和CURRVAL伪列
- NEXTVAL返回下一个可用的序列值，它每次返回一个唯一的被引用值， 即使对于不同的用户也是如此
- CURRVAL获得当前的序列值
- 在CURRVAL获得一个值以前，NEXTVAL对该序列必须发布
在实际的开发中一般使用工具创建，只给定名称即可，其余使用默认值。
说明：对于一个新创建的序列，它的值并没有被NEXTVAL所发布，使用CURRVAL时会报错。
-- 先进行发布
select seq_test.nextval from dual;
返回：
NEXTVAL
----------
1
-- 在取当前值
select seq_test.currval from dual;
CURRVAL
----------
1
说明：我们在使用序列当主键的时候调用的是nextval，因为currval返回的永远是当前值，而主键要求是唯一的。
示例：
在location ID 2500中插入一个新部门名称Support。
insert into departments(DEPARTMENT_ID,DEPARTMENT_NAME,location_id) values (dept_seq.nextval,'Support',2500);
说明：这里DEPARTMENT_ID是主键，我们通过dept_seq.nextval获取序列的值作为主键。
1.4 修改序列
1.4.1 修改序列
1.4.2 修改序列的原则
- 必须是被修改序列的所有者，或者有ALIER权限。
- 用ANER SEQUENCE语句，只有以后的序列数会受影响。
- 用ALIER SEQUENCE语句, START WITH选项不能被改变。为了以不同的数重新开始一个序列，该序列必须被删除和重新创建。
示例
将dept_seq 序列中的增长量修改20,最大值修改为99999
alter sequence dept_seq increment by 20 maxvalue 999999 nocache nocycle;
1.5 删除序列
示例：
删除dept_seq序列。
drop sequence dept_seq;
2. 索引
2.1 什么是索引
在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供对表中行的直接和快速访问,它的目的是用已索引的路径快速定位数据以减少磁盘IO。索引由Oracle 服务器自动使用和维护，索引逻辑地和物理地独立于他们索引的表,这意味者索引可以在任何时候被创建或删除，并且不影响基表或其它的索引。当删除表时，相应的索引也被删除。
2.2 使用索引
过多也件坏事
在表上建立更多的索引并不意味者更快地查询,在带索引的表上被提交的每个DML操 作意味者索引必须更新;与表联系的索引越多，对Oracle 数据库的影响越大，Oracle 数据库在每次DML操作之后必须更新所有的索引。
什么时候创建索引
- 一个列包含一个大范围的值
- 一个列包含很多的空值
- 一个或多个列经常同时在-一个WHERE子句中或-一个连接条件中被使用
- 表很大，并且经常的查询期望取回少于百分之2到4的行。
2.3 操作索引
2.3.1 非唯一性索引的类型
Oracle的非唯一性索引：单行索引，复合索引（组合索引），函数索引。
2.3.2 创建索引的语法
创建单行索引：
示例：
为employees表中的last_name创建一个单行索引并命名为emp_index。
create index emp_index on employees(last_name);
创建复合索引：
示例：
为depatments表创建一个包括manager_id与location_id的复合索引，并命名为dept_man_loc。
create index dept_man_loc on departments(manager_id,location_id);
说明：对于复合索引，当我们使用的时候，查询条件的顺序要和我们创建索引时的顺序一致，索引才会生效。
创建函数索引：
为departments表中的department_name创建一个带有大写函数的索引dept_upper。
create index dept_upper on departments(upper(department_name));
说明：当我们创建了函数索引，那么在查询条件中也需要有函数才能生效。如下：
select * from departments d where upper(d.department_name) = 'payroll';
2.3.3 查询索引
2.3.4 修改索引
删了重新建。
2.3.5 删除索引
示例：
删除索引emp_index。
drop index emp_index;
3. 同义词
同义词可以除去对象名必须带的方案限制，并提供给你一个可替换表名、视图名、序列名和存储过程名或其它对象名。该方法对具有特别长的对象的名字很有用。
3.1 创建同义词
示例：
给employees表起一个别名em。
create synonym em for employees;
说明：当我们给数据库对象起了别名之后，就可以使用别名对其进行查询。
3.2 删除同义词
示例：
删除同义词em。
drop synonym em;