四

在system用户中创建用户和授权： create user usera identified by usera defalut tablespace test; grant connect,resource to usera;

一，同义词

分私有和公共

私有:普通用户创建的，只有创建该同义词的用户才可以使用 前提：普通用户具有对scott用户的表emp具有访问权利 connect scott/tiger; grant all on emp to usera; --all 包括：select ,update ,insert ,delete connect usera/usera; create synonym emp for scott.emp; select * from emp; 公共:公共一般由dba创建，需要具有create public synonym 系统权限,如普通用户要创建需要 connect system/manager; grant create public synonym to usera; conect usera/usera;

create public synonym emp for scott.emp; 其他用户也可以使用公共同义词 emp 查看用户自己创建的同义词:user_synonyms select synonym_name from user_synonyms;

二，序列

通常和表一起使用，用来生成唯一主键值，在插入数据时使用，但不属于任何表，独立于表存在 create sequence deptseq increment by 10 start with 50

insert into dept values(deptseq.nextval,'Sale_dept','HaizhuStreet'); 使用伪列nextval 和 currval nextval:创建序列后第一次使用返回序列初始值，既start with指定的值，后续使用增量后的值 currval:返回序列的当前值 数据词典: user_sequences

三、视图

在创建视图时可以使用group by ,order by 子句,函数等,使用函数时需要指定列别名 1, 简单视图：使用单表查询创建的视图 可以对视图进行增删改操作,除了一些限定外，如：创建只读视图，或带有with chek option 选项的视图，或视图中不包含 基表中不允许为空的列(不允许向视图中插入数据)等

1）使用with check option 可以限定对单表视图的修改, 不能更新无法通过该视图查看的行 create or replace view emp_view as select * from emp where job='SALESMAN' with check option constraint empv;

SQL> select * from emp_view;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

update emp_view set job='CLERK' where empno=7499;将无法更新 如创建视图时没有with check option， 修改视图后将在视图查不到该行数据

2） 使用with read only 创建只读视图 create or replace view emp_view as select * from emp where job='SALESMAN' with read only; 3) 创建视图时使用force,强制创建视图 如果视图定义中引用了不存在的表或表中有不存在的列，或创建视图的所有者没有访问表的权限，都可以创建， 但创建的视图有错误，在稍后创建表或得到访问权限后，ORACLE自动重新编译使视图有效，可以手动编译 假设dept表也不存在a1列，但还可以创建视图，但会提示错误，在表dept添加列a1后,视图自动编译为有效 create or replace force view emp_view as select a1,dname from dept; 可以 使用 alter view emp_view compile ; 手动编译 2, 复杂视图 ： 1)使用多表联接创建视图, 2)创建视图时包括函数或group by 等 复杂视图通常不能直接进行增删改，但对于使用多表联接创建的视图，可以使用INSTEAD OF 触发器可以修改(后面课程) create or replace view emp_dept_view as select empno,ename,dname,emp.deptno from dept,emp where dept.deptno=emp.deptno; 3, 键保留表 键保留表:复杂视图中出现的表。 如果满足条件:1) 主键列全部显示在视图中，并且它们的值在视图中都是唯一且非空的. 如: create or replace view emp_dept_view as select empno,ename,dname,dept.deptno from dept,emp where dept.deptno=emp.deptno;

empn是emp表的主键,并且它的值在emp_dept_view 中是唯一且非空.所以emp表是键保留表 而deptno的值在视图中不唯一,所以dept不是键保留表 可以 修改视图中键保留表中的数据,主键值除外 update emp_dept_view set ename='JOE' where empno=7566; //可以 update emp_dept_view set dname='abc' where deptno=30; //不行 dname是dept表中的列,dept不是键保留表 4,分区视图 create or replace view v_emp as select * from emp1 partition(p1) union all select * from emp1 partition(p2);

数据字典: user_views 使用方法：在SQL*PLUS下 col view_name for a10 --定制显示的列宽 col text for for a50 set linesize 200 --设置每行显示的字符数 select view_name,text from user_views; --查看用户创建的视图情况 四，索引 使用索引的目的： 1）强制唯一 ：在primary key 列或UNIQUE 列上自动创建唯一索引 2）提高查询速度: 当查询使用索引时，速度会有戏剧性的提高 只有在查询中使用where 或 order by 时，才使用索引 1，唯一索引 create unique index ind2 on dept(deptno); 定义索引的列中，表的任意两行的值都不相同 2, 组合索引 在表的多列上创建的索引 create index comind1 on emp(deptno,empno) 3, 反向键索引 该索引反转索引列中的每一个字节 使插入操作分布在整个索引上，避免插入数据时索引性能的降低。但会使查询变慢 create index revind1 on salgrade(grade) reverse;

可将反向键索引改为标准索引 alter index revind1 rebuild noreverse;

4,位图索引(可在单个或多个连接表上创建) 如果列重复的次数超过了100次，则可以考虑在该列上建立位图索引。既不同值的数目比行数少的情况 表有1000000 行，某列有10000个不同值,可在该列上建立位图索引 在位图索引中，使用每个键值的位图，而不是使用rowid。位图中的每一位对应一个可能的rowid 映射函数 每位位置---------rowid(确定表的一行) 　当where 子句中有and 或 or 条件时，可直接在位图上进行布尔运算 create bitmap index bitemp on emp(deptno);

5,索引组织表 对表是基于主键的访问，而不是基于rowid的访问 表中的数据象索引一样被存储，表中就没有rowid,因此用户不能从 索引组织表 中选取rowid伪列的值 对于要求精确匹配的查询和基于范围的搜索，它提供了一种更快的访问数据的方法 　　　表的数据存储在与其关联的索引中，索引中不存储rowid ,而是存储表的实际数据

create table indorg ( a1 number(2) primary key, a2 varchar2(10) ) organization index; 用户不能在indorg表的其它列上创建索引，当数据很少变化时，使用索引组织表 6,基于函数的索引 如果在where 中某列上使用了函数，即使在列上创建了索引，索引也不会被使用。所以可以创建函数索引 create index exind1 on emp(length(ename)); --基于函数创建索引 create index exind2 on emp(sal+nvl(comm,0)); --基于表达式创建索引

7,键压缩索引 为了节省索引块的空间,相同的列值，作为前缀存储一次,后缀是不同的rowid create index exind1 on emp(length(ename)) compress 1; --作为前缀处理的键列的数目为1 create index exind2 on emp(deptno) compress 1;

8,索引分区 当用户建立了分区表后，也可以在该表上创建索引,这个索引可以按照用于表分区同样值范围进行划分 　　　 create table emp1( empno number(4), ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2)) partition by range(empno) ( partition p1 values less than (7566), partition p2 values less than (7900), partition p3 values less than (maxvalue) ) 　 1) 使用local 关键字，可以为每一个分区建立一个独立的索引, 这个索引命令将建立３个独立的索引，每个索引对应一个分区 数据字典： select index_name,partition_name ,tablespace_name from user_ind_partitions

i) 本地无前缀索引 create index emp_ind1 on emp1(ename) local

ii) 本地前缀索引(索引的第一列与分区的第一列相同) 　　　　　　　 create index emp_ind1 on emp1(empno) local; 或 指定不同的索引表空间 create index emp_ind1 on emp1(empno) local ( partition p1 tablespace test1_idx, partition p2 tablespace test2_idx, partition p3 tablespace test3_idx) 如果分区上执行了修改，会自动重建索引 SQL> col segment_name for a20 SQL> select segment_name,partition_name,segment_type,tablespace_name from user_segments where segment_name='EMP_IND1';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME -------------------- ------------------------------ ------------------ ------------------------------ EMP_IND1 P1 INDEX PARTITION SYSTEM EMP_IND1 P2 INDEX PARTITION SYSTEM EMP_IND1 P3 INDEX PARTITION SYSTEM

　　　 2) 使用global 关键字,创建全局索引 i) 全局无前缀索引 create index emp_ind2 on emp1(ename) global; 　　　ii) 全局前缀索引 建立全局前缀索引(empno为前缀，不能使用ename做索引列) create index emp_ind2 on emp1(empno,ename) global partition by range(empno) ( partition p1 values less than (6000) , partition p2 values less than (7500) , partition p3 values less than (maxvalue) ) 对分区的任何操作都会使全局索引无效，需要重建索引

注释：不能手动删除索引分区，当所引用的数据从表分区中删除时，索引分区自动删除

　五，簇

　　　　簇是一组表，由两个或多个拥有公共列的表组成 　　　　簇是用来存储表的方法，这些表相互联系密切并通常相连接在磁盘的相同区域上。 　　　　如：emp 表存储在磁盘的一个区域(section) 内,而dept表存储在另一区域内，它们的行可以被连接到同一区域上，该区域 　　　　称为簇（cluster）,簇关键字通常是在查询中把表连接起来的一列或几列(如dept,emp中的deptno),要聚簇这些表，用户必须 　　　　是表的所有者 1,创建了空簇 create cluster emp_dept(deptno_key number(2)); 创建了空簇（如同建表一样，也设置了空间）

2,下面创建包含在簇中的表 create table dept ( deptno number(2) primary key, dname char(14), local char(13) ) cluster emp_dept(deptno); 　　　　　　　　　　　　　　--deptno为表中将要存储在簇关键字deptno_key中的列名 create table emp ( empno number(4), ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2) references dept ) cluster emp_dept(deptno);

3, 建立簇索引(在向簇表插入数据之前建立簇索引) create index emp_dept_NDX on cluster emp_dept;

4, 插入数据

insert into emp select * from scott.emp; insert into dept select * from scott.dept;

两个表的数据实际存储在同一位置， 簇好象是包含两个表数据的一个大表