Oracle,视图，同义词，索引，序列，分区

1              视图

1.1         视图简介

视图是由一个或者多个表组成的虚拟表；那些用于产生视图的表叫做该视图的基表。视图不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候只是重新执行SQL。一个视图也可以从另一个视图中产生。视图没有存储真正的数据，真正的数据还是存储在基表中。一般出于对基本的安全性和常用的查询语句会建立视图；并一般情况下不对视图进行新增、更新操作。

【语法】

 

--创建视图

CREATE [OR REPLACE] VIEW <view_name>

AS

<SELECT 语句>;

 

 

--删除视图

DROP VIEW <view_name> ;

 

1.2         视图操作

 

 

-- 授予wxz用户 创建视图 的权限

grant createviewto wxz;

 

-- 登录wxz，创建视图

create orreplaceview v_emp

as

select empno,enamefrom emp;

 

--通过视图查询数据

select *from v_emp;

 

--通过视图添加数据,需要保证基表的其它数据项可以为空

insert into v_emp(empno,ename)values(3333,'wxz');

 

--通过视图修改数据

update v_emp set ename='wxz3'where empno=3333;

 

--通过视图删除数据

delete from v_emp where empno=3333;

 

--基于多个基表的视图，不建议使用视图进行增删改操作

create orreplaceview v_dept_emp

as

select dept.deptno,dept.dname,enamefrom empinner join dept on emp.deptno=dept.deptno;

 

--查询多个基表的视图

select *from v_dept_emp;

 

--创建基于视图的视图

create orreplaceview vv_emp

as

select ename from v_emp;

 

--查询基于视图的视图

select *from vv_emp;

 

--删除视图

drop view v_emp;

drop view v_dept_emp;

drop view vv_emp;

 

 

2              同义词

同义词是数据库模式对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，Oracle数据库将它翻译成对应模式对象的名字。与视图类似，同义词并不占用实际存储空间，只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象，如表、视图、同义词、序列、存储过程等，数据库管理员都可以根据实际情况为他们定义同义词。隐藏对象名称和所有者。

2.1         私有同义词

私有Oracle同义词由创建它的用户所有；创建的用户需要具有CREATE SYNONYM权限。

 

【语法】

CREATE SYNONYM <synonym_name> for <tablename/viewname...>

 

【示例】

--管理员 授权用户wxz创建同义词的权限

grant createsynonymto wxz;

 

--创建私有同义词

create synonym syn_empfor emp;

create synonym syn_v_empfor v_emp;--为视图v_emp创建私有同义词（别名）

 

--使用私有同义词

select empno,enamefrom syn_emp;

update syn_emp set ename='wxz4'where empno='1234';

 

--删除同义词

drop synonym syn_emp;

 

 

2.2         公有同义词

公有Oracle同义词由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公有同义词。公有同义词往往用来标示一些比较普通的数据库对象，这些对象常需要引用。公有同义词一般由管理员用户创建及删除，普通用户需要创建及删除需要create public synonym和drop public synonym权限。

【语法】

CREATE PUBLIC SYNONYM <synonym_name> for <tablename/viewname...>

 

--登陆sys管理员用户，授权用户wxz创建、删除(公有的删除权限需要特别给定)公有同义词权限

grant createpublicsynonym,droppublicsynonym to wxz;

--revoke create public synonym,drop public synonym from wxz;

 

--登陆wxz用户创建公有同义词 conn wxz/wxz;

create publicsynonym syn_public_empfor emp;

 

--使用公有同义词

select *from syn_public_emp;

 

-- 登录system管理员 conn system/orcl; 创建wxz2并授权

--create user wxz2 identified by wxz2 default tablespace wxz_ts;

--grant connect,resource to wxz2;

 

--为其它用户wxz2授权使用公有同义词（需要给予使用表的权限）

grant select,updateon wxz.empto wxz2;

--revoke select,update on wxz.emp from wxz2;

 

--登陆wxz2用户下使用公有同义词syn_public_emp

select *from syn_public_emp;

