关于oracle自动编号

序列可以保证多个用户对同一张表进行操作时生成唯一的整数,利用序列可以自动生成主关键字,序列只存在于数据字典中.

CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE |NOCYCLE}]
[{CACHE n|NOCACHE}];
INCREMENT BY--指定步长
START WITH--指定初始值
MAXVALUE--定义序列生成的最大编号.默认的MAXVALUE就是NOMAXVALUE,对于递增序列为10^27,对于递减序列为-1
MINVALUE--定义序列的最小编号,默认的MINVALUE为NOMINVALUE,对于递增序列为1,递减序列为-10^26.
CYCLE--配置序列在达到界限值时重复编号
NOCYCLE--达到界限值时不重复编号,这是默认值,当你试图生成MAXVALUE+1时将返回异常.
CACHE--定义在内存中保留的序列编号块的大小,默认值为20.
NOCACHE--强制数据词典对于生成的每个序列编号进行更新,保证在生成的编号中没有空缺,但这样会降低性能.

生成一个序列
CREATE SEQUENCE dept_deptid_seq
INCREAMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
//如果是用来生成主键值的话,不要用CYCLE选项,而且命名序列时最好能体现它的潜在用途以便于理解.

确认序列
SELECT sequence_name,min_value,max_value,increament_by,last_number
FROM user_sequences;
//如果你指定了NOCACHE选项,那么LAST_NUMBER列将显示下一可用的序列号.

使用NEXTVAL可以访问序列中的下一个编号,但问题常常出现在会话初始序列之前查询其当前序列号CURRVAL
CREATE SEQUENCE emp_seq
NOMAXVALUE
NOCYCLE;
然后查询
SELECT emp_seq.currval
FROM dual;
将返回错误,问题就在于你视图引用CURRVAL之前,在你的会话中并没有使用NEXTVAL先初始化此序列.
SELECT emp_seq.nextval
FROM dual;
这样再查询CURRVAL就不会出错了.

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

对序列进行缓冲存储可以提高性能,因为这样就不必对每个生成的编号都更新数据字典表,只需要对每一组编号进行更新即可.这样,在我们查询NEXTVAL时就直接从缓冲中提取,速度将快很多,但是进行序列缓冲带来的负面影响就是当数据库被回滚时,比如说系统崩溃,手动ROLLBACK数据时,在缓冲中存储的序列值将会丢失,这也就是为什么会出现空缺(GAPS),如果生成序列时指定的是NOCACHE,那么可以在USER_SEQUENCES表里查询下一个可用的序列号值,这个查询并不会产生增加序列值的动作.

修改序列
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
规则:
>必须为序列的所有者或者拥有ALTER特权
>修改对于以后的序列号生效
>序列必须是被删除然后重新生成(使所有相关的对象失效,并且失去相应的关联)
>修改时还要满足些其他的验证条件,比如说新的MAXVALUE不可以比现在的序列号低

删除序列
DROP SEQUENCE dept_deptid_seq;
>必须要时序列的所有者或者有DROP ANY SEQUENCE的权限

 

举个例子：

要实现t1的某个键值（这里权且用id）值是自增长。

实现方法一

1,创建一个序列 SEQ_XT_T1

2,创建触发器

create or replace
TRIGGER TRI_XT_T1
 BEFORE INSERT ON XT_SEQ 
 FOR EACH ROW
BEGIN
  IF INSERTING THEN

     SELECT SEQ_XT_T1.NEXTVAL INTO :NEW.ID FROM DUAL;

  END IF;

END;

 

PS:自增长的字段不一定就必须是主键。
实现方法二

1.创建一个序列 SEQ_XT_T1

不采用触发器，而是在向表里面插入数据的时候插入这个字段

eg:

INSERT INTO XT_T1(ID,..) VALUES(SEQ_XT_T1.NEXTVAL,.....);

 

留下的问题：方法一和方法二的比较

索引
索引是通过获取特定的行信息而与默认的全表扫描相比大大提高系统性能的数据结构.可是显式的手动建立,也可一由ORACLE自动生成,它们是与之索引的表相独立的,就是说,可以在任何时候建立或者删除索引而对基表或者其他索引无任何影响(当你删除表时,相关的索引将会被删除).
有两种索引
>唯一索引--当你定义了一列含有主键或者唯一键约束时将自动生成一个唯一索引(可以手动建立,但是推荐由ORACLE自动建立)
>非唯一索引--当你手动为一个查询中的连接建立一个外键索引来加速查询速度时

建立索引
CREATE INDEX index
ON table (column[,column]...);
e.g.
CREATE INDEX emp_last_name_idx
ON employees(last_name);

何时使用索引
>当一个列包含的数值范围较大时
>当一列包含大量空值时
>一个或者多个列经常一起在WHERE子句或者JOIN子句中使用时
>表非常大,但是大多数的查询只要求检索少于2-4个百分点的行记录

何时不该使用索引
>表很小时
>列并不经常作为查询中的条件使用时
>大多数查询都检索多于4个百分点的行记录
>表经常被更新
>索引列被作为表达式的一部分引用

索引虽然可以很大程度提高检索性能,但是越多的索引,意味着在DML操作之后ORACLE就将花越多的功夫去更新索引.所以,一定要适时使用,

确认索引
查询数据字典视图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.inde_name=ix.index_name
AND ic.table_name='EMPLOYEES';

基于函数的索引
CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
//但是如果想保证ORACLE使用索引而不是全表扫描就必须保证函数值非空,就是需要加个WHERE子句指定非空如
SELECT *
FROM employees
WHERE UPPER(last_name_ IS NOT NULL
ORDER BY UPPER(last_name);
如果没有WHERE子句,则将会进行全表扫描非使用索引了.

删除索引
DROP INDEX index;
//当你删除一个表时,索引和约束将会自动删除,但是视图和序列将会保留.

同义词
同义词经常用来通过为一个本地或者远程对象给定一个通用的名字来简化SQL.同义词可以指向一个表,视图,序列,过程,函数或者本地数据库中的包,或者通过一个数据库连接指向另一个数据库中的对象.公共同义词可供所有用户使用,而专用同义词则只能供其所有者或者获得了相关授权的帐户所有者使用.
比如说SCOTT拥有表EMP,所有用户都使用自己的用户名登陆数据库,并且必须引用该表,即SCOTT.EMP,如果我们为其生成一个同义词EMP,每个对该表具有相关特权的人都可以简单地在自己的SQL或者PL/SQL语句中以EMP的形式来引用它,不需要再指出所有者了.

CREATE [PUBLIC] SYNONYM synonym
FOR object;

e.g.
CREATE SYNONYM d_sum
FOR dept_sum_vu;

DROP SYNONYM d_sum;

CREATE PUBLIC SYNONYM dept
FOR alice.departments;

DROP PUBLIC SYNONYM dept;