索引

索引
B树索引 根节点块，分支节点块，叶子节点块（rowid,键值）
位图索引(离散度低，男女 1,2)
索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，索引能优化查询，不能优化DML操作。oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护




常用的B树索引类型
唯一或非唯一索引（unique or  nounique）,唯一索引指键值不重复
drop table emp1;
create table emp1 as select * from emp;
update emp1 set empno = 7788 where empno =7900


create unique index emp1_empno_index on emp1(empno);
update emp1 set empno =7900 where empno =7788 and ename <>'SCOTT';
create unique index emp1_empno_index on emp1(empno);
update emp1 set empno = 7788 where empno =7900


或者
drop index emp1_empno_index;
create index emp1_empno_index on emp1(empno);




组合索引（composite）绑定了两个以上列的索引


create index emp1_comp_index on emp1(job,deptno)


反向键索引(reverse)将字节倒置后组建键值，当使用序列产生主键索引时，可以防止叶节点出现热快现象
create index emp1_mgr_index on emp1(mgr) reverse


函数索引，以索引列值的函数值去组织索引


create index emp1_fun_index on emp1(lower(ename)) 
select * from emp1 where lower(ename)='scott';
压缩，重复键值只存储一次，重复的键值在页块中只存储一次，后面跟所有与之匹配的rowid字符串


create index emp1_sal_index on emp1(sal)  compress




升序或降序，叶节点中的键值排序默认是升序的
create index emp1_deptjob_index on emp1(deptno desc,job asc)


select * from user_indexes
select * from user_ind_columns




优化器使用索引的扫描方式
drop table emp1;
create table emp1 as select * from emp;


索引唯一扫描。通过唯一索引查找一个数值返回单个rowid。对于唯一组合索引，要在where的谓词“=”后包含所有列的“布尔与”
create unique index index_emp1_empno on emp1(empno);
select * from emp1 where empno =7788;
create index index_emp1_deptnoename on emp1(deptno,ename);
select * from emp1 where deptno =20 and ename ='SCOTT';
select * from emp1 where ename ='SCOTT' and deptno =10;
select * from emp1 where ename ='SCOTT';
select * from emp1 where deptno =20;


索引范围扫描。在非唯一索引上可能返回多行数据。所以在非唯一索引上都使用索引范围扫描。
a）在唯一索引列上使用了range操作符（>,<,>=,<=,between）
select * from emp1 where empno >7788;
select * from emp1 where empno <>7788;
b)在唯一组合索引上，对组合索引使用部分列进行查询（含引导列），导致查询出多行
select * from emp1 where deptno =20;
c）对非唯一索引列上进行的任何查询。不含 布尔或
create index index_emp1_sal on emp1(sal);
select * from emp1 where sal =800;


索引全扫描
对整个index进行扫描，并且顺序的读取其中的数据
select empno  from emp1;
alter table emp1 modify empno not null;
select empno  from emp1;
索引快速扫描
扫描索引中的所有数据块
drop table emp1;
create table emp1 as select * from emp;
alter table emp1 modify empno not null;
create index index_emp1_empno on emp1(empno);
insert into emp1 select * from emp1;
select empno from emp1;


show parameter multi;






索引的碎片问题
聚簇因子：堆表的表行物理的存储在数据块是无序的，这与插入一行记录首选空闲快的策略有关。而索引的键值又是有序的。当这两者差异越大,聚簇因子的值就越高。




由于对基表做dml操作，便导致对索引表块的自动更改操作，尤其是基表的delete操作会引起index的逻辑删除。只有当index块中的所有index entry都被删除了，这个块才能够被回收，
如果update基表索引列，则索引块会发生entry delete，再entry insert，这些动作都可能产生索引碎片。


create table t (id int);
create index index_t_id on t(id);
begin
  for i in 1..1000000 loop
      insert into t values(i);
      if mod(i,100) = 0 then
         commit;
       end if;
  end loop;
end;


analyze index index_t_id validate structure;
select name,height,pct_used,del_lf_rows/lf_rows from index_stats
delete from t where id < 700000;
analyze index index_t_id validate structure;
select name,height,pct_used,del_lf_rows/lf_rows from index_stats
当下列三种情形之一发生时，说明积累的碎片应该整理了
1.height >=4
2.pct_used < 50%
3 delete_lf_rows/lf_rows >0.2


alter index index_t_id rebuild;//重建
analyze index index_t_id validate structure;
select name,height,pct_used,del_lf_rows/lf_rows from index_stats;


alter index index_t_id coalesce//比rebuild动作轻 ，融合
索引不可见（invisible）
alter index index_t_id invisible;//优化器不可见,索引正常更新
select * from user_indexes//visibility是invisible
alter index index_t_id unusable;
select * from user_indexes //status是unusable








约束


约束是数据库能够实现业务规则以及保证数据遵循ER模型的一种手段。


约束的语法
列级约束：只能引用一个列，表中可以有多个列级约束
表级约束：引用一个或多个列，通常用来定义主键
追加约束:建表后，再通过alter table追加约束




select * from user_constraints；
select * from user_cons_columns；




五种约束的语法
非空约束
列级定义
create table student(no int not null,name varchar(40));


追加非空约束
alter table emp1 modify ename not null;
select * from user_constraints
alter table emp1 modify ename null
alter table emp1 modify ename constraint c_emp1_ename not null;
select * from user_constraints


唯一性约束


列级定义
drop table student;
create table student(no int unique,name varchar(40));
insert into student
values(null,'zzl');
insert into student
values(null,'z');
insert into student
values(1,'zzl');
insert into student
values(1,'z');
表级定义
drop table student;
create table student(no int ,name varchar(40),constraint c_uno unique(no));




追加定义
drop table student;
create table student(no int ,name varchar(40));
alter table student add constraint c_uno unique(no);




主键约束
每个表只能建立一个主键约束 primary key = unique + not null
主键列上需要索引，如果该列没有索引，则自动创建unique索引，
主键约束和唯一约束不能同时建立在一个列上


主键约束的六种写法；
列级定义
drop table student
create table student(no int primary key,name varchar(40));
drop table student
create table student(no int constraint pk_student primary key,name varchar(40));
表级定义
drop table student
create table student(no int,name varchar(40),primary key (no));
drop table student
create table student(no int,name varchar(40),constraint pk_studnet primary key (no));


追加定义
drop table student;
create table student(no int,name varchar(40));
alter table student add primary key (no);
drop table student;
create table student(no int,name varchar(40));
alter table student add constraint pk_student primary key (no);






主键和索引关联的问题
drop table student
create table student(no int ,name varchar(40));
create index index_student_no on student(no);
alter table student add constraint pk_no primary key (no);
drop table student;
create table student(no int ,name varchar(40));
create index index_student_no on student(no);
alter table student add constraint pk_no primary key (no) using index index_student_no;
alter table student drop constraint pk_no;
select * from user_indexes where table_name ='STUDENT';


create table student(no int,name varchar(40),constraint pk_studnet primary key (no) using index(create index index_student_no on student(no)
));






外键约束
作用，是为了和同一个表或其他表的主键建立连接关系，外键值必须和父表中的值匹配或者为空


外键约束和unique约束都可以有空值
外键需要参考主键的约束，但也可以参考唯一键约束
外键和主键一般分别在两个不同的表中，但也可以同处在一个表中




drop table emp1;
create table emp1 as select * from emp;
create table dept1 as select * from dept;


列级定义：
alter table dept1 add constraint pk_dept1 primary key (deptno);
create table emp2 (empno int ,deptno int references dept1(deptno),deptno2 int);


表级定义
create table emp3(empno int ,deptno int,foreign key (deptno) references dept1(deptno));


追加定义
alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno);




insert into emp3 values(1,99);


insert into emp3 values(1,null);


delete from dept1 where deptno =10;
delete from dept1 where deptno =10;
alter table emp1 drop constraint fk_emp1;
alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete cascade;


delete from dept1 where deptno =10;
select * from emp1;


alter table emp1 drop constraint fk_emp1;
alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete set null;


delete from dept1 where deptno =10;
select * from emp1;


check 约束
列级定义
create table emp4(empno int ,sal int check(sal>0),comm int)
表级定义
create table emp5(empno int ,sal int,comm int， check(sal>5000))
追加定义
alter table emp5 add constraint e_no_ck check(empno is not null)


insert into emp5
values(null,1,1);


check约束中的表达式中不能使用变量日期函数
alter table emp1 add constraint emp_chk_date check (hiredate < sysdate);
alter table emp1 add constraint emp_chk_date check (hiredate < to_date('2016.08.16','yyyy.mm.dd'))




级联约束
drop table test;
create table test(
pk number primary key,
fk number,
col1 number,
col2 number,
constraint fk_constraint foreign key (fk) references test,
constraint ck1 check(pk >0 and col1 >0),
constraint ck2 check(col2 > 0)
)




alter table test drop column col2;


alter table test drop column fk;
alter table test drop column pk;
alter table test drop column col1;


alter table test drop column pk cascade constraint//级联


alter table test drop column col1 cascade constraint






约束的四种状态
enable validate :无法输入违反约束的行，而且表中所有行都要符合约束
enable novalidate：表中可以存在不符合约束的状态，但对新加入数据必须符合约束条件
disable novalidate：可以输入任何数据，表中或已存在不符合约束条件的数据
disable validate：不能对表进行插入，更新，删除等操作，相当于对整个表的read only 设定。


drop table emp1;
create table emp1 as select * from emp;


update emp1 set empno =NULL where empno =7900


select * from emp1
alter table emp1 add constraint ck_emp1 check (empno is not null);
alter table emp1 add constraint ck_emp1 check (empno is not null) enable novalidate;
insert into emp1(empno)
values(null);


将disable novalidate,enable novalidate,enable validate 三种状态组合起来使用
这种组合，可以避免因有个别不符合条件的数据，而导致大数据量的传输失败
假设a表示源数据，其中有空值，b表示a表的归档表，设有非空约束，现将a表插入到b表中
alter table b modify constraint b_nnl disable ,novalidate;
insert into b select * from a;
alter table b modify constraint b_nnl enable,novalidate;
update b set channel = 'NOT KNOWN' where channel is null;
alter table b modify constraint b_nnl enable ,validate;






延迟约束
alter table emp1 add constraint chk_sal check(sal > 500) deferrable;//延迟约束
insert into emp1(empno,sal)
values(3030,100);


set constraint chk_sal immediate;
insert into emp1(empno,sal)
values(3030,100)


set constraint chk_sal deferred;//延迟
insert into emp1(empno,sal)
values(3030,100)




alter table emp1 drop constraint chk_sal;
alter table emp1 add constraint chk_sal check(sal > 500) deferrable initially immediate;
insert into emp1(empno,sal)
values(3030,100);


alter table emp1 drop constraint chk_sal;
alter table emp1 add constraint chk_sal check(sal > 500) deferrable initially deferred;
insert into emp1(empno,sal)
values(3030,100);








用户访问控制
创建和管理数据库用户


查看数据库用户
select * from dba_users;




模式 schema


showuser


用户缺省表空间
select * from v$tablespace
create user zzl identified by bsoft;
缺省表空间


select * from database_properties


alter  database default tablespace tablespace_name




空间配额
配额 (quota)是表空间中为用户的对象使用的空间量
alter user zzl quota 10M on users;
alter user zzl quota
alter user zzl quota 0 on users;




管理概要文件(profile)
作用是对用户访问数据库做一些限制