Oracle SQL 序列 锁 位图 视图 事务 索引 分区 的理解笔记

 

--序列化对象

create sequence seq_001

increment by 1

start with 1001

nomaxvalue

create table kind

(

kid int primary key,

kname varchar2(200)

)

 

select * from kind

insert into kind values (seq_001.nextval,'aaa')

--删除序列

drop sequence seq_001

--视图

--with read only

create view view_emp

as

select ename,sal,emp.deptno,dname

from emp inner join dept on 

emp.deptno=dept.deptno

with read only;

select * from view_emp

drop view view_emp;

create view view_emp1

as

select * from emp where

sal>2000

--对视图操作时

--对视图条件做一个检测

with check option 

--现在把sal全部改为3000

update view_emp1 set sal=3000

 

--现在把sal全部改为小于视图的条件的值2000

update view_emp1 set sal=2000

---结果：用CHECK选项查看WHERE子句违例

---起一个别名--同义词

create synonym e for emp

---别的用户也可以用

create public synonym d for dept

create public synonym em for emp

select * from emp

--索引

--标准索引

create index indx_001 on

 emp(ename);

select * from emp 

where ename='SCOTT'

drop index indx_001

---产生红黑二叉树，二叉树趋于平衡

create index indx_001 on

 emp(ename)  reverse;

 

 

 ----位图索引

 

 

 ---事务

 --事务回滚

 rollback

 --事务提交

 commit

 

 select * from emp

 

 update emp set sal=sal-1000 

 where ename='SCOTT'

 --行级锁

 select * from emp where sal<3000 for update;

 

 

  update emp set sal=sal+1000 where ename='SCOTT';

 

 

 

 ----创建序列化

create sequence seq_002

increment by 1

start with 1001

nomaxvalue

---序列化对象的使用

--Nextval 访问序列化的下一个值

--Currval 访问序列化的当前值

select * from kind;

insert into kind

values(seq_002.nextval,'aa');

---删除序列化

drop sequence seq_002;

---视图

---with read only 

drop view e_d_v;

create view e_d_v

as

select ename,sal,emp.deptno,dname

from emp inner join dept on

emp.deptno=dept.deptno

with read only;

----视图2

drop view e_d_v;

create view emp_view

as

select * from emp where

sal>=3000

WITH CHECK OPTION

select * from emp;

select * from emp_view;

update emp_view set sal=2000;

 

---同义词

drop synonym em;

create synonym e for emp;

select * from e;

grant create public synonym to scott;

drop public synonym d;

create public synonym d2 for dept;

select * from d2;

----索引

----标准索引

create index idx_001 on emp(ename);

select * from emp

where ename='SCOTT';

---组合索引

create index idx_001 on 

emp(ename,sal);

---唯一索引

create unique index idx_001 on 

emp(ename);

drop index idx_001;

---反向键索引

---红黑二叉树

create index idx_001 on

emp(ename) reverse;

--此索引为二进制，检索速度要快

---位图索引

create bitmap index

idx_001 on emp(ename);

---事务

select * from emp;

---银行转账的事务

update emp set sal=sal-1000

where ename='SCOTT';

update emp set sal=sal+1000

where ename='SMITH';

----红色按钮

rollback;

---绿色按钮

commit;

----行级锁

select *

from emp where 

sal>=3000 for

update;

----表级锁

---exclusive独占锁/排它锁

---share  共享锁

lock table emp

in share mode;

---分区管理

----范围分区

drop table bank;

create table bank

(

 bno varchar2(20) primary key,

 bmoney float

)

partition  by range(bmoney)(

   partition p1 values less than(5000),

   partition p2 values less than(15000),

   partition p3 values less than(25000)

)

----散列分区

create table bank

(

 bno varchar2(20) primary key,

 bmoney float

)

partition  by hash(bmoney)(

   partition p1,

   partition p2,

   partition p3

)

----列表分区

 

---分区的维护

alter table bank

add partition p4;

alter table bank

drop partition p3;

---删除分区里数据

alter table bank truncate partition p1;