初识Oracle day15(sql语句的优化等相关的知识)

1-【常规SQL语句优化】 truncate 写出存储过程动态删除表的SQL并加注释…

--truncate语法结构：
Truncate [table | cluster] schema.[table_name] [cluster_name] [drop | reuse storage]

 --创建一个存储过程，传入一个表示表名称的参数，实际清空指定的表
create or repalce  procedure trun_table(table_deleted in varchar2) as
cur_name integer;--定义内部变量，存储打开的游标
begin
--打开游标
cur_name:=dbms_sql.open_cursor;
--执行truncate table tb_name命令，从而实现清空指定的表
dbms_sql.parse(cur_name,'truncate table'||table_deleted||'drop storage',dbms_sql.native);
--关闭游标
dbms_sql.close_cursor(cur_name);
exception
when others then 
--出现异常关闭游标
dbms_sql.close_cursor(cur_name);
raise;
end trun_table;
/

2-commit释放哪些资源?列出4条…
COMMIT所释放的资源如下。
（1）回滚段上用于恢复数据的信息，撤销表空间也只做短暂的保留
（2）被程序语句获得的锁
（3）redo log buffer中的空间
（4）Oracle为管理上述资源的内部花费
3-查询A和B表，A字段没索引，B字段有索引，哪个表作为驱动表比较合适？为什么？你能总结出什么？
A表比较适合作为驱动表
因为只有两个表都建立有索引，优化器才能按照紧随from关键字后面的驱动表的规则来对待。

--从Students表和Department表查询学生信息，代码如下
SQL> select s.Name,d.Dept_Name
  2  from Department d ,Students s
  3  where d.Dept_No = s.Dept_No;

在上面的代码中，假设在Students表的dept_no列创建了索引，而在Department表的dept_no列没有索引。由于Department最先被访问（紧随from其后），这样Department表将被作为查询中的驱动表，由此可见，只有两个表都建立有索引，优化器才能按照紧随from关键字后面的驱动表的规则来对待。

注： Oracle采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前。那些可以过滤掉最大数据记录的条件必须写在WHERE子句的末尾，也就是在表进行连接操作以前，过滤掉的记录数越多越好。

4-创建索引事务基本原则？
原则：
（1）以查询关键字为基础，表中的行随机排序。
（2）包含的列数相对比较少的表。
（3）表中的大多数查询都包含相对简单的where从句。
（4）对于经常以查询关键字为基础的表，并且该表中的行遵从均匀分布
（5）缓存命中率低，并且不需要操作系统权限。
5-写出创建复合索引的示例SQL,并写出自己的总结…

多列索引也叫做复合索引，复合索引有时比单列索引有更好的性能。如果在建立索引时采用了几个列作为索引，则在使用时也要按照建立时的顺序来描述，也就是说，主列是最先被选择的列，下面来看一个例子。

例如，为tb_test表创建一个复合索引complex_index，该索引包括column1、column2、column3个列（并且建立顺序亦此），如果把这3列作为where查询条件，那么这3个列的最优排序方式如下：即创建的时候顺序和查询的顺序保持一致能够提高查询的效率。

--为tb_test表创建一个复合索引complex_index，
SQL> create index complex_index on tb_test(column1,column2,column3)
--如果把这3列作为where查询条件，那么这3个列的最优排序方式如下
SQL> select * from tb_test where column1 > 0 and column2 > 0 and column3 < 0

6-查看整车测试数据库，找出一个你认为需要建立复合索引的表，并给出SQL代码…

在一个或者一些字段需要频繁用作查询条件，并且表数据较多的时候，创建索引会明显提高查询速度，因为可由全表扫描改成索引扫描。(无索引时全表扫描也就是要逐条扫描全部记录，直到找完符合条件的，索引扫描可以直接定位)
索引并不是越多越好，太多索引会占用很多的索引表空间，甚至比存储一条记录更多。

经过最近一段的学习，发现发运信息表经常用到，并且低于经常查询用到的几列（时间，底盘号，状态）建立复合索引。这对于提高以后的查询效率有很大的帮助

create index complex_index on mid_vehicle.v_dms_03 (create_date,vin,status);
select *from mid_vehicle.v_dms_03 
where create_date='想要查询的时间'  
and vin='想要查询的底盘号'
and status='当前订单处于的状态'

7-explain plan 优化器的作用是什么？
explain plain能够使系统生成某个SQL语句的执行计划

基本语法：
explain plan [set statement_id [=] <string literal>]
      [into 表的名称]
      for 要优化的sql语句