四
在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;
两个表的数据实际存储在同一位置， 簇好象是包含两个表数据的一个大表
