【書評:Oracle查詢最佳化改寫】第三章
【書評:Oracle查詢最佳化改寫】第三章
BLOG文件結構圖
一.1 導讀
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 隱含引數 _b_tree_bitmap_plans介紹
② 11g新特性Native Full Outer Join
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
一.2 實驗環境介紹
oracle：11.2.0.3 、8.1.7.0.0
OS: RHEL6.5
一.3 前言
前2章的連結參考相關連線：
【書評:Oracle查詢最佳化改寫】第一章 http://blog.itpub.net/26736162/viewspace-1652985/
【書評:Oracle查詢最佳化改寫】第二章 http://blog.itpub.net/26736162/viewspace-1654252/
昨天晚上(5.14)看完了《Oracle查詢最佳化改寫》的第三章，不得不說下這本書裡邊程式碼的排版有很大問題，格式老是不對齊，尤其是執行計劃的格式，可能是印刷的時候出現的問題吧，不說這個了。這個第三章主要是講多表的關聯，包括各種連線的寫法，如左聯、右聯，以及過濾條件錯誤地放在WHERE裡會有什麼影響；當資料有重複值時要直接關聯還是分組彙總後再關聯。
第 3 章 操作多個表
3.1 UNION ALL 與空字串
3.2 UNION 與 OR
3.3 組合相關的行
3.4 IN、EXISTS 和 INNER JOIN
3.5 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 解析
3.6 自關聯
3.7 NOT IN、NOT EXISTS 和 LEFT JOIN
3.8 外連線中的條件不要亂放
3.9 檢測兩個表中的資料及對應資料的條數是否相同
3.10 聚集與內連線
3.11 聚集與外連線
3.12 從多個表中返回丟失的資料
3.13 多表查詢時的空值處理
下邊我就針對一些重點，或者說是我自己也不是很懂的部分做做研究吧。
一.4 隱含引數 _b_tree_bitmap_plans 實驗
一.4.1 簡介
該引數為隱含引數，是指是否將索引轉換為bitmap索引然後執行，在oracle9i之前預設值為false，之後的預設值為true。可以這樣認為，如有兩個欄位A，B都有btree索引，oracle有可能將這兩個索引轉換成bitmap索引然後做and操作得出結果集。如果改為false就會選用其中的一個索引，走btree的索引，我們可以將該引數在session或系統級別設定為false，也可以加hint /*+ opt_param('_b_tree_bitmap_plans', 'false') */ 來實現禁用該引數。
· symptom: Execution plan operation shows bitmap conversion from rowids
· symptom: No bitmap indexes
· symptom: Execution plan shows BITMAP CONVERSION
· cause: In 7.3.4 and in 8.1.7 default value of _b_tree_bitmap_plans is FALSE
whereas as of 9.0.1 (and 9.2) the default value is TRUE When _b_tree_bitmap_plans set to true (advice not to change the default setting
yourself) the optimizer is allowed to produce bitmap plans for normal b*tree
indexes even if no bitmap indexes set.
相關的執行計劃中可能轉換為如下的形式：
（1）BITMAP CONVERSION FROM ROWIDS
將一批資料記錄的ROWID對映為點陣圖。
對於普通B*樹索引，Oracle也可以將資料記錄的ROWID對映成一個點陣圖，然後進行點陣圖操作。進行這樣的轉換需要將系統引數_b_tree_bitmap_plans設定為TRUE。
（2）BITMAP CONVERSION TO ROWIDS
將點陣圖對映為ROWID。在一個點陣圖鍵值中，包含了一批資料記錄的起始地址和結束地址，且這批記錄是連續的，因此點陣圖中的每一個位就按序對應了一條資料記錄。
（3）BITMAP OR
對點陣圖進行“或”（OR）操作。在查詢的過濾條件中，如果點陣圖索引欄位直接的關係是“或”，可以透過BITMAP OR來判斷點陣圖所對映的一批資料記錄是否滿足條件。
eygle大師的一個例子：
一.4.2 11g情況下
[root@rhel6_lhr ~]# su - oracle
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 10:16:10 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
10:16:10 SQL>
10:16:10 SQL> conn lhr/lhr
Connected.
10:16:10 SQL> create table emp_bk as select * from scott.emp;
Table created.
Elapsed: 00:00:03.43
10:16:15 SQL> create index idx_emp_empno on emp_bk(empno);
Index created.
Elapsed: 00:00:00.05
10:19:26 SQL> create index idx_emp_ename on emp_bk(ename);
Index created.
Elapsed: 00:00:00.04
10:20:48 SQL> explain plan for select empno,ename from emp_bk where empno=7788 or ename='SCOTT';
Explained.
Elapsed: 00:00:00.09
10:20:56 SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4193090541
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP_BK | 1 | 20 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | IDX_EMP_EMPNO | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | IDX_EMP_ENAME | | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("EMPNO"=7788)
7 - access("ENAME"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
24 rows selected.
Elapsed: 00:00:00.52
10:24:06 SQL> conn / as sysdba
Connected.
Elapsed: 00:00:00.03
10:24:34 SQL> set pagesize 9999
10:24:41 SQL> set line 9999
10:24:41 SQL> col NAME format a30
10:24:41 SQL> col KSPPDESC format a50
10:24:41 SQL> col KSPPSTVL format a20
10:24:42 SQL> SELECT a.INDX,
10:24:42 2 a.KSPPINM NAME,
10:24:42 3 a.KSPPDESC,
10:24:42 4 b.KSPPSTVL
10:24:42 5 FROM x$ksppi a,
10:24:42 6 x$ksppcv b
10:24:42 7 WHERE a.INDX = b.INDX
10:24:42 8 and lower(a.KSPPINM) like lower('%?meter%');
Enter value for parameter: _b_tree_bitmap_plans
old 8: and lower(a.KSPPINM) like lower('%?meter%')
new 8: and lower(a.KSPPINM) like lower('%_b_tree_bitmap_plans%')
INDX NAME KSPPDESC KSPPSTVL
---------- ------------------------------ -------------------------------------------------- --------------------
1910 _b_tree_bitmap_plans enable the use of bitmap plans for tables w. only TRUE
B-tree indexes
Elapsed: 00:00:00.01
10:25:44 SQL> conn lhr/lhr
Connected.
10:26:56 SQL> alter session set "_b_tree_bitmap_plans" = false;
Session altered.
Elapsed: 00:00:00.00
10:27:01 SQL> show parameter _b_tree_bitmap_plans
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_b_tree_bitmap_plans boolean FALSE
10:27:05 SQL> explain plan for select empno,ename from emp_bk where empno=7788 or ename='SCOTT';
Explained.
Elapsed: 00:00:00.01
10:27:14 SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 370270337
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP_BK | 1 | 20 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7788 OR "ENAME"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
Elapsed: 00:00:00.04
10:27:18 SQL> explain plan for select empno,ename from emp_bk where empno=7788
10:27:49 2 union
10:27:55 3 select empno,ename from emp_bk where ename='SCOTT';
Explained.
Elapsed: 00:00:00.00
10:28:07 SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3014579657
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 6 (67)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 40 | 6 (67)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP_BK | 1 | 20 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_EMP_EMPNO | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP_BK | 1 | 20 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPNO"=7788)
6 - access("ENAME"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
23 rows selected.
Elapsed: 00:00:00.01
10:28:13 SQL> Select Name ,Value From v$parameter Where Name ='_b_tree_bitmap_plans' ;
NAME VALUE
------------------------------ ---------------------------------------------------------------
_b_tree_bitmap_plans FALSE
Elapsed: 00:00:00.02
10:34:06 SQL> alter session set "_b_tree_bitmap_plans" = true;
Session altered.
Elapsed: 00:00:00.00
11:19:04 SQL> explain plan for select /*+ opt_param('_b_tree_bitmap_plans', 'false') */ empno,ename from emp_bk where empno=7788 or ename='SCOTT';
Explained.
Elapsed: 00:00:00.08
11:19:22 SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 370270337
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP_BK | 1 | 20 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7788 OR "ENAME"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
Elapsed: 00:00:00.24
由實驗可以看出，_b_tree_bitmap_plans設定為false後，emp_bk走了全表掃描，並沒有走點陣圖索引轉換。
一.4.3 8i情況下
C:\Users\Administrator>sqlplus "lhr/lhr@orcl8i as sysdba"
SQL*Plus: Release 8.1.7.0.0 - Production on Mon May 18 10:44:28 2015
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> set pagesize 9999
SQL> set line 9999
SQL> col NAME format a30
SQL> col KSPPDESC format a50
SQL> col KSPPSTVL format a20
SQL> SELECT a.INDX,
2 a.KSPPINM NAME,
3 a.KSPPDESC,
4 b.KSPPSTVL
5 FROM x$ksppi a,
6 x$ksppcv b
7 WHERE a.INDX = b.INDX
8 and lower(a.KSPPINM) like lower('%?meter%');
Enter value for parameter: _b_tree_bitmap_plans
old 8: and lower(a.KSPPINM) like lower('%?meter%')
new 8: and lower(a.KSPPINM) like lower('%_b_tree_bitmap_plans%')
INDX NAME KSPPDESC KSPPSTVL
---------- ------------------------------ -------------------------------------------------- --------------------
348 _b_tree_bitmap_plans enable the use of bitmap plans for tables w. only FALSE
B-tree indexes
SQL>
SQL> create table lhr.emp_bk as select * from scott.emp;
Table created.
SQL> create index lhr.idx_emp_empno on lhr.emp_bk(empno);
Index created.
SQL> create index lhr.idx_emp_ename on lhr.emp_bk(ename);
Index created.
SQL> set line 9999 pagesize 9999
SQL> set autot on;
SQL> select empno,ename from lhr.emp_bk where empno=7788 or ename='SCOTT';
EMPNO ENAME
---------- ----------
7788 SCOTT
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP_BK'
3 2 INDEX (RANGE SCAN) OF 'IDX_EMP_ENAME' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP_BK'
5 4 INDEX (RANGE SCAN) OF 'IDX_EMP_EMPNO' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
8i下預設為false，執行計劃也完全不同。
一.5 Native Full Outer Join
關於這個特性可以參考如下文章：
http://blog.itpub.net/26736162/viewspace-1660038/
我們在10.2.0.4下測試一下：
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 18 11:41:13 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 448790528 bytes
Fixed Size 2084616 bytes
Variable Size 121635064 bytes
Database Buffers 318767104 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> create table lhr.emp_bk as select * from scott.emp;
Table created.
SQL> create table lhr.emp_bk as select * from scott.emp;
Table created.
SQL> set autot on;
SQL> set line 9999 pagesize 9999
SQL> select * from lhr.emp_bk a full outer join lhr.emp_bk2 b on a.empno=b.empno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 914601651
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 2610 | 13 (8)| 00:00:01 |
| 1 | VIEW | | 15 | 2610 | 13 (8)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 14 | 2436 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP_BK | 14 | 1218 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP_BK2 | 14 | 1218 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN ANTI | | 1 | 100 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| EMP_BK2 | 14 | 1218 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| EMP_BK | 14 | 182 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."EMPNO"="B"."EMPNO"(+))
6 - access("A"."EMPNO"="B"."EMPNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
338 recursive calls
0 db block gets
61 consistent gets
6 physical reads
0 redo size
2521 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select /*+ NATIVE_FULL_OUTER_JOIN */ * from lhr.emp_bk a full outer join lhr.emp_bk2 b on a.empno=b.empno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2812081866
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2436 | 7 (15)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 14 | 2436 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 14 | 2436 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP_BK | 14 | 1218 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP_BK2 | 14 | 1218 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."EMPNO"="B"."EMPNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
2521 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> set pagesize 9999
SQL> set line 9999
SQL> col NAME format a40
SQL> col KSPPDESC format a50
SQL> col KSPPSTVL format a20
SQL> SELECT a.INDX,
2 a.KSPPINM NAME,
3 a.KSPPDESC,
4 b.KSPPSTVL
5 FROM x$ksppi a,
6 x$ksppcv b
7 WHERE a.INDX = b.INDX
8 and lower(a.KSPPINM) like lower('%?meter%');
Enter value for parameter: optimizer_native_full_outer_join
old 8: and lower(a.KSPPINM) like lower('%?meter%')
new 8: and lower(a.KSPPINM) like lower('%optimizer_native_full_outer_join%')
INDX NAME KSPPDESC KSPPSTVL
---------- ------------------------------ -------------------------------------------------- --------------------
1318 _optimizer_native_full_outer_j execute full outer join using native implementaion off
oin
SQL>
一.6 多表查詢時候的null值處理
我們在第一篇（http://blog.itpub.net/26736162/viewspace-1652985/）中總結了一下null值特徵，今天我們再來看一下多表查詢的時候null值得處理。
一.6.1 情形一：
若子查詢中的結果中包含null值，那麼not in（null、xx、bb、cc）返回為空。
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 18 13:38:09 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
13:38:09 SQL> drop table lhr.emp_bk;
Table dropped.
Elapsed: 00:00:04.16
13:38:15 SQL> create table lhr.emp_bk as select * from scott.emp;
Table created.
Elapsed: 00:00:00.77
13:41:01 SQL> create table lhr.dept_bk as select * from scott.dept;
Table created.
Elapsed: 00:00:00.13
13:41:43 SQL> insert into lhr.dept_bk values(50,'lhr','China');
1 row created.
Elapsed: 00:00:00.03
13:41:57 SQL> select * from lhr.dept_bk ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 lhr China
Elapsed: 00:00:00.01
13:42:48 SQL> select * from lhr.emp_bk b;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 20800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 31600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 31250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 22975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 31250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 32850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 12450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 23000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 15000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 31500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 21100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 30950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 23000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 11300 10
16 rows selected.
Elapsed: 00:00:00.02
13:44:00 SQL> select * from lhr.dept_bk a where a.deptno not in(select b.deptno from lhr.emp_bk b);
DEPTNO DNAME LOC
---------- -------------- -------------
50 lhr China
40 OPERATIONS BOSTON
Elapsed: 00:00:00.93
13:44:07 SQL> update lhr.emp_bk b set b.deptno=null where empno=7788;
1 row updated.
Elapsed: 00:00:00.04
13:45:17 SQL> select * from lhr.emp_bk b;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 20800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 31600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 31250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 22975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 31250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 32850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 12450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 23000
7839 KING PRESIDENT 1981-11-17 00:00:00 15000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 31500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 21100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 30950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 23000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 11300 10
14 rows selected.
Elapsed: 00:00:00.14
13:45:23 SQL> select * from lhr.dept_bk a where a.deptno not in(select b.deptno from lhr.emp_bk b);
no rows selected
Elapsed: 00:00:00.00
13:45:39 SQL> select * from lhr.dept_bk a where a.deptno not in(select b.deptno from lhr.emp_bk b where b.deptno is not null);
DEPTNO DNAME LOC
---------- -------------- -------------
50 lhr China
40 OPERATIONS BOSTON
Elapsed: 00:00:00.04
13:46:01 SQL>
一.6.2 情形二：
要求返回所有比“ALLEN”提成低的員工：
14:01:07 SQL> select a.ename,a.comm from scott.emp a;
ENAME COMM
---------- ----------
SMITH
ALLEN 300
WARD 500
JONES
MARTIN 1400
BLAKE
CLARK
SCOTT
KING
TURNER 0
ADAMS
JAMES
FORD
MILLER
14 rows selected.
Elapsed: 00:00:00.23
14:01:17 SQL> select a.ename,a.comm from scott.emp a where a.comm < ( select b.comm from scott.emp b where b.ename='ALLEN');
ENAME COMM
---------- ----------
TURNER 0
Elapsed: 00:00:00.11
14:01:28 SQL> select a.ename,a.comm from scott.emp a where coalesce(a.comm,0) < ( select b.comm from scott.emp b where b.ename='ALLEN');
ENAME COMM
---------- ----------
SMITH
JONES
BLAKE
CLARK
SCOTT
KING
TURNER 0
ADAMS
JAMES
FORD
MILLER
11 rows selected.
Elapsed: 00:00:00.02
14:01:55 SQL>
一.7 總結
到此個人覺得本章的一些難點或需要補充的地方就這些了，希望大家看完有所收穫。
一.8 about me
...........................................................................................................................................................................................
本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1660422/
本文pdf版： 提取碼：af2d
QQ：642808185 若加QQ請註明你所正在讀的文章標題
創作時間地點：2015-05-15 10:00~ 2015-05-18 15:00 於外匯交易中心
...........................................................................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1660422/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【書評:Oracle查詢優化改寫】第三章Oracle優化
- 【書評:Oracle查詢最佳化改寫】第二章Oracle
- 【書評:Oracle查詢最佳化改寫】第五至十三章Oracle
- 【書評:Oracle查詢最佳化改寫】第一章Oracle
- 【書評:Oracle查詢優化改寫】第二章Oracle優化
- 【書評:Oracle查詢優化改寫】第一章Oracle優化
- 【書評:Oracle查詢優化改寫】第五至十三章Oracle優化
- GreatSQL 最佳化技巧：將 MINUS 改寫為標量子查詢SQL
- 用WITH…AS改寫標量子查詢
- oracle的查詢最佳化Oracle
- Oracle最佳化技術---物化檢視查詢重寫query rewriteOracle
- 帶彙總的標量子查詢改寫
- Oracle臨時表最佳化查詢速度Oracle
- Oracle臨時表 最佳化查詢速度Oracle
- Laravel Passport OAuth 資料庫查詢改快取最佳化LaravelPassportOAuth資料庫快取
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- MySQL not in巢狀查詢改寫成外連線方式MySql巢狀
- vertica查詢最佳化
- 查詢重寫
- Oracle 樹查詢 效能最佳化紀實（start with, connect by）Oracle
- Oracle 最佳化器與sql查詢執行順序OracleSQL
- StoneDB 子查詢最佳化
- 最佳化星型查詢
- oracle 查詢Oracle
- 美團搜尋中查詢改寫技術的探索與實踐
- Oracle查詢最佳化4大方面的主要途徑Oracle
- 【效能最佳化】Oracle 效能最佳化：降低列值聚簇因子 提高查詢效率Oracle
- 慢查詢最佳化及分析
- 【Mysql】MySQL查詢最佳化-explainMySqlAI
- oracle 精確查詢和模糊查詢Oracle
- 在Oracle中進行大小寫不敏感的查詢Oracle
- oracle子查詢Oracle
- Oracle 日期查詢Oracle
- oracle 樹查詢Oracle
- ORACLE SCN 查詢Oracle
- Oracle查詢原理Oracle
- Oracle case when改寫SQLOracleSQL
- Oracle Rownum分頁改寫Oracle