【書評:Oracle查詢最佳化改寫】第一章

BLOG文件結構圖：

之前幫助ITPUB上的一位博主修改過一個很明顯的錯誤，ITPUB為了表達感謝特贈予一本技術方面的書籍，我可以自己選擇書名，想了想，自己對SQL最佳化特感興趣於是就訂了一本SQL最佳化改寫方面的書籍，書名為《Oracle查詢最佳化改寫》，其實這本書的作者我是認識的，之前數次在公開課上聽過他講過SQL最佳化改寫方面的內容，印象很深刻，好了，不多說了，說多了有打廣告的嫌疑。

最近一直在學習rac方面的內容，但是rac高可用性，這個方面很難寫，寫淺了感覺浪費時間，也浪費讀者的時間，寫深了又怕出錯，誤導別人，在自己未完全理解的情況下，不想隨便釋出blog，但總得釋出一點啥吧，想了想不如就寫寫讀後感吧，基於自己本身喜歡SQL最佳化方面的內容，又有書在手，那就寫寫書中的內容吧，有好的例子，我試驗下給大家分享出來，今天我們就看看這本書的第一章的內容吧。

第一章很簡單，就講了下單表查詢的內容，目錄參考後邊附錄部分，大致包含：NULL上常犯的錯誤、字串中單引號的處理方式及模糊查詢時對萬用字元的轉義。

第 1 章單表查詢

1.1 查詢表中所有的行與列

1.2 從表中檢索部分行

1.3 查詢空值

1.4 將空值轉換為實際值

1.5 查詢滿足多個條件的行

1.6 從表中檢索部分列

1.7 為列取有意義的名稱

1.8 在 WHERE 子句中引用取別名的列

1.9 拼接列

1.10 在 SELECT 語句中使用條件邏輯

1.11 限制返回的行數

1.12 從表中隨機返回 n 條記錄

1.13 模糊查詢

1 空值

這個很簡單，但是容易出錯，可以認為是重點，判斷為空或者不為空的時候，只能採用is null 或者is not null，不能用=null，任何空值做加減乘除大小比較或相等比較結果都為空，這個沒啥實驗的，oracle入門必學的一些內容，另外還有一些處理空值的函式，例如nvl、coalesce等，這部分就不做實驗了，書中沒有如下的內容，我補充一些，也是書是講SQL最佳化改寫的，又不是講解語法的。

解釋：

1. 空值是無效的，未指定的，未知的或不可預知的值。

2. 空值不是空格也不是0。

3. 包含空值的數學表示式的值(即加減乘除等操作)都為空值null

4. 對空值進行連線字串的操作之後，返回為被連線的字串。

5. 為空用is null 來表示，不為空用 is not null 來表示，除此之外沒有其它的表示方法了，這一點尤為重要！！！！！！

6. 除了count(1) 和count(*) 外的其它函式都不計算空值

7. Null 在排序中預設為最大值，desc在最前，asc在最後，可以加上nulls last 來限制null值的顯示

辦法：使用 nvl 函數來處理

SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp;

問題：如何顯示沒有上級的僱員的情況？

錯誤寫法：select * from emp where mgr = '';

正確寫法：SELECT * FROM emp WHERE mgr is null;

1. 有如下的資料，請問select avg(age) from stuInfo; 的結果是多少？

答案：5.2 空值是不參與運算的

1.1 讓is null走索引

不知道書的後邊會不會講解如何讓is null走索引的內容，我這裡先給大家講講如何讓is null去走索引吧。

解決辦法： is null 加偽列建立偽聯合索引來使得is null使用索引

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 13 17:29:16 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> create table lhr.t2 as select * from dba_objects;

Table created.

SQL> select * from lhr.t2 where object_id is null;

no rows selected

SQL> set autot on;

SQL> select * from lhr.t2 where object_id is null;

no rows selected

Execution Plan

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

Plan hash value: 1513984157

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

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

| 0 | SELECT STATEMENT | | 12 | 2484 | 289 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T2 | 12 | 2484 | 289 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID" IS NULL)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

0 recursive calls

0 db block gets

1035 consistent gets

1032 physical reads

0 redo size

1343 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL> create index ind_t2_id on lhr.t2(object_id);

Index created.

SQL> exec dbms_stats.gather_index_stats('SYS','ind_t2_id');

PL/SQL procedure successfully completed.

SQL> select * from lhr.t2 where object_id is null;

no rows selected

Execution Plan

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

Plan hash value: 1513984157

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

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

| 0 | SELECT STATEMENT | | 12 | 2484 | 289 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T2 | 12 | 2484 | 289 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID" IS NULL)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

935 recursive calls

0 db block gets

1238 consistent gets

1102 physical reads

0 redo size

1343 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

0 rows processed

SQL> DROP INDEX ind_t2_id;

Index dropped.

SQL> create index LHR.ind_t2_id on lhr.t2(object_id,-1);

Index created.

SQL> exec dbms_stats.gather_index_stats('LHR','ind_t2_id');

PL/SQL procedure successfully completed.

SQL> SET LINE 9999

SQL> select * from lhr.t2 where object_id is null;

no rows selected

Execution Plan

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

Plan hash value: 2868503181

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

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

| 0 | SELECT STATEMENT | | 12 | 2484 | 67 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 12 | 2484 | 67 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_T2_ID | 4071 | | 12 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID" IS NULL)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

1343 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL>

可以看出過濾條件中is null走了索引。

2 sql中的別名

以我的sql最佳化經驗而言，強烈建議表加別名，尤其對於sql中是多張表關聯的時候更應對每一個表加上別名。

3 從表中隨機返回N條記錄

正確寫法：select empno,ename from ( select empno,ename from scott.emp order by dbms_random.value()) where rownum<=3;

錯誤寫法：select empno,ename from scott.emp where rownum<=3 order by dbms_random.value();

SQL> select empno,ename from ( select empno,ename from scott.emp order by dbms_random.value()) where rownum<=3;

EMPNO ENAME

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

7788 SCOTT

7934 MILLER

7900 JAMES

SQL> select empno,ename from ( select empno,ename from scott.emp order by dbms_random.value()) where rownum<=3;

EMPNO ENAME

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

7782 CLARK

7369 SMITH

7499 ALLEN

SQL> select empno,ename from scott.emp where rownum<=3 order by dbms_random.value();

EMPNO ENAME

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

7499 ALLEN

7369 SMITH

7521 WARD

SQL> select empno,ename from scott.emp where rownum<=3 order by dbms_random.value();

EMPNO ENAME

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

7369 SMITH

7521 WARD

7499 ALLEN

SQL> select empno,ename from scott.emp where rownum<=3 order by dbms_random.value();

EMPNO ENAME

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

7499 ALLEN

7521 WARD

7369 SMITH

SQL>

4 模糊查詢

簡單點的模糊查詢就不說了，但是在字串中含有’\’的時候，雙寫\\即可。

SQL> create or replace view v as select '_\BCEDF' VNAME FROM DUAL;

View created.

SQL> SELECT * FROM V WHERE VNAME LIKE '_\BC%' ESCAPE '\';

SELECT * FROM V WHERE VNAME LIKE '_\BC' ESCAPE '\'

*

ERROR at line 1:

ORA-01424: missing or illegal character following the escape character

SQL> SELECT * FROM V WHERE VNAME LIKE '_\\BC%' ESCAPE '\';

VNAME

-------

_\BCEDF

好了，第一章內容基本上就這些，後邊等我閱讀了第二章後再給大家分享吧。

...........................................................................................................................................................................................

本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

ITPUB BLOG：http://blog.itpub.net/26736162

本文地址：http://blog.itpub.net/26736162/viewspace-1652985/

本文pdf版：提取碼：af2d

QQ：642808185 註明：ITPUB的文章標題

...........................................................................................................................................................................................

【書評:Oracle查詢最佳化改寫】第一章

1 空值

1.1 讓is null走索引

2 sql中的別名

4 模糊查詢

相關文章