oracle-索引优化

day 05

索引：index  => 目录	
	数据库会在具有唯一性的列上自动添加唯一性索引		      //主键就是唯一字段，自动添加唯一性索引
							//可以添加多个索引
如何创建索引
	create index 索引名 on 表名(字段);
	create index index_name on student(name);

查询索引
	select index_name,table_name,uniqueness,status from user_indexes
	where table_name like 'STUDENT';
修改索引
	alter index index_name rename to new_index_name;
删除索引
	drop index 索引名;
	drop index new_index_name;
*****************************
索引类型：
普通索引：normal
		create index 索引名 on 表名（列名）;
唯一性索引：unique
		create unique index 索引名 on 表名（列名）;
位图索引（分类）:bitmap
	这种索引适合用在数据量比较大，基数比较小的列  比如：男/女/。。
		create bitmap index 索引名 on 表名（列名）;
函数索引：在一个列上经过函数计算后的结果上创建索引
		create index 索引名 on 表名（函数（列名））;
~~~~
普通索引normal：加快访问速度，索引列允许值重复
	create index 索引名 on 表名（列名）;
唯一索引unique：不允许值重复
	create unique index 索引名 on 表名（列名）;
位图索引（分类）:bitmap
	这种索引适合用在数据量比较大，基数比较小的列  比如：男/女/。。
函数索引：在一个列上经过函数计算后的结果上创建索引
		create index 索引名 on 表名（函数（列名））;



创建索引的优缺点：					//重点
	能够更快的帮助我们进行提高查询效率
	增删改表中的数据，数据库就需要耗费资源去维护索引，降低了增删改的效率
	数据量如果很少，没必要加索引
	数据量比较大，不需要经常增删改操作的而且查询比较多，适合使用索引
***********************************
存储过程：procedure
	在服务器端，能够被一个或者多个应用程序调用的一段sql语句集		//调用sql语句

创建存储过程
	create or replace procedure 
	过程名(参数名 in 参数类型,参数名 in 参数类型,参数名 out 参数类型,参数名 out 参数类型,)
	as
	变量名 变量类型 := 值；
	begin
	sql语句集;
	end;

存储过程可以没有参数，如果没有参数则过程名后不能出现括号
存储过程可以有参数
传入参数用in标明，传出参数用out标明
可以有多个传入参数，也可以有多个传出参数
可以只有传入参数，也可以只有传出参数
存储过程没有返回值，而是通过返回参数来返回数据的

如果传入参数是1，则返回你好
如果传入参数是2，则返回再见

create or replace procedure
pro_hi(mykey in number,value out varchar)
as
begin
if mykey = 1
then value := '你好';
else if mykey = 2
then value := '再见';
end if;
end if;
end;
/

调用存储过程
	declare
	变量  类型:=初始值；
	begin
	过程名(参数，变量);
	end;


开启
	set serveroutput on;

declare
val varchar2(20):='';
begin
pro_hi(1,val);
dbms_output.put_line(val);
pro_hi(2,val);
dbms_output.put_line(val);
end;
/

触发器：trigger
	for each row:行级触发器的标志

创建触发器：
	create or replace trigger 触发器名
	before/after  insert/update/delete  on 表名
	for each row
	begin
	sql语句集;
	end;

--创建表
create table test4(
id int primary key,
name varchar2(20)
 );
--创建序列
create sequence seq_id;
--创建触发器
create or replace trigger tri_insert_test4
before insert on test4
for each row
begin
select seq_id.nextval
into:new.id
from dual;--从序列中生成一个新的数值，赋值给当前插入行的id列
end;
/
--增加数据，触发器
insert into test4(name) values ('香蕉');
insert into test4(name) values ('苹果');
--查询数据结果
select * from test4;

--创建日志表
create table test_log(
name varchar2(20),
time date
);
--创建触发器
create or replace trigger test_dept
before delete or insert or update on dept
declare var_tag varchar2(20);
begin
if inserting then
var_tag := 'insert';
elsif updating then
var_tag := 'update';
elsif deleting then
var_tag := 'delete';
end if;
insert into test_log values(var_tag,sysdate);
end;
/
--分别执行语句对dept表进行操作
insert into dept values(60,'教学部','济南');
update dept set loc = '易途' where deptno = 60;
delete from dept where deptno = 60;
--查询
select * from dept;
select * from test_log;
****************************
distinct和group by的区别：
	distinct：去重
		支持单列，多列的去重方式
			select distinct sid from student;
			select distinct sid,name from student;
	group by：聚合统计

sql语句优化：
	1.建议不用‘*’来代替所有列名
	2.用truncate代替delete
	3.在确保语句完整的情况下多用commit语句(用在begin..end中)
	4.尽量减少表的查询次数
	5.用not exists代替 not in(少用not in)
	6.where之后尽量少用表连接，数据多的的记录在where字句的末尾，表连接之前过滤掉的数据越多越好（表连接在where条件之前）
	7.合理使用索引（详见索引）
		根据需要，合理选择索引类型
		数据量如果很少，没必要加索引
		数据量比较大，不需要经常增删改操作的而且查询比较多，适合使用索引	
	8.sql语句尽量用大写的，Oracle总是先解析语句，把小写的转换成大写在执行
	9.连接多个表的时候尽量使用表的别名，减少解析时间
	10.优化group by，将不需要的记录在group by之前过滤掉，避免使用having，效率太低

************************************
视图节省编译时间，效率高
some/any/all：效率较低，用多表查询
in效率低：用exists代替
//自己写的便于记忆
sql语句的优化：
	尽量减少用 *
	truncate 代替delete	，效率高			//为什么：
	写完begin..end语句，多用commit，效率高
	--减少表查询次数，效率高
	not exists 代替 not in，效率高
	--、where之后少用表连接，数据多的记录在where的末尾，表连接之前过滤的越多越好
	合理使用索引，，效率高
		根据情况，选择适合情况的索引类型
		数据 多，增删改 少，查询 多，可以使用索引
	sql语句尽量大写，减少Oracle解析语句时间
	多个表 用别名，效率高
	--将不需要的记录在group by之前过滤，避免使用having，效率太低，效率高

索引类型：
普通索引：normal
		create index 索引名 on 表名（列名）;
唯一性索引：unique
		create unique index 索引名 on 表名（列名）;
位图索引（分类）:bitmap
	这种索引适合用在数据量比较大，基数比较小的列  比如：男/女/。。
		create bitmap index 索引名 on 表名（列名）;
函数索引：在一个列上经过函数计算后的结果上创建索引
		create index 索引名 on 表名（函数（列名））;

创建索引的优缺点：					//重点
	能够更快的帮助我们进行提高查询效率
	增删改表中的数据，数据库就需要耗费资源去维护索引，降低了增删改的效率
	数据量如果很少，没必要加索引
	数据量比较大，不需要经常增删改操作的而且查询比较多，适合使用索引