SQL优化

order by 1 desc 要以哪列排序就标注第几列
select deptno,count(*) from dept group by deptno order by 1 desc
当查询结果返回的是返回表中5%以内的数据时,应该走索引，如果查过了，应该走全表扫描
某个列唯一键的数量叫做基数
-- 先对该表所有列不收集直方图


begin
  dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'EMP',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1',
                                no_invalidate    => false,
                                degree           => 1,
                                cascade          => true);
end;

 -- 收集直方图
begin
  dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'EMP',
                                estimate_percent => 100,
                                method_opt       => 'for columns EMPNO size skewonly',
                                no_invalidate    => false,
                                degree           => 1,
                                cascade          => true);
end;

select a.column_name,
       b.num_rows,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows * 100, 2) selectivity,
       a.histogram,
       a.num_buckets
  from dba_tab_col_statistics a, dba_tables b
 where a.owner = b.owner
   and a.table_name = b.table_name
   and a.owner = 'SCOTT'
   and a.table_name = 'EMP'
   order by 3 desc


执行：刷新数据库监控信息
begin
  dbms_stats.flush_database_monitoring_info;
end;
刷新后执行查询
select r.name            owner,
       o.name            table_name,
       c.name            column_name,
       equality_preds, --等值过滤
       equijoin_preds, --等值join 比如 where a.id=b.id
       nonequijoin_preds, --不等 join
       range_preds, --范围过滤次数 > >= < <= between and
       like_preds, --like过滤
       null_preds, --null 过滤
       timestamp
  from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
 where o.obj# = u.obj#
   and c.obj# = u.obj#
   and c.col# = u.intcol#
   and r.name = 'SCOTT'
   and o.name = 'EMP';
SQL优化脚本
select owner,
       column_name,
       num_rows,
       Cardinality 基数,
       selectivity 选择性,
       'Need index' as notice
  from (select b.owner,
               a.table_name,
               a.column_name,
               b.num_rows,
               a.num_distinct Cardinality,
               round(a.num_distinct / b.num_rows * 100, 2) selectivity
          from dba_tab_col_statistics a, dba_tables b
         where a.owner = b.owner
           and a.table_name = b.table_name
           and a.owner = 'SCOTT'
           and a.table_name = 'EMP'
         order by 3 desc) x
 where selectivity >= 20
   and column_name not in
       (select column_name
          from dba_ind_columns
         where table_owner = x.owner
           and table_name = x.table_name)
   and column_name in
       (select c.name
          from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
         where o.obj# = u.obj#
           and c.obj# = u.obj#
           and c.col# = u.intcol#
           and r.name = x.owner
           and o.name = x.table_name)
查看执行计划
explain plan for select e.ename,e.job,d.dname from emp e,dept d where e.deptno = d.deptno and e.sal <2000;

select * from table(dbms_xplan.display);

索引
-- INDEX FAST FULL SCAN  快速全扫描
-- TABLE ACCESS FULL 全表扫描
-- TABLE ACCESS BY USER ROWID  直接用ROWID获取数据
-- TABLE ACCESS BY ROWID RANGE ROWID范围扫描
-- TABLE ACCESS BY INDEX ROWID 表示回表,单块读
-- INDEX UNIQUE SCAN 索引唯一扫描,单块读
-- INDEX RANGE SCAN  索引范围扫描
-- INDEX SKIP SCAN 索引跳跃扫描（原因是跳过了组合索引的引导列）
-- INDEX FULL SCAN 索引全扫描(单块读)
-- INDEX FAST FULL SCAN 索引快速全扫描(多块读)
-- INDEX FULL SCAN (MIN/MAX) 表示索引最小/最大值扫描、单块读;性能与INDEX UNIQUE SCAN一样,仅次于TABLE ACCESS BY USER ROWID 
由于null引起的全表扫描
select MIN(logtime),MAX(logtime) from YCT_LOG t WHERE logtime IS NOT NULL
在Btree索引中,我们要连带的扫描最大值与最小值中间的叶子块，而不会走INDEX FULL SCAN。如果该SQL走索引，会走INDEX FAST FULL SCAN（多块读）
-- 我们将SQL进行等价改写,访问两次索引，一次最大，一次最小，从而避免扫描不需要的索引叶子块
SELECT (SELECT MAX(AAB001) FROM Ab01),(SELECT MAX(aab001) FROM Ab01 ) FROM dual
建议返回表中总行数5%以内数据，走索引扫描，超过5%走全表扫描
高并发情况下，递增列索引会引起索引热点块争用，我们对于递增索引可进行反转，此时索引集群因子变大，回表会有严重的性能问题
单进程批量INSERT，不推荐在非递增列建立索引；高并发INSERT一般电话号码等非递增列需要建立索引

select /*+ gather_plan_statistics use_nl(w,d) leading(e) */
 e.ename, e.job, d.dname
  from emp e, dept d
 where e.deptno = d.deptno


select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

嵌套循环（NESTED LOOPS）

1.嵌套循环,被驱动表必须走索引
2.嵌套循环，被驱动表只能走INDEX UNIQUE SCAN（索引唯一扫描）与INDEX RANGE SCAN(索引范围扫描)，被驱动表不能走TABLE ACCESS FULL(全表扫描)、INDEX FULL SCAN（索引全扫描）、INDEX SKIP SCAN（索引跳跃扫描）、INDEX FAST FULL SCAN（索引快速扫描）
-- 嵌套循环
declare
  cursor cur_emp is
    select ename, job, deptno from emp;

  v_dname dept.dname%type;
begin
  for x in cur_emp loop
    select dname into v_dname from dept where deptno = x.deptno;
    dbms_output.put_line(x.ename || ' ' || x.job || ' ' || v_dname);
  end loop;
end;
3.HASH连接：如果外连接中从表有过滤条件，那么外连接会变为内连接
select * from dept d left join emp e on d.deptno = e.deptno
where e.sal <  3000

SELECT A.AAB001
  FROM AB01 A
  LEFT JOIN HJSQ H
    ON A.AAB001 = H.AAB001
 WHERE H.TFA205 = '20'
4.如果两个表是1：N关系，驱动表1，被驱动表N并且N很大
    这时即使驱动表返回数据量很少，也不能走嵌套循环（NL），因为两表关联之后的数据量会很多
如果两表关联之后返回的数据量很少，可以走Nested Loops；
反之返回的数据量很多，应该走HASH连接（JOIN）
5.提问：大表是否可以当嵌套循环（NL）的驱动表？
   可以，如果大表过滤之后的数据量很少就可以当NL驱动表
6.select * from a,b where a.id = b.id;如果a有100条数据，b有100w数据，a与b是1：N的关系，
N很低，怎么优化SQL？
-- 注意审题：N很低，所有可以使用Nested Loops
-- 因为嵌套循环，被驱动表必须走索引，我们可以在b的连接列（b.id）上建立索引。让a与b走嵌套循环
    这样b表会被扫描100次，但是每次扫描走的都是id列的索引（范围扫描）
-- 如果a与b进行HASH连接，b表会被全表扫描（因为没有过滤条件）
-- 一般情况下，一个小表与一个大表关联，我们可以考虑让小表NL（嵌套循环）大表，大表走连接索引
    （如果大表有过滤条件，需要将过滤条件与连接列组合起来创建组合索引，避免大表被全表扫描）

HASH连接（HASH JOIN）

  1.两表等值关联，返回大量数据，将较小的表选为驱动表；执行计划中离HASH连接关键字最近的表就是驱动表
  2.再次强调：嵌套循环被驱动表需要扫描多次，而HASH连接的被驱动表只需要扫描一次
  3.HASH连接的驱动表与被驱动表的连接列都不需要创建索引
  4.将需要的列放入SELECT LIST中，这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间

/*+FULL(D1)*/ 强制全表扫描
/*+ use_nl(a2,a1) leading(a2)*/  强制嵌套循环  leading是强制驱动表
/*+ use_hash(k,l) leading(k)*/  强制HASH内连接
/*+ use_hash(k,l) swap_join_inputs(l)*/  强制修改HASH外连接的驱动表
 /*+ opt_param('_optimizer_mjc_enabled','false')*/ 禁用笛卡尔积
-- /*+ use_nl(a2,a1) leading(a2)*/ 强制嵌套循环  leading是强制驱动表
SELECT /*+ use_nl(a2,a1) leading(a2)*/ * FROM AB02 A2 JOIN AB01 A1 ON A2.AAB001 = A1.AAB001
-- /*+FULL(D1)*/ 强制全表扫描
SELECT /*+FULL(D1)*/ D1.AAB001, A1.AAB004 FROM AB02 D1, AB01 A1  WHERE D1.AAB001 >= A1.AAB001
--  /*+ use_hash(k,l) leading(k)*/  强制HASH内连接
SELECT /*+ use_hash(k,l) leading(k)*/ * FROM kc25 k JOIN lc07 l ON l.aaca07 = k.aaca07
-- /*+ use_hash(k,l) swap_join_inputs(l)*/ 强制修改HASH外连接的驱动表
SELECT /*+ use_hash(k,l) swap_join_inputs(l)*/ * FROM kc25 k LEFT JOIN lc07 l ON l.aaca07 = k.aaca07

笛卡尔积连接（MERGE JOIN CARTESIAN）

 1.两个表关联没有连接条件的时候，会产生笛卡尔积，这种连接方式就叫做笛卡尔积连接
 2.笛卡尔积连接会对两表中其中一个表进行排序，执行计划中的BUFFER SORT就表示排序
 3.当执行计划中有笛卡尔积连接怎么优化？
首先检查是否有关联条件；其次检查笛卡尔积连接最近的表是否真的返回一行数据，如果返回行数真的只有一行，那么走笛卡尔积连接是没有问题的，
如果返回行数超过一行，需要检查错误并纠正
使用  /*+ opt_param('_optimizer_mjc_enabled','false')*/ 禁用笛卡尔积
SELECT /*+ opt_param('_optimizer_mjc_enabled','false')*/ D1.AAB001, A1.AAB004 FROM AB02 D1, AB01 A1
标量子查询（SCALAR SUBQUERY）
 1.假如主表返回大量数据，主表的连接列基数很高，那么子查询的表会被反复扫描多次，严重影响SQL性能
  2.如果主表的连接列是外键，而子查询的连接列是主键，那就没必要改写外连接了
-- 数据分布不均衡导致性能问题
SELECT COUNT(DISTINCT OWNER), COUNT(DISTINCT OBJECT_NAME)
  FROM Z1
 WHERE OWNER IN (SELECT OWNER FROM DBA_OBJECTS);

SELECT COUNT(DISTINCT OWNER), COUNT(DISTINCT OBJECT_NAME)
  FROM (SELECT OWNER, OBJECT_NAME
          FROM Z1
         WHERE OWNER IN (SELECT OWNER FROM DBA_OBJECTS)
           AND ROWNUM > 0);

半连接（SEMI JOIN）

 1.如果半连接中主表属于1的关系，子表（子查询中的表）属于n的关系，我们再改写内连接的时候，需要加GROUP BY 去重，这时半连接性能高于内连接
 -- 半连接  
   SELECT * FROM ab02 WHERE aab001 IN (SELECT aab001 FROM hjsq)
-- 内连接   
   SELECT * FROM ab02,(SELECT aab001 FROM hjsq GROUP BY aab001) h1 WHERE ab02.aab001 = h1.aab001

/*+ use_nl(ab02@a2, a1) leading(a1)*/ 强制嵌套循环 leading为驱动表
  SELECT /*+ use_nl(ab02@a2, a1) leading(a1)*/ * FROM ab01 a1 WHERE aab001 IN (SELECT /*+ qb_name(a2)*/ aab001 FROM ab02)

反连接（ANTI JOIN）

   1.需要注意的是使用NOT IN里面有null，整个查询会返回空（注意！！！）
-- 正确用法not in
    SELECT * FROM lb09 WHERE aab001 NOT IN (SELECT aab001 FROM lb07 where aab001 is not null) 
SELECT * FROM lb09 WHERE NOT EXISTS (SELECT 1 FROM lb07 WHERE lb09.aab001 = lb07.aab001)
   2.将反连接等价改写HASH JOIN ANIT
SELECT LB09.AAB001 FROM LB09 LEFT JOIN LB07 ON LB09.AAB001 = LB07.AAB001 WHERE LB07.AAB001 IS NULL