溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
【書評:Oracle查詢優(yōu)化改寫】第三章
BLOG文檔結(jié)構(gòu)圖
各位技術(shù)愛好者，看完本文后，你可以掌握如下的技能，也可以學(xué)到一些其它你所不知道的知識，~O(∩_∩)O~：
① 隱含參數(shù) _b_tree_bitmap_plans介紹
② 11g新特性Native Full Outer Join
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
oracle：11.2.0.3 、8.1.7.0.0
OS: RHEL6.5
前2章的鏈接參考相關(guān)連接：
【書評:Oracle查詢優(yōu)化改寫】第一章 http://blog.itpub.net/26736162/viewspace-1652985/
【書評:Oracle查詢優(yōu)化改寫】第二章http://blog.itpub.net/26736162/viewspace-1654252/
昨天晚上(5.14)看完了《Oracle查詢優(yōu)化改寫》的第三章，不得不說下這本書里邊代碼的排版有很大問題，格式老是不對齊，尤其是執(zhí)行計劃的格式，可能是印刷的時候出現(xiàn)的問題吧，不說這個了。這個第三章主要是講多表的關(guān)聯(lián)，包括各種連接的寫法，如左聯(lián)、右聯(lián)，以及過濾條件錯誤地放在WHERE里會有什么影響；當數(shù)據(jù)有重復(fù)值時要直接關(guān)聯(lián)還是分組匯總后再關(guān)聯(lián)。
第 3 章 操作多個表
3.1 UNION ALL 與空字符串
3.2 UNION 與 OR
3.3 組合相關(guān)的行
3.4 IN、EXISTS 和 INNER JOIN
3.5 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 解析
3.6 自關(guān)聯(lián)
3.7 NOT IN、NOT EXISTS 和 LEFT JOIN
3.8 外連接中的條件不要亂放
3.9 檢測兩個表中的數(shù)據(jù)及對應(yīng)數(shù)據(jù)的條數(shù)是否相同
3.10 聚集與內(nèi)連接
3.11 聚集與外連接
3.12 從多個表中返回丟失的數(shù)據(jù)
3.13 多表查詢時的空值處理
下邊我就針對一些重點，或者說是我自己也不是很懂的部分做做研究吧。
該參數(shù)為隱含參數(shù)，是指是否將索引轉(zhuǎn)換為bitmap索引然后執(zhí)行，在oracle9i之前默認值為false，之后的默認值為true?？梢赃@樣認為，如有兩個字段A，B都有btree索引，oracle有可能將這兩個索引轉(zhuǎn)換成bitmap索引然后做and操作得出結(jié)果集。如果改為false就會選用其中的一個索引，走btree的索引，我們可以將該參數(shù)在session或系統(tǒng)級別設(shè)置為false，也可以加hint /*+ opt_param('_b_tree_bitmap_plans', 'false') */ 來實現(xiàn)禁用該參數(shù)。
· 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.
相關(guān)的執(zhí)行計劃中可能轉(zhuǎn)換為如下的形式：
（1）BITMAP CONVERSION FROM ROWIDS
將一批數(shù)據(jù)記錄的ROWID映射為位圖。
對于普通B*樹索引，Oracle也可以將數(shù)據(jù)記錄的ROWID映射成一個位圖，然后進行位圖操作。進行這樣的轉(zhuǎn)換需要將系統(tǒng)參數(shù)_b_tree_bitmap_plans設(shè)置為TRUE。
（2）BITMAP CONVERSION TO ROWIDS
將位圖映射為ROWID。在一個位圖鍵值中，包含了一批數(shù)據(jù)記錄的起始地址和結(jié)束地址，且這批記錄是連續(xù)的，因此位圖中的每一個位就按序?qū)?yīng)了一條數(shù)據(jù)記錄。
（3）BITMAP OR
對位圖進行“或”（OR）操作。在查詢的過濾條件中，如果位圖索引字段直接的關(guān)系是“或”，可以通過BITMAP OR來判斷位圖所映射的一批數(shù)據(jù)記錄是否滿足條件。
eygle大師的一個例子：
http://www.eygle.com/archives/2011/12/bitmap_conversion_cpu.html
[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設(shè)置為false后，emp_bk走了全表掃描，并沒有走位圖索引轉(zhuǎn)換。
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，執(zhí)行計劃也完全不同。
關(guān)于這個特性可以參考如下文章：
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>
我們在第一篇（http://blog.itpub.net/26736162/viewspace-1652985/）中總結(jié)了一下null值特征，今天我們再來看一下多表查詢的時候null值得處理。
若子查詢中的結(jié)果中包含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>
要求返回所有比“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>
到此個人覺得本章的一些難點或需要補充的地方就這些了，希望大家看完有所收獲。
...........................................................................................................................................................................................
本文作者：小麥苗，只專注于數(shù)據(jù)庫的技術(shù)，更注重技術(shù)的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1660422/
本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w 提取碼：af2d
QQ：642808185 若加QQ請注明你所正在讀的文章標題
創(chuàng)作時間地點：2015-05-15 10:00~ 2015-05-18 15:00 于×××交易中心
<版權(quán)所有，文章允許轉(zhuǎn)載，但須以鏈接方式注明源地址，否則追究法律責任!>
...........................................................................................................................................................................................
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
