Oracle Class4. 数据库对象(同义词,序列,视图,索引,簇)

------------------------2013-5-9------------------------
索引的建立情况：经常用于查询，排序和分组的列（即经常在where,order或group by子句中出现的列）

主键索引和复合索引。

desc student;
create index i_stu_name on student(STU_NAME);

--删除索引，当表结构被删除时，与其所有的索引都会随之删除。
drop index i_stu_name;

--查询索引的语句，表名必须大写，否则查询不到值。
select table_name, index_name,status from all_indexes where table_name = 'STUDENT';

select index_name,column_name,column_position from all_ind_columns where table_name='STUDENT';

索引的特点：
1.不同值较多上建立索引，不同值少的列上不要建。如性别列只有男与女两个值，因此就没有必要建立索引。（bitmap）
2.索引上面加表达式，索引不能正常使用。 （函数索引）
3.where子句中如果使用in,or,like,!=,均会导致索引不能正常使用。
4.使用复合索引进行查询时必须使用前置列。

写where条件时，有索引的判断在前，其他字段的判断在后。如果where条件中用到复合索引，按照索引列在复合索引中出现的顺序来依次写where条件。
使用多表连接时，在from子句中，将记录数少的表放在后面，可提高执行效率。

首先从大表中查询需要的记录，此时记录数已经被大量缩小，然后再和其他两个表连接，速度会得到很大改善！

查询数量较大时，使用表连接代替in,exists,not in,not exists等。

然后不断的积累经验，并且注意不能一概而论。

全局索引，本地索引，全局分区索引。

--创建临时表
--create global temporary table myemp as select * from student;

select * from myemp;
desc myemp;

--删除临时表
drop table myemp;

--增加一个列
--alter table student add(deptno varchar2(6));
--修改一个列
--alter table student modify(deptno varchar2(10));
--删除表中的一个列，oracle8.0.5版本以上方可。
alter table student drop column deptno;

desc student;

-->3.2.4使用CHECK做限制条件

------------------------2013-5-10------------------------

--创建员工宿舍表
create table lodging(
  lodging char(18) primary key,
  name varchar2(10)
);

--创建工人表
create table worker(
  empno number(4) primary key,
  name varchar2(10),
  age number(2) check(age between 18 and 60),   --check检查约束
  lodging char(18) references lodging(lodging)  --外键关联
);

--员工基本信息表
create table emp3(
  empno number(4) constraint abc primary key,
  ename varchar2(10),
  job varchar2(10),
  sex char(2) check(sex='男' or sex='女'),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2),
  check (sal+comm > 0 and sal+comm < 5000)
);

--使用unrecoverable创建表，nolodging lodging
create table table_copy as select * from emp3 unrecoverable;
--使用nolodging关键字创建
create table table_copy2 as select * from emp3 nolodging;
--使用lodging关键字创建
create table table_copy3 as select * from emp3 lodging;

--查询表空间
select tablespace_name,table_name,initial_extent from user_tables;

create table table3(
  xm varchar2(12),
  sal number(4,2)
);

//修改表结构
alter table table3 modify sal number(6,2);

insert into table3 values('gao',1111.11);
insert into table3 values('gao',1111.11);
insert into table3 values('zhu',5555.55);

insert into table3 values('zhu',55551.55);
ORA-01438: 值大于此列指定的允许精确度

##定点数的精度(p)和刻度(s)遵循以下规则##
1.当一个数的整数部分的长度 > p-s时，Oracle会报错。
alter table table3 add sal2 number(6,2);     -->1111.11
2.当一个数的小数部分的长度 > s时，Oracle就会舍入。  
insert into table3 values('zhu',5555.5589);         -->5555.56
3.当s(scale)为负数时，Oracle就对小数点左边的s个数字进行舍入。
alter table table3 add sal2 number(2,-2);

insert into table3 values('zhu',1111.11,5609.12);   -->5600
insert into table3 values('zhu',1111.11,5678.78);   -->5700
insert into table3 values('zhu',1111.11,609.12);    -->600
insert into table3 values('zhu',1111.11,63.12);     -->100
insert into table3 values('zhu',1111.11,43.12);     -->0