update syn_public_emp set  ename='wxz5'where empno=5555;

 

--删除同义词

--登陆wxz，删除公有同义词

drop publicsynonym syn_public_emp;

 

 

3              索引

索引是建立在数据库表中的某些列的上面，是与表关联的，可提供快速访问数据方式，但会影响增删改的效率；常用类型（按逻辑分类）：单列索引和组合索引、唯一索引和非唯一索引。

 

什么时候要创建索引

（1）在经常需要搜索、主键、连接的列上

（2）表很大，记录内容分布范围很广

（3）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

（4）在经常使用在WHERE子句中的列上面创建索引

 

什么时候不要创建索引

（1）表经常进行 INSERT/UPDATE/DELETE 操作

（2）表很小(记录超少)

（3）列名不经常作为连接条件或出现在 WHERE 子句中

（4）对于那些定义为text, image和bit数据类型的列不应该增加索引

3.1         创建索引

 

【语法】

CREATE [UNIQUE] INDEX <index_name> ON <table_name>(字段 [ASC|DESC]);

 

【说明】

UNIQUE --确保所有的索引列中的值都是可以区分的。

[ASC|DESC] --在列上按指定排序创建索引。

 

(创建索引的准则：

1.如果表里有几百行记录则可以对其创建索引(表里的记录行数越多索引的效果就越明显)。

2.不要试图对表创建两个或三个以上的索引。

3.为频繁使用的行创建索引。)

 

 

【示例】

--创建单列唯一索引，表中的列值将不允许重复

create uniqueindex index_emp_empnoon emp(empno);

 

--创建单列非唯一索引

create index index_emp_enameon emp(ename);

 

--创建组合列、唯一索引

create uniqueindex index_emp_ename_jobon emp(ename,job);

 

--创建组合列、非唯一索引

create index index_emp_job_salon emp(job,sal);

 

 

3.2         删除索引

 

【语法】

DROP INDEX <index_name>;

 

 

【示例】

--删除索引

drop index index_emp_empno;

drop index index_emp_ename;

drop index index_emp_ename_job;

drop index index_emp_job_sal;

 

 

4              序列

序列是oracle提供的一个产生唯一数值型值的机制。通常用于表的主健值，序列只能保证唯一，不能保证连续。

4.1         创建序列

【语法】

CREATE SEQUENCE <sequencen_name>

[INCREMENT BY n]

[START WITH n]

[MAXVALUE n][MINVALUE n]

[CYCLE|NOCYCLE]

[CACHE n|NOCACHE];

 

INCREMENT BY n --表示序列每次增长的幅度;默认值为1.

START WITH n --表示序列开始时的序列号。默认值为1.

MAXVALUE n --表示序列可以生成的最大值(升序).

MINVALUE n --表示序列可以生成的最小值(降序).

CYCLE --表示序列到达最大值后，在重新开始生成序列.默认值为 NOCYCLE。

CACHE n--允许更快的生成序列.预先生成n个序列值到内存（如果没有使用完，那下次序列的值从内存最大值之后开始；所以n不应该设置太大）

 

 

【示例】

--创建递增序列

create sequence seq_test

increment by 1

start with1

maxvalue 1000

nocycle;

 

--创建递减序列

create sequence seq_test2

increment by -1

start with5

maxvalue 5

minvalue 1

nocycle;

 

 

4.2         序列使用

1、NEXTVAL 返回序列下一个值；第一次访问时，返回序列的初始值，后继每次调用时，按步长增加的值返回

【语法】

select <sequence_name>.nextval from dual;

 

【示例】

select seq_test.nextval from dual;

 

 

2、CURRVAL 返回序列的当前值.注意在刚建立序列后,序列的CURRVAL值为NULL，所以不能直接使用。使用过NEXTVAL访问序列后才能使用

【语法】查看序列的当前值

select <sequence_name>.currval from dual;

 

【示例】

select seq_test.nextval from dual;

select seq_test.currval from dual;

 

 

运用序列

-- 创建序列

create sequence seq_emp_empno

start with1000

increment by 1

maxvalue 9000

minvalue 1000

nocycle;

 

-- 使用序列作为主键插入emp表的empno列

insert into emp(empno,ename)

values(seq_emp_empno.nextval,'wxz1');

insert into emp(empno,ename)

 values(seq_emp_empno.nextval,'wxz2');

 

-- 查看emp表数据

select empno,enamefrom emp;

 

-- 查看当前序列的值

select seq_emp_empno.currval from dual;

 

 

--修改序列

alter sequence seq_emp_empno

maxvalue 9999

cycle;

 

 

4.3         删除序列

 

【语法】

DROP SEQUENCE <sequence_name>

 

【示例】

drop sequence seq_test;

 

4.4         序列与sys_guid

sys_guid和序列都可以作为主键值。

--使用SYS_GUID函数，32位，由时间戳和机器标识符生成，保证唯一

select sys_guid()from dual;

 

 

5              分区表

5.1         分区表用途

分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

分区表的优点：

（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；

（2）可以对单独的分区进行备份和恢复；

（3）可以将分区映射到不同的物理磁盘上，来分散IO；

（4）提高可管理性、可用性和性能。

数据量大的表，一般大于2GB；数据有明显的界限划分；对于Long和Long Raw类型列不能使用分区。

5.2         分区表类型

一般包括范围分区，散列分区，列表分区、复合分区（范围-散列分区，范围-列表分区）、间隔分区和系统分区等。

5.2.1  范围分区

范围分区根据数据库表中某一字段的值的范围来划分分区。

【语法】

在Create Table语句后增加

PARTITION BY RANGE(column_name)

(

PARTITION part1 VALUES LESS THAN (range1) [TABLESPACE tbs1],

PARTITION part2 VALUES LESS THAN (range2) [TABLESPACE tbs2],

           ....

PARTITION partN VALUES LESS THAN (MAXVALUE) [TABLESPACE tbsN]

);

 

【说明】

MAXVALUE：当分区列值都不在设置的范围内时，新增数据将到这个分区中

 

【示例】

-- 创建表，并设置分区

create table myemp

( empno number(4)primarykey,

  ename varchar2(10),

  hiredate date,

  sal   number(7,2),

  deptno number(2)

)

partition by range(sal)

(

  partition p1 values less than(1000),

  partition p2 values less than(2000),

  partition p3 values less than(maxvalue)

);

 

-- 插入数据

insert into myemp(empno,ename,hiredate,sal,deptno)

select empno,ename,hiredate,sal,deptnofrom emp;

 

-- 查看工资1000-2000的数据

select *from myemppartition(p2);

 

-- 删除工资小于1000的数据

delete from myemppartition(p1);

 

-- 查看数据

select *from myemp;

 

 

5.2.2  列表分区

列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

【语法】

在Create Table语句后增加

PARTITION BY LIST(column_name)

(

PARTITION part1 VALUES (values_list1),

PARTITION part2 VALUES (values_list2),

           ....

PARTITION partN VALUES (DEFAULT)

);

其中：column_name是以其为基础创建列表分区的列。

      part1...partN是分区的名称。

      values_list是对应分区的分区键值的列表。

      DEFAULT关键字允许存储前面的分区不能存储的记录。

 

 

【示例】

-- 创建表，并设置分区

create table myemp2

( empno number(4)primarykey,

  ename varchar2(10),

  hiredate date,

  sal   number(7,2),

  deptno number(2)

)

partition by list(deptno)

(

  partition dept10 values(10),

  partition dept20 values(20),

  partition dept30 values(30),

  partition deptx  values(default)

);

 

-- 插入数据

insert into myemp2(empno,ename,hiredate,sal,deptno)

select empno,ename,hiredate,sal,deptnofrom emp;

 

-- 查看部门20的数据

select *from myemp2partition(dept20);

 

-- 删除部门30的数据

delete from myemp2partition(dept30);

 

-- 查看数据

select *from myemp2;