Oracle 11g 学习笔记-15（SQL优化）

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

SQL> create or replace procedure trun_table(table_deleted in varchar2) as
  2    cur_name integer;
  3  begin
  4    cur_name :=dbms_sql.open_cursor;
  5    dbms_sql.parse(cur_name,'truncate table'||table_deleted ||'drop storage',dbms_sql.native);
  6    dbms_sql.close_cursor(cur_name);
  7  exception
  8    when others then dbms_sql.close_cursor(cur_name);
  9    raise;
 10  end trun_table;
 11  /

过程已创建。

2-commit释放哪些资源?列出4条…
COMMIT所释放的资源如下：
（1）回滚段上用于恢复数据的信息，撤销表空间也只做短暂的保留
（2）被程序语句获得的锁
（3）redo log buffer中的空间
（4）Oracle为管理上述资源的内部花费

3-查询A和B表，A字段没索引，B字段有索引，哪个表作为驱动表比较合适？为什么？你能总结出什么？
B表作为驱动表比较合适。
驱动表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问)。对于多个表的连接，FROM子句中，选择最右的表作为驱动表(一般也就是from中最后的表作为驱动表)。
总结
(1)用于连接的子句的列应被索引、在Where子句中应尽量利用索引，而不是避开索引
(2)连接操作应从返回较少行上驱动
(3)如果所连接的表A和B，A表长度远远大于B表，建议从较大的A表上驱动
(4)如果Where子句中含有选择性条件，将最具有选择性部分放在表达式最后
(5)如果只有一个表有索引，另一表无索引，无索引的表通常作为驱动表。如A表的No列以被索引而B表的No列没被索引，则应当B表作为驱动表，A表作为被驱动表

4-建索引事务基本原则？
在利用索引的情况下，用于只从表中选择部分行，所以能够提高查询速度。对于只从总行数中查询2%~4%的表，可以考虑创建索引。下面是创建索引的基本原则：
（1）以查询关键字为基础，表中的行随机排序。
（2）包含的列数相对比较少的表。
（3）表中的大多数查询都包含相对简单的WHERE从句。
（4）对于经常以查询关键字为基础的表，并且该表中的行遵从均匀分布
（5）缓存命中率低，并且不需要操作系统权限。

5-写出创建复合索引的示例SQL,并写出自己的总结…

SQL> create index complex_index on tb_test(column1,column2,column3)
SQL> select * from tb_test where column1 > 0 and column2 > 0 and column3 < 0

总结：如果在建立索引时采用了几个列作为索引，则在使用时也要按照建立时的顺序，这样的语句执行效率会更高。
6-查看整车测试数据库，找出一个你认为需要建立复合索引的表，并给出SQL代码…

create index complex_index1 on v_dms_03 (vin,status);//在发运表中创建复合索引complex_index1。
select * from v_dms_03 
where  vin='V006'
and status='1'

7-explain plan 优化器的作用是什么？

（1）语法检查：检查SQL语句的拼写是否正确。
（2）语义分析：核实所有与数据字典不一致的表和列的名字。
（3）概要存储检查：检查数据字典，以确定该SQL语句的概要是否已经存在。
（4）生成执行计划：使用基于成本的优化规则和数据字典的统计表来决定最佳执行计划。
（5）建立二进制代码：基于执行计划，Oracle生成了二进制执行代码。

--语法
explain plan [set statement_ id [=] <string literal>]
[into <table_ name> ]
for <sql_ S tatment>

本章节适合开发人员学习，SQL优化从运维到开发都很适用。