4.当s > p时，p表示小数点后第s位向左最多可以有多少位数字，如果大于p则Oracle报错，小数点后s位向右的数字被舍入。
--更加明白的说法--
--当p小于s时候，表示数字是绝对值小于1的数字，且从小数点右边开始的前s-p 位必须是0，保留s位小数。
insert into table3 values('zhu',1111.11,43.12,0.00126);  -->0.0013

insert into table3 values('zhu',1111.11,43.12,0.00996);  -->报错，这样会进位，绝对值大于1。
#http://ilinux.iteye.com/blog/289550#

--对表中的列表示为不使用
--alter table table3 set unused(sal3);

--创建主键
create table dept1(
  deptno number(2),
  dname varchar2(10),
  loc varchar2(20),
  constraint pk_dept primary key(deptno)
);

--增加表的主键
--alter table table3 add ( pk_key primary key (sal) );

--ORA-00907: 缺少右括号 ??
alter table table3 add(pk_key primary key (xm));  --可能跟表有关系

alter table dept1 add primary key(deptno);        --系统默认的名称
alter table dept1 add constraint pk_dept primary key(deptno); --constraint必须要写--
#http://miracle.blog.51cto.com/255044/55315#

--主键不可用
create table dept2(
  deptno number(2) primary key,
  dname varchar2(10)
)
disable primary key;

--启用取消的主键，才可以建立外键关系，否则报错。
alter table dept2 enable primary key;

--ORA-02270: 此列列表的唯一或主键不匹配
create table table4(
  id varchar2(8),
  deptno number(2) references dept2(deptno)   --外键
);

唯一索引和复合主键
e_name varchar2(8) constraint un_dname unique,
name varchar2(10) constraint pt_1 primary key,
表结构最后一行写
constraint pri_3 primary key(deptno)
--外键
deptno constraint pri_4 references dept5(deptno);

--创建复合主键
--alter table dept1 add constraint pk_dept primary key(deptno,dname);

alter table dept1 add primary key(deptno,dname);

--创建复合主键 禁用状态。
alter table dept1 add primary key(deptno,dname) disable;

--禁用主键，如果有一外键依赖于该主键，则系统会报错。
alter table dept1 disable constraint SYS_C002136;  （语法适用于外键）
--恢复主键
alter table dept1 enable constraint SYS_C002136;
--删除主键
alter table dept1 drop constraint SYS_C002136;    （语法适用于外键）
--删除主键，将外键一起删除。
alter table dept1 drop constraint pk_2 cascade;

create table dept5(
  deptno varchar(8),
  name varchar(8),
  loc varchar(4),
  constraint pri_3 primary key(deptno)  --建立主键
);

create table emp5(
  emptno number(4),
  ename varchar2(10),
  job varchar2(10),
  mgr number(4),
  deptno constraint pri_4 references dept5(deptno)  --外键
);

create table emp6(
  emptno number(4),
  ename varchar2(10),
  job varchar2(10),
  mgr number(4),
  deptno, constraint pri_5 foreign key(deptno) references dept5(deptno)  --外键，逗号不能少。
);

create table emp7(
  emptno number(4),
  ename varchar2(10),
  job varchar2(10),
  mgr number(4),
  deptno varchar(8) , constraint pri_6 foreign references dept5(deptno) on delete cascade  --外键，使用delete cascade管理引用完整性
);

#索引组织表# 基于主键进行搜索
create table命令与organization index子句一起用于创建索引组织表。
--表及索引.txt--

--抽象数据类型的使用
create type add_type as object(
  street varchar2(10),  --街道名
  city varchar2(10),    --城市名
  state char(2),        --州代码
  zip   number          --邮编
);
create table customer(
  name varchar2(10),
  address add_type
);
insert into customer values('1',add_type('mystatree','some city','st',10001));

大数据类型使用，clob,blob,bfile。  -->ORACLE埃里森1.txt

3.9.1.表和索引数据字典
 .DBA_TABLES,ALL_TABLES,USER_TABLES  存放表的基本信息
 .DBA_INDEXS,ALL_INDEXS,USER_INDEXS  存放索引的基本信息
 .DBA_IND_COLUMN                     存放有索引的列的信息
 .ALL_CONSTRAINTS                    存放表的限制信息
 .ALL_COLS_COLUMNS                   存放表的列的限制信息

查询当前用户的基本信息，包括表名，索引名及表空间，存储参数。
select tablespace_name,table_name,initial_extent,next_extent from all_tables where owner = user;

--查询当前用户的限制信息
select owner,constraint_name,table_name from user_constraints;

select search_condition from user_constraints;
SEARCH_CONDITION
--------------------------------------------------------------------------------
sex='男' or sex='女'
sal+comm > 0 and sal+comm < 5000
"ID" IS NOT NULL
"NAME" IS NOT NULL
age between 18 and 60

#创建视图#
create or replace view viewTitles
as
select title_id,nvl(price,0) price from titles;  --SQL语句--

无法直接指定一个分区数据表的多个分区中的数据时，通过union关键字将多个数据表分区联结。
create or replace view viewStus
as
select * from student partition(east)
union
select * from student partition(west);
--通过视图来实现，可以简化SQL命令。
select * from viewStus;

查看用户创建的视图信息(user_views)
select * from user_views;

删除试图
delete view viewStus;

--查看用户序列
desc user_sequences;
--查询簇信息
desc user_clusters;

##簇##
簇
簇表
簇索引
簇表其实就是一组表，是一组共享相同数据块的多个表组成。将经常一起使用的表组合在一起成为簇可以提高处理效率。
在一个簇中的表就叫做簇表。建立顺序是：簇 ->簇表 ->数据 ->簇索引。

唯一索引
组合索引
反向键索引：在创建索引时使用reverse关键字。
位图索引：为低基数列创建，bitmap index语句用于创建位图索引。
   优点：减少响应时间，降低空间占用。
   --无须频繁的修改造成死锁--

当索引被创建后，由Oracle自动维护。

create table student8(
  stu_no number(2),
  stu_name varchar2(8)
);

--在指定的字段上创建唯一索引
create unique index idx_stu_no
on student8(stu_no);

insert into student8 values(1,'ANiu');
--ORA-00001: 违反唯一约束条件 (NEWLIFEYHJ.IDX_STU_NO)
insert into student8 values(1,'AZhu');

select * from student8;

数据库对象
同义词：数据库对象的替换名称，隐藏了对象的名称和所有者。
序列：生成唯一，连续的整数。
-- 获取序列的下一个值
-- 序列的第一个值必须通过NextValue来获得
select seqTest1.NextVal from dual;
-- 获取序列的当前值
select seqTest1.CurrVal from dual;
视图：经过定制的表示方式，用于显示来自一个或多个表的数据。
索引：加快了SQL语句的执行速度。多个列上的创建的索引称为“组合索引”。
      在索引组织表中，数据访问基于主键值。
簇：一组表，这些表拥有公共列，并且一起使用。

create
alter
drop

------------------------2013-5-14------------------------
普通表与索引表的对比
普通表   索引编排表
rowid唯一的标识行 主键唯一的标识行
隐式的rowid列  没有隐式的rowid列
基于rowid的访问  基于主键的访问
顺序扫描返回所有行 完全索引扫描返回所有行，并按主键顺序排列
普通表可以存储在簇中 索引组织表不能存储在簇中
支持分发，复制和分区 不支持分发，复制和分区

区别：rowid与主键，顺序扫描和索引扫描，簇，分发，复制和分区。

基于函数的索引
基于一个或多个列上的函数或表达式创建的索引
表达式中不能包含聚集函数（sum,count,avg,min,max）
不能在lob,ref或嵌套表列上创建

键压缩索引：compress子句用于创建这种索引。
分区索引
分区索引的类型
本地索引
全局索引