此文是我最早开始sql优化至今整理的小知识点和经常遇到的问题，弄懂这些对优化大型的sql会有不少帮助
---------------------------------使用了多余的外连接-------------------------------------------------
使用多余的外连接
外连接是一个代价非常昂贵的执行过程。如果业务需要，这种操作是必要的，但是有时
候会出现人为的在SQL 中使用不必要的外连接，这实际上是因为有的开发人员担心遗漏一
些数据而刻意使用它，这就非常有可能留下隐患，让数据库选择昂贵的执行计划而不是最优
的那一个。
SQL> select * from t1;
A B
---------- ----------
1 101
2 102
3 103
4 104
5 105
6 106
7 107
8 108
9 109
已选择9 行。
SQL> select * from t2;
C D
---------- ----------
1001
2 1002
1003
4 1004
1005
6 1006
1007
8 1008
1009
已选择9 行。
通过下面这条语句，通过使用A 字段和T2 表C 字段关联，我们获取了T1 表上所有的
行以及T2 表上符合条件的行:
SQL> select a,b,c,d from t1,t2 where t1.a=t2.c(+) ;
A B C D
---------- ---------- ---------- ----------
2 102 2 1002
4 104 4 1004
6 106 6 1006
8 108 8 1008
1 101
3 103
5 105
7 107
9 109
SQL> select a,b,c,d from t1,t2 where t1.a=t2.c(+) and t2.d>1000;
A B C D
---------- --------- ---------- ----------
2 102 2 1002
4 104 4 1004
6 106 6 1006
8 108 8 1008
这条SQL 的意思是告诉数据库，我要得到T1 表上所有的行，并且用A 列和T2 表C
做关联，同时要求T2 表C 列的值大于1000.
让我们再看看另一条结果集完全一样的SQL：
SQL> select a,b,c,d from t1,t2 where t1.a=t2.c and t2.d>1000;
A B C D
---------- ---------- ---------- ----------
2 102 2 1002
4 104 4 1004
6 106 6 1006
8 108 8 1008
从结果集上来看，这是两条等价的SQL 语句，就是说，在这种情况下，外连接其实是
没有用的，是人为的在SQL 里设定的限制！如果仔细看一下第一条语句，我们不难发现，
条件中T2.C>1000 已经明确的指出，在结果集中，T2 表在任何一行，C 列都应该有值的，
也就是在这种情况下，根本就不需要使用外连接，业务逻辑上讲，外连接在这里是多余的。
这种情况在开发人员的代码中有时候会遇到，对他们来讲，只要保证结果集正确就好，但对
数据库来讲，在执行时可能会引起极大的性能差别。
---------------------------------all_rows / first_rows-------------------------------------------------
对于OLAP 系统，绝大多少时候数据库上运行着的是报表作业，执行基本上是聚合类的SQL
操作，比如GROUP BY，这时候，把优化器模式设置成all_rows 是恰当的。
而对于一些分页操作比较多的网站类数据库，设置成first_rows 会更好一些。
在SQL 里通过Hints 的方式来将优化模式转换成FIRST_ROWS
比如这样的一个每次取出10 条记录的分页查询：
Select * from
(SELECT /*+ first_rows(10) */ a.*,rownum rnum from
(SELECT /*+ first_rows(10) */ id,name from t1 order by id) a
Where rownum<=10)
Where rnum>=1;
alter session set optimizer_mode=all_rows;
alter session set optimizer_mode=first_rows;
---------------------------------join与left join-------------------------------------------------
当两表关联时，如a left join b，然后在b上有where条件，那么可去掉left。
不去掉left，会产生不必要的关联，严重的是，a left join b，一般会选择a表为驱动表，
这是为防止结果错误（当然优化器有可能看到where有b的条件去掉left）
无where时：
SQL> select * from t1
2 left join t2
3 on t1.a=t2.a
4 ;
A B A B
---------- ---------- ---------- ----------
2 2 2 2
3 3 3 3
1 1
SQL> select * from t1
2 join t2
3 on t1.a=t2.a
4 ;
A B A B
---------- ---------- ---------- ----------
2 2 2 2
3 3 3 3
where条件在t2表的关连建上：
SQL> select * from t1
2 join t2
3 on t1.a=t2.a
4 where t2.a=2
5 ;
A B A B
---------- ---------- ---------- ----------
2 2 2 2
SQL> select * from t1
2 left join t2
3 on t1.a=t2.a
4 where t2.a=2
5 ;
A B A B
---------- ---------- ---------- ----------
2 2 2 2
where条件在t2表的非关连建上：
SQL> select * from t1
2 join t2
3 on t1.a=t2.a
4 where t2.b=2
5 ;
A B A B
---------- ---------- ---------- ----------
2 2 2 2
SQL> select * from t1
2 left join t2
3 on t1.a=t2.a
4 where t2.b=2
5 ;
A B A B
---------- ---------- ---------- ----------
2 2 2 2
以下2种写法等价：
SQL> select * from t1
2 left join t2
3 on t1.a=t2.a
4 and t2.a=2
5 ;
A B A B
---------- ---------- ---------- ----------
2 2 2 2
3 3
1 1
SQL> select * from t1,t2 where t1.a=t2.a(+) and t2.a(+)=2;
A B A B
---------- ---------- ---------- ----------
2 2 2 2
3 3
1 1
left join on and on 与 left join on where 的区别：
第一种是：先根据on过滤，再关联
第二种是：先关联再根据where过滤
这2种写法意义是不同的，
inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的
实验：
创建2张表
SQL> select * from table1;
ID ID_NAME
---------- ----------
1 10
2 20
3 30
SQL> select * from table2;
ID_NAME NAME
---------- ----------
10 AAA
20 BBB
30 CCC
第一种写法：t1只和t2的AAA关联，但还是显示所有行
SQL> select * from table1 t1
2 left join table2 t2
3 on t1.id_name=t2.id_name
4 and t2.name='AAA';
ID ID_NAME ID_NAME NAME
---------- ---------- ---------- ----------
1 10 10 AAA
3 30
2 20
即使and使用t1，因为left join，也会留下所有行
SQL> select * from table1 t1
2 left join table2 t2
3 on t1.id_name=t2.id_name
4 and t1.id=1;
ID ID_NAME ID_NAME NAME
---------- ---------- ---------- ----------
1 10 10 AAA
2 20
3 30
SQL> select * from table1 t1
2 left join table2 t2
3 on t1.id_name=t2.id_name
4 and t1.id=0;
ID ID_NAME ID_NAME NAME
---------- ---------- ---------- ----------
2 20
3 30
1 10
第二种写法：t1和t2所有行关联，结果集再过滤，所以只留下1行
SQL> select * from table1 t1
2 left join table2 t2
3 on t1.id_name=t2.id_name
4 where t2.name='AAA';
ID ID_NAME ID_NAME NAME
---------- ---------- ---------- ----------
1 10 10 AAA
----------------------------------------------------- with as ---------------------------------------------------------------
select * from (
with a as (select 1 from dual union all select 1 from dual),
b as (select renshu from (select rownum renshu from a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a) where renshu>30000 and renshu<100000 )
select distinct DECODE(mod(renshu, 3),
1,
decode(mod(renshu, 5),
2,
decode(mod(renshu, 7),
4,
decode(mod(renshu, 11),
6,
decode(mod(renshu, 23), 8, renshu))))) renshu1
from b
) aa where aa.renshu1 is not null;
-----------------------------------------------------分析函数 ---------------------------------------------------------------
分析函数的功能：排名
1、按照3种方式为cnt列排名
SQL> with t as
2 (select rownum * 10 cnt from dual connect by rownum < 5
3 union all
4 select rownum * 40 - 10 from dual connect by rownum < 3)t 表构造出10，20，30，30，40，70
5 select cnt,
6 row_number() over(order by cnt) rn,//排名无并列，且每个排名与紧接着的下一个排名都是连续的row_number()，不需要参数
7 rank() over(order by cnt) rk,//排名有并列，且并列的排名与紧接着的下一个排名不连续rank()
8 dense_rank() over(order by cnt) drk//排名有并列，且并列的排名与紧接着的下一个排名连续dense_rank()
9 from t;
CNT RN RK DRK
---------- ---------- ---------- ----------
10 1 1 1
20 2 2 2
30 3 3 3
30 4 3 3
40 5 5 4
70 6 6 5
已选择6行。
2、分组排名：按照dept分组后，按照sale用3种方式排名
SQL> select dept_id,
2 sale_date,
3 goods_type,
4 sale_cnt,
5 row_number() over(partition by dept_id order by sale_cnt desc) rn,//分组的话，使用partition by dept，排名用order by sale
6 rank() over(partition by dept_id order by sale_cnt desc) rk,//partition、order by均可以有多列
7 dense_rank() over(partition by dept_id order by sale_cnt desc) drk
8 from lw_sales
9 where trunc(sale_date, 'MM') = date '2013-04-01' ;
DEPT_I SALE_DATE GOOD SALE_CNT RN RK DRK
------ -------------- ---- ---------- ---------- ---------- ----------
S00 18-4月 -13 G01 300 1 1 1
S01 20-4月 -13 G04 900 1 1 1
S01 13-4月 -13 G00 900 2 1 1
S01 15-4月 -13 G01 400 3 3 2
S01 07-4月 -13 G02 300 4 4 3
S01 03-4月 -13 G03 200 5 5 4
S02 05-4月 -13 G03 800 1 1 1
S02 05-4月 -13 G00 400 2 2 2
S02 22-4月 -13 G03 300 3 3 3
S02 06-4月 -13 G04 300 4 3 3
已选择10行。
分析函数的功能：相邻
–LAG 是取到排序后，向上相邻的记录
–LEAD 是取到排序后，向下相邻的记录
LAG/LEAD(v, n, dv) over(partition by a order by b):按照a分组后，每组按b排序，列出排序后v列前n行的值，没有找到则列出dv
例：先按deptno分组，再按sal排序，列出与ename相邻2行的ename，如果往上2行无数据为‘AAA'，往下2行无数据为‘ZZZ’：
SQL> select deptno,
2 ename,
3 sal,
4 lag(ename, 2, 'AAA') over(partition by deptno order by sal) lower_name,
5 lead(ename, 2, 'ZZZ') over(partition by deptno order by sal) higher_name
6 from scott.emp;
DEPTNO ENAME SAL LOWER_NAME HIGHER_NAM
---------- ---------- ---------- ---------- ----------
10 MILLER 1300 AAA KING
10 CLARK 2450 AAA ZZZ
10 KING 5000 MILLER ZZZ
20 SMITH 800 AAA FORD
20 JONES 2975 AAA ZZZ
20 FORD 3000 SMITH ZZZ
30 JAMES 950 AAA WARD
30 MARTIN 1250 AAA TURNER
30 WARD 1250 JAMES ALLEN
30 TURNER 1500 MARTIN BLAKE
30 ALLEN 1600 WARD ZZZ
DEPTNO ENAME SAL LOWER_NAME HIGHER_NAM
---------- ---------- ---------- ---------- ----------
30 BLAKE 2850 TURNER ZZZ
LiKun AAA ZZZ
已选择13行。
同样可实现相同的行，只输出一次，如上例中10只输出一次
SQL> select (case
2 when deptno = lag(deptno, 1, -1) over(partition by deptno order by ename) then//如果deptno=上一行的deptno，则输出null；
3 null
4 else//如果不等于，则表示是新数据，直接显示deptno
5 deptno
6 end) deptno,
7 ename,
8 sal,
9 lag(ename, 2, 'AAA') over(partition by deptno order by sal) lower_name,
10 lead(ename, 2, 'ZZZ') over(partition by deptno order by sal) higher_name
11 from scott.emp;
DEPTNO ENAME SAL LOWER_NAME HIGHER_NAM
---------- ---------- ---------- ---------- ----------
10 CLARK 2450 AAA ZZZ
KING 5000 MILLER ZZZ
MILLER 1300 AAA KING
20 FORD 3000 SMITH ZZZ
JONES 2975 AAA ZZZ
SMITH 800 AAA FORD
30 ALLEN 1600 WARD ZZZ
BLAKE 2850 TURNER ZZZ
JAMES 950 AAA WARD
MARTIN 1250 AAA TURNER
TURNER 1500 MARTIN BLAKE
WARD 1250 JAMES ALLEN
LiKun AAA ZZZ
已选择13行。
分析函数的功能：统计
sum(cnt) over(partition by a order by b) 按a分组后，按b的顺序对cnt进行累计
例如：求出每个部门按商品类型分共累计多少销售额，以及部门中按商品类型累计的销售额
SQL> with t as
2 (select dept_id,
3 goods_type,
4 sum(sale_cnt) goods_sale_cnt
5 from lw_sales
6 group by dept_id, goods_type)
7 select dept_id,
8 goods_type,
9 goods_sale_cnt,
10 sum(goods_sale_cnt) over(partition by dept_id order by goods_type) cum_goods_sale_cnt
11 from t;
DEPT_I GOOD GOODS_SALE_CNT CUM_GOODS_SALE_CNT
------ ---- -------------- ------------------
S00 G00 400 400
S00 G01 330 730
S00 G03 1000 1730
S00 G04 1000 2730
S00 G05 900 3630
S01 G00 1600 1600
S01 G01 800 2400
S01 G02 1400 3800
S01 G03 800 4600
S01 G04 2530 7130
S02 G00 400 400
S02 G01 270 670
S02 G02 900 1570
S02 G03 1100 2670
S02 G04 300 2970
S02 G05 200 3170
已选择16行。
avg() over()平均值
例如：求出每个部门每种货物的累计销售额，以及这种货物在各部门中的平均销售额，以及他们的差
SQL> with t as
2 (select dept_id, goods_type, sum(sale_cnt) goods_sale_cnt
3 from lw_sales
4 group by dept_id, goods_type)
5 select dept_id,
6 goods_type,
7 goods_sale_cnt,
8 round(AVG(goods_sale_cnt) over(partition by goods_type), 2) avg_goods_sale_cnt,
9 goods_sale_cnt -
10 round(AVG(goods_sale_cnt) over(partition by goods_type), 2) dv_goods_sale_cnt
11 from t;
DEPT_I GOOD GOODS_SALE_CNT AVG_GOODS_SALE_CNT DV_GOODS_SALE_CNT
------ ---- -------------- ------------------ -----------------
S00 G00 400 800 -400
S01 G00 1600 800 800
S02 G00 400 800 -400
S00 G01 330 466.67 -136.67
S01 G01 800 466.67 333.33
S02 G01 270 466.67 -196.67
S01 G02 1400 1150 250
S02 G02 900 1150 -250
S00 G03 1000 966.67 33.33
S01 G03 800 966.67 -166.67
S02 G03 1100 966.67 133.33
S00 G04 1000 1276.67 -276.67
S01 G04 2530 1276.67 1253.33
S02 G04 300 1276.67 -976.67
S00 G05 900 550 350
S02 G05 200 550 -350
已选择16行。
max(col1) over(partition by col2 order by col3):partition可选；order by可选，写上表示依次选出最大，不写表示组内所有值的最大
按月分组列出销售额的最高和最低
SQL> select dept_id,
2 to_char(sale_date, 'YYYY-MM') sale_month,
3 sum(sale_cnt) goods_sale_cnt，
4 max(sum(sale_cnt)) over(partition by to_char(sale_date, 'YYYY-MM')) max_gsc,
5 min(sum(sale_cnt)) over(partition by to_char(sale_date, 'YYYY-MM')) min_gsc
6 from lw_sales
7 where goods_type = 'G01'
8 group by dept_id, to_char(sale_date, 'YYYY-MM')
9 ;
DEPT_I SALE_MO GOODS_SALE_CNT MAX_GSC MIN_GSC
------ ------- -------------- ---------- ----------
S00 2013-03 30 400 30
S01 2013-03 400 400 30
S02 2013-03 270 400 30
S00 2013-04 300 400 300
S01 2013-04 400 400 300
-----------------------------------------------------函数索引 ---------------------------------------------------------------
SQL> select job,count(distinct deptno) from scott.emp where mgr is not null group by job;
JOB COUNT(DISTINCTDEPTNO)
--------- ---------------------
CLERK 3
SALESMAN 1
MANAGER 3
ANALYST 1
SQL> set autot trace
SQL> select job,count(distinct deptno) from scott.emp where mgr is not null group by job;
执行计划
----------------------------------------------------------
Plan hash value: 3818262728
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 105 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 5 | 105 | 4 (25)| 00:00:01 |
| 2 | VIEW | VM_NWVW_1 | 11 | 231 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 165 | 4 (25)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 11 | 165 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("MGR" IS NOT NULL)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> select job,count(distinct deptno) from scott.emp where nvl2(mgr,1,0)=1 group by job order by job;
执行计划
----------------------------------------------------------
Plan hash value: 2809461788
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 21 | 4 (25)| 00:00:01 |
| 2 | VIEW | VM_NWVW_1 | 1 | 21 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 15 | 4 (25)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 1 | 15 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(NVL2("MGR",1,0)=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
575 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> create index i_emp_mgr on scott.emp(nvl2(mgr,1,0));
索引已创建。
SQL> select job,count(distinct deptno) from scott.emp where nvl2(mgr,1,0)=1 group by job order by job;
执行计划
----------------------------------------------------------
Plan hash value: 1097451957
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 21 | 3 (34)| 00:00:01 |
| 2 | VIEW | VM_NWVW_1 | 1 | 21 | 3 (34)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 14 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_EMP_MGR | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(NVL2("MGR",1,0)=1)
统计信息
----------------------------------------------------------
2 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
575 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
-----------------------------------------------------用 EXISTS 替换DISTINCT ---------------------------------------------------------------
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT 子句中使用
DISTINCT. 一般可以考虑用EXIST 替换
例如:
低效:（nestloop，外加unique）
SELECT DISTINCT d.DEPTNO, d.DNAME
FROM scott.DEPT D, scott.EMP E
WHERE D.DEPTNO = E.DEPTNO
执行计划
----------------------------------------------------------
Plan hash value: 2401638402
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 80 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 5 | 80 | 4 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | 12 | 192 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO | 3 | 9 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("E"."DEPTNO" IS NOT NULL)
高效:（nestloop）
SELECT d.DEPTNO, d.DNAME
FROM scott.DEPT D
WHERE EXISTS (SELECT 1 FROM scott.EMP E WHERE E.DEPTNO = D.DEPTNO);
执行计划
----------------------------------------------------------
Plan hash value: 3369102344
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 48 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 3 | 48 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO | 8 | 24 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO" IS NOT NULL)
EXISTS 使查询更为迅速,因为RDBMS 核心模块将在子查询的条件一旦满足后,立刻返回结
果.
-----------------------------------------------------------索引合并 ---------------------------------------------------------------
empno和ename上都有unique索引，会根据2个索引的rowid做个bitmap合并
SQL> select * from scott.emp e where e.empno=7369 and ename='SMITH';
执行计划
----------------------------------------------------------
Plan hash value: 2575831182
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 39 | 0 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | BBB | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."EMPNO"=7369)
7 - access("ENAME"='SMITH')
-----------------------------------------------------------索引优先级 ---------------------------------------------------------------
empno上有唯一索引，deptno上为非唯一索引，但是查询选择使用depno上的索引，
虽然 EMPNO 是唯一性索引,但是由于它所做的是范围比较, 等级要比非唯一性索引的等式比较低!
SQL> select * from scott.emp e where e.empno>73 and deptno=20;
执行计划
----------------------------------------------------------
Plan hash value: 1182541070
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 117 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO | 3 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."EMPNO">73)
2 - access("DEPTNO"=20)
-----------------------------------------------------------Order by 使用索引 ---------------------------------------------------------------
ORDER BY 子句只在两种严格的条件下使用索引.
1ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
2ORDER BY 中所有的列必须定义为非空.
SQL> select * from test1 order by id1;（未使用索引，因为列不是非空）
已选择8192行。
执行计划
----------------------------------------------------------
Plan hash value: 1692556001
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8192 | 416K| | 125 (1)| 00:00:02 |
| 1 | SORT ORDER BY | | 8192 | 416K| 528K| 125 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL| TEST1 | 8192 | 416K| | 17 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
58 consistent gets
0 physical reads
0 redo size
176097 bytes sent via SQL*Net to client
6422 bytes received via SQL*Net from client
548 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8192 rows processed
SQL> alter table test1 modify id1 not null;
表已更改。
SQL> select * from test1 order by id1;（走索引）
已选择8192行。
执行计划
----------------------------------------------------------
Plan hash value: 2136352608
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8192 | 416K| 32 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 8192 | 416K| 32 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | MMM | 8192 | | 19 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
30 recursive calls
0 db block gets
13526 consistent gets
17 physical reads
0 redo size
248747 bytes sent via SQL*Net to client
6422 bytes received via SQL*Net from client
548 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
8192 rows processed
-----------------------------------------------------------NOT in 与 NOT exists --------------------------------------------------------------
exist不检测实际的值~只是做一个存在判断就立刻返回~所以null值也返回给结果集了
in 会查询结果集除了null值不会返回以外还会过滤重复的值
这两个sql不等价：
SQL> SELECT count(*) FROM qdata.t_pub010 t1 WHERE t1.f006 NOT IN (SELECT t2.comcode FROM qdata.t_stk005 t2);
COUNT(*)
----------
8725
SQL> SELECT count(*) FROM qdata.t_pub010 t1 WHERE NOT exists (SELECT t2.comcode FROM qdata.t_stk005 t2 where t2.comcode=t1.f006) ;
COUNT(*)
----------
22092
跟这个才等价：
SQL> SELECT count(*) FROM qdata.t_pub010 t1 WHERE NOT exists (SELECT t2.comcode FROM qdata.t_stk005 t2 where t2.comcode=t1.f006) and t1.f006 is not null;
COUNT(*)
----------
8725
简单说就是not in不会统计结果为null的，而not exists只是逻辑判断，所以包含为null的结果：
SQL> select count(*) from scott.emp where comm not in (1000,300);
COUNT(*)
----------
3
-----------------------------------------------------------标量子查询的等价 --------------------------------------------------------------
DROP TABLE TEST111;
CREATE TABLE TEST111 (ID INT );
INSERT INTO TEST111 VALUES (1);
INSERT INTO TEST111 VALUES (2);
INSERT INTO TEST111 VALUES (3);
COMMIT;
DROP TABLE TEST222;
CREATE TABLE TEST222 (ID INT );
INSERT INTO TEST222 VALUES (1);
INSERT INTO TEST222 VALUES (2);
INSERT INTO TEST222 VALUES (NULL);
COMMIT;
如下两种写法等价
SELECT T1.ID , (SELECT T2.ID FROM TEST222 T2 WHERE ID=T1.ID) FROM TEST111 T1;
ID (SELECTT2.IDFROMTEST222T2WHERE
--------------------------------------- ------------------------------
1 1
2 2
3
SQL> SELECT T1.ID ,T2.ID FROM TEST111 T1 ,TEST222 T2 WHERE T1.ID=T2.ID(+);
ID ID
--------------------------------------- ---------------------------------------
1 1
2 2
3
如果不写成外连接，将不等价
SQL> SELECT T1.ID ,T2.ID FROM TEST111 T1 ,TEST222 T2 WHERE T1.ID=T2.ID;
ID ID
--------------------------------------- ---------------------------------------
1 1
2 2
如下是等价的 :
select a.username, count(owner)
from all_users a, all_objects b
where a.username = b.owner (+)
group by a.username;
select a.username,
(select count(*) from all_objects b where b.owner = a.username) cnt
from all_users a
-----------------------------------------------------------行列转换函数 --------------------------------------------------------------
LISTAGG，多行合并，11.2新特性
SQL> select listagg(comm,',') within group(order by empno) from scott.emp;
LISTAGG(COMM,',')WITHINGROUP(ORDERBYEMPNO)
--------------------------------------------------------------------------
1000,300,500,1400,0
说明：合并会忽略空值，不能去重，如果想去重，需要在from中用子查询distinct，如：根据deptno分组，把job合并。
SQL>select deptno,listagg(job,',') within group(order by job) list from (select distinct deptno,job from scott.emp) group by deptno;//去重后
DEPTNO LIST
---------- ------------------------------
10 CLERK,MANAGER,PRESIDENT
20 ANALYST,CLERK,MANAGER
30 CLERK,MANAGER,SALESMAN
SQL> select deptno,listagg(job,',') within group(order by job) list from scott.emp group by deptno;//未去重
DEPTNO LIST
---------- ------------------------------
10 CLERK,MANAGER,PRESIDENT
20 ANALYST,CLERK,MANAGER
30 CLERK,MANAGER,SALESMAN,SALESMAN,SALESMAN,SALESMAN
WM_CONCAT
SQL> select deptno,wmsys.wm_concat(job) from emp group by deptno;
DEPTNOWMSYS.WM_CONCAT(JOB)
------------------------------------------------------------------------------------------
10MANAGER,CLERK,PRESIDENT
20CLERK,ANALYST,CLERK,ANALYST,MANAGER
30SALESMAN,CLERK,SALESMAN,MANAGER,SALESMAN,SALESMAN
SQL> select deptno,wmsys.wm_concat(distinct job) from emp group by deptno;
DEPTNOWMSYS.WM_CONCAT(JOB)
------------------------------------------------------------------------------------------
10MANAGER,CLERK,PRESIDENT
20ANALYST,CLERK,MANAGER
30SALESMAN,CLERK,MANAGER
说明：合并会去重，10g以后有，不能排序，可以去重。未公开函数，10g返回值是varchar，11.2.0.3.2变为clob。可以用做分析函数
SQL> select deptno,to_char(wmsys.wm_concat(job) over(partition by deptno order by job)) a from scott.emp;
DEPTNO A
---------- ------------------------------------------------------------
10 CLERK
10 CLERK,MANAGER
10 CLERK,MANAGER,PRESIDENT
20 ANALYST
20 ANALYST,CLERK
20 ANALYST,CLERK,MANAGER
30 CLERK
30 CLERK,MANAGER
30 CLERK,MANAGER,SALESMAN,SALESMAN,SALESMAN,SALESMAN
30 CLERK,MANAGER,SALESMAN,SALESMAN,SALESMAN,SALESMAN
30 CLERK,MANAGER,SALESMAN,SALESMAN,SALESMAN,SALESMAN
-----------------------------------------------------------in 子查询改写 --------------------------------------------------------------
in的子查询可以成表关联方式，但是要注意去重
去重可通过group by或者distinct，否则会出现重复值
SQL> select * from az1;
N
----------
1
SQL> select * from az2;
N
----------
1
1
1
1
SQL> select * from az1 where n in (select n from az2);
N
----------
1
SQL> select * from az1,az2 where az1.n=az2.n group by az1.n,az2.n;
N N
---------- ----------
1 1
SQL> select distinct * from az1,az2 where az1.n=az2.n;
N N
---------- ----------
1 1
-----------------------------------------------------------根据某字段更新的优化 --------------------------------------------------------------
aaa表和bbb表是从scott.emp创建，至13000行，并把empno改成rownum；
原始语句：
update bbb set sal=(select sal from aaa where bbb.empno=aaa.empno) where bbb.job='PRESIDENT';
35S
优化为游标方式：
create or replace procedure fuck is
cursor allamt is
select a.sal, a.empno from aaa a, bbb b where a.empno = b.empno;
begin
FOR tc in allamt loop
update bbb b set b.sal = tc.sal where b.empno = tc.empno and b.job='PRESIDENT';
end loop;
commit;
end fuck;
/
exec fuck;
13s
再次优化为merge：
merge into bbb
using aaa
on (aaa.empno=bbb.empno)
when matched then
update set bbb.sal=aaa.sal , bbb.mgr=aaa.mgr
where bbb.job='PRESIDENT';
2S
-----------------------------------------------------------insert all --------------------------------------------------------------
insert all
into a
into b
select * from dba_objects;
同时插入2张表，一个事务。一份复制，2份粘贴。性能更好。
分成2条写，有可能中间dba_objects数据有了变化，导致ab表不一致了。
-----------------------------------------------------------connect by实现行展开成列 --------------------------------------------------------------
WITH T AS (
SELECT 'a' name ,1 ST, 4 EN FROM DUAL UNION ALL
SELECT 'b' name ,1 ST, 4 EN FROM DUAL UNION ALL
SELECT 'D' name ,3 ST, 3 EN FROM DUAL UNION ALL
SELECT 'C' name ,7 ST, 9 EN FROM DUAL
)
SELECT T.name name
,ROW_NUMBER()OVER(PARTITION BY T.name ORDER BY 1)+T.ST-1 RN
FROM T
CONNECT BY LEVEL <= (T.EN-T.ST+1) AND T.name = PRIOR T.name AND PRIOR SYS_GUID() IS NOT NULL ;