《基于ORACLE SQL优化》读书笔记-特殊执行计划

AND-EQUAL(INDEX MERGE):多个单列等值条件 +每个列上有索引。示例中数据量少。不加HINT走的是全表扫，如果数据量大，而且那两列的选择性好，应当不用加HINT。

INDEX JOIN:针对单表不同索引的连接。 （在没有联合索引情况下）要取代的是全表扫，原表数据不能有NULL值。列有属性NOTNULL。

VIEW：按能否做视图合并分为两种。不做合并在执行计划中会出现VIEW字样。

FILTER（执行计划）：一种改良的嵌套循环连接，并非驱动结果集中有多少记录就要访问多少次被驱动表。

SORT:

1.      SORT AGGREGATE 不一定会有排序。

2.      SORT UNIQUE:既要排序又要去重。

3.      SORT JOIN:SORT MERGE JOIN的第一步

4.      SORT ORDER BY:只需要 ORDER BY

5.      SORT GROUP BY：排序加分组

6.      BUFFER SORT:ORACLE会借用PGA把扫描结果LOAD进去，结省缓存在SGA中带来的种种开销。 统计信息中的SORT(MEMEORY),SORT(DISK)不准确，要看真实执行计划中的COLUMNPROJECTION INFORMATION 中#KEYS值来判断，大于0表示有排序。它紧跟着的为排序的列。

UNION/UNION-ALL:UNION 先做UNION ALL 然后做SORTUNIQUE。

CONNECT BY:层次查询关键字

示例：

 

create index IDX_EMP_MGR on emp(mgr);
create index idx_deptno on emp(deptno);
select/*+ and_equal(emp IDX_EMP_MGR idx_deptno)*/ empno,jobfrom emp where mgr =7092 and deptno =20;
---- VIEW
create view emp_mgr_view as select * from emp where job ='MANAGER';
select empno,sal from emp_mgr_view where ename ='JONES';
create or replace view emp_mgr_view as select * from empwhere job = 'MANAGER' and rownum<10;
select empno,sal from emp_mgr_view where ename ='JONES';
----- FILTER
select * from t21;
select * from t22;
insert into t21 values(4,'C');
commit;
select/*+gather_plan_statistics */ * from t21 where col2 in(select /*+ no_unnest*/ col2 from t22);
select * fromtable(dbms_xplan.display_cursor(null,null,'IOSTATS COST'));
 
------ SORT
select sum(sal) from emp where job ='MANAGER';-- SORTAGGREGATE
select distinct ename from emp where job ='MANAGER' order byename;-- SORT UNIQUE
select /*+ use_merge(t1 t2)*/ t1.empno ,t1.ename,t2.sal fromemp t1,emp t2 where t1.empno =t2.empno; --SORT JOIN
select ename from emp where job ='MANAGER' order by ename;--SORT ORDER BY
select ename from emp where job ='MANAGER' group by enameorder by ename; --SORT GROUP BY
select t1.empno,t2.ename from  emp t1,emp t2;-- BUFFER SORT
 
 
select * from emp order by ename;
select sql_text,sql_id ,child_number from v$sql wheresql_text = 'select * from emp order by ename';
select * fromtable(dbms_xplan.display_cursor('aa4x3rfrugzxd',0,'advanced'));
 
 
alter session set events '10032 trace name context forever'
select * from emp order by ename;
@tracefile
 
---- UNION/UNION ALL
select empno,ename from emp union all select empno,enamefrom emp;--UNION ALL
select empno,ename from emp union  select empno,ename from emp;--UNION