序列
-
Oracle序列Sequence是用来生成连续的整数数据的对象,它经常用来作为业务中无规则的主键。Oracle序列可以是升序列也可以是降序列
-
mysql中有自增
Oracle中用序列模拟自增
-
序列(本质就是数组)
[1、2、3、4…] 默认数组
创建序列的语法
-
oracle序列sequence是用来生成连续的
-
语法结构:
create sequence sequence_name ;
[maxvalue num | nomaxvalue] 是否有最大值(升序最大值10^27)
[minvalue num | nominvalue] 是否有最小值
[start with increment] 起始值 默认1
[increment by increment] 步长 默认1
[cycle | nocycle] 循环
[cache num | nocache] 简单否
sequence_name.nextval 下一个值
currval 当前值
-
语法解析
(1)MAXVALUE/MINVALUE:指定的是序列的最大值和最小值。
(2)NOMAXVALUE/NOMINVALUE:不指定序列的最大值和最小值,使用系统的默认选项,升序的最大值:10的27 次方,降序是-1。升序最小值:1,降序的最小值:-10的26次方
(3)START WITH:指定从某一个整数开始,升序默认是1,降序默认是-1。
(4)CYCLE | NOCYCLE:表示序列达到最大值或者最小值的时候,是否重新开始。CYCLE:重新开始,NOCYCLE:不重新开始。
(5)CACHE:使用 CACHE 选项时,该序列会根据序列规则预生成一组序列号。保留在内存中,当使用下一个序列号时,可以更快的响应。当内存中的序列号用完时,系统再生成一组新的序列号,并保存在缓存中,这样可以提高生成序列号的效率 。
(6)NOCACHE:不预先在内存中生成序列号。
创建序列实例
-
创建序列
create sequence myseq;
-
查看序列
select * from user_sequence ;
-
序列的属性
对象.nextval :指向下一个值
对象.currval :当前值(指向)
注:第一次先用next才能找到1
单独使用序列没有意义,需要结合表来使用,序列创建出来就是给表使用
序列和表
-
找出一个表
select * from emp ;
-
查询该表+序列
select myseq.nextval, empno,ename from emp ;
-
再次查询该表
序列号会接着上一次的值继续使用
select myseq.nextval, empno,ename from emp ;
-
使用该序列在查询其他表,序列号则也会接着上一次的值继续使用
select myseq.nextval,deptno,dname from dept ;
-
创建一新表
create table person( pid number(10) primary key , pname varchar2(20) );
-
插入数据,同时使用序列作为主键
insert into person values(myseq.nextval,‘zhangsan’);
insert into person values(myseq.nextval,‘lisi’);
insert into person values(myseq.nextval,‘wangwu’);
-
查询该表
select *from person ;
创建一个自定义序列号
-
创建序列
create sequence myseq3
increment by 2
start with 100
; -
应用该序列到emp表
-
再创建一个序列
create sequence myseq4 increment by 2 start with 1 cycle minvalue 1 maxvalue 8 cache 3 ;
-
补充讲解cach3
线程1:1 2
线程2:3 8
线程3:4 5 6 7 -
使用该序列查询emp表
-
再次查询
-
注意:不能使用循环序列作为主键!!因为违反了主键的唯一约束!!
裂缝问题
一.
-
裂缝:序列不连贯,使用序列会产生裂缝
-
序列在下列情况下出现裂缝:
a. 回滚
b.系统异常
c.多个表使用同一序列
二.
-
查询person表
select * from person ;
-
插入数据
-
提交commit
-
查询一下数据
-
再插入数据
-
此时不提交,回滚rollback
-
第三次插入数据
-
提交commit
-
查询表
此时的序列45与48之间就有了裂缝
修改序列
-
alter sequence 序列名
[INCREMENT BY n] // 注意当前值是多少,
[START WITH n] //开始的值不能修改
[{CACHE n | NOCACHE}];//分配并存入到内存中
-
语法:
alter SEQUENCE sequence //修改序列名称 [INCREMENT BY n]//递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1 [START WITH n]//开始的值,递增默认是minvalue 递减是maxvalue [{MAXVALUE n | NOMAXVALUE}] //最大值 [{MINVALUE n | NOMINVALUE}] //最小值 [{CYCLE | NOCYCLE}] //循环/不循环 [{CACHE n | NOCACHE}];//分配并存入到内存中 案例: alter sequence myseq1 increment by 1 start with 500//如果一开始指定了启动序列,那么无法更改 ;
测试一.
-
查看当前用户的序列
-
更改已有序列
注意:开始的值不能修改!!
-
修改步长
测试二
-
查看person表
-
修改步长myseq为2
-
插入数据(插两条一样的数据)
-
再查询person表,之后插入的数据的步长已改
注:序列的修改只对修改后的操作有效s
-
注意:
(1)必须是序列的拥有者或对序列有 ALTER 权限
(2) 只有将来的序列值会被改变
(3)改变序列的初始值只能通过删除序列之后重建序列的方法实现
删除序列
- drop sequence 序列名 ;
索引
-
Oracle索引(index)最大的作用是用来优化数据库查询的效率,提升数据库的查询性能。就好比书的目录一样,可以通过目录来直接定位所需内容存在的页数,提高检索效率。
-
语法
create[unique]|[bitmap] index index_name --UNIQUE表示唯一索引、BITMAP位图索引 on table_name(column1,column2...|[express])--express表示函数索引 [tablespace tab_name] --tablespace表示索引存储的表空间 [pctfree n1] --索引块的空闲空间n1 [storage --存储块的空间 ( initial 64K --初始64k next 1M minextents 1 maxextents unlimited )];
-
语法解析
(1)UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引,BITMAP表示位图索引。
(2)index_name:指定索引名。
(3)tabl_name:指定要为哪个表创建索引
(4)column_name:指定要对哪个列创建索引。我们也可以对多列创建索引,这种索引称为组合索引。也可以是函数表达式,这种就是函数索引。
-
类似于书的目录
-
例如:emp表中查询部门为10的员工
(1)为部门编号deptno设置索引
(2)在通过索引查到10的部门,此时查到的都是编号
(3)通过这些编号在找到对应的rowid
(4)在通过rowid找对应的
-
理论:
索引原理 B树索引
## 创建索引
-
创建一级索引
create indexName on table (columnName) ;
(1)根据emp表的deptno创建索引:
create index myindex on emp(empno) ;
此句会报错,因为empno是主键,默认有索引
(2)正确写法:
create index myindex on emp(deptno) ;
-
创建二级索引(两个列)
先根据部门编号找,如果一样,再根据工资找
create index myindex2 on emp(deptno,sal) ;
-
索引的建立依据,哪些适合建立索引?
(1)需要经常where中使用:比如根据部门筛选(满足条件的行)
(2)比较集中的列:deptno(满足条件的列)
(3)主键列:empno不集中,但是是主键,使用很频繁
注意:查询索引比查询表要快很多!
数据量有要求 太少没必要加索引!!
-
测试
(1)创建一个测试用表(大数据量!!!)
create table cstable( id number, sex varchar(10) name varchar(10) );
(2)开启SQL执行时间
set timing on /off
(3)插入数据
begin for i in 1..1000000 loop insert into cstable values(i,'M','LISI'); end loop; commit; end;
(4)查询id=985762 ;
select id, sex, name from cstable where id=985762 ;
(5)创建一个单一索引
create index cs_index on cstable(id);
(6)此时再查询一个id=989699;
select id, sex, name from cstable where id=989699 ;
节省了0.03秒!
修改索引
-
重命名索引:
alter index index_old rename to index
-
合并索引、重新构造索引
alter index index_name coalesce;–合并索引
alter index index_name rebuild;–重新构造
删除索引
- drop index index_name ;
查看索引
-
语句:
select t.index_name, t.index_type, t.tablespace_name, t.status, t.uniqueness from all_indexes t