【書評:Oracle查詢優化改寫】第三章

發布時間：2020-07-08 20:37:04 來源：網絡閱讀：353 作者：小麥苗best 欄目：關系型數據庫

BLOG文檔結構圖

【書評:Oracle查詢優化改寫】第三章

一.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大師的一個例子：

http://www.eygle.com/archives/2011/12/bitmap_conversion_cpu.html

一.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

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

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

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

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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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;