【書評:Oracle查詢最佳化改寫】第二章
【書評:Oracle查詢最佳化改寫】第二章
BLOG文件結構圖
在上一篇中http://blog.itpub.net/26736162/viewspace-1652985/,我們主要分析了一些單表查詢的時候需要注意的內容,今天第二章也很簡單,主要是關於排序方面的內容,以下貼出第二章的內容:
第 2 章 給查詢結果排序
2.1 以指定的次序返回查詢結果
2.2 按多個欄位排序
2.3 按子串排序
2.4 TRANSLATE
2.5 按數字和字母混合字串中的字母排序
2.6 處理排序空值
2.7 根據條件取不同列中的值來排序
排序基本上沒有什麼可以講的,不過書中著重介紹了下translate的用法。
1 translate用法
語法:TRANSLATE(char, from, to)
用法:
1. 返回將出現在from中的每個字元替換為to中的相應字元以後的字串。
2. 若from比to字串長,那麼在from中比to中多出的字元將會被刪除,或者認為from中多出的字元在to中與空對應
3. 三個引數中有一個是空,返回值也將是空值。
09:43:50 SQL> select translate('abcdefga','abc','wo') from dual;
TRANSLA
-------
wodefgw
Elapsed: 00:00:00.14
09:43:57 SQL> select translate('abcdefga','abc','') from dual;
T
-
Elapsed: 00:00:00.00
SELECT translate('ab 你好 bcadefg','abcdefg','1234567'),translate('ab 你好 bcadefg','1abcdefg','1') FROM dual;
2 按數字和字母混合字串中的字母排序,採用translate函式來實現
09:52:01 SQL> create or replace view v as select empno || ' '||ename as data from scott.emp;
View created.
Elapsed: 00:00:00.54
09:52:07 SQL> select * from V
09:52:15 2 ;
DATA
---------------------------------------------------
9000 lastwiner
9001 lastwiner
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
16 rows selected.
Elapsed: 00:00:00.20
09:55:07 SQL> select data,translate(data,'- 0123456789','-') from V order by 2;
DATA TRANSLATE(DATA,'-0123456789','-')
--------------------------------------------------- ------------------------------------------------------------------------------------------------------
7876 ADAMS ADAMS
7499 ALLEN ALLEN
7698 BLAKE BLAKE
7782 CLARK CLARK
7902 FORD FORD
7900 JAMES JAMES
7566 JONES JONES
7839 KING KING
7654 MARTIN MARTIN
7934 MILLER MILLER
7788 SCOTT SCOTT
7369 SMITH SMITH
7844 TURNER TURNER
7521 WARD WARD
9001 lastwiner lastwiner
9000 lastwiner lastwiner
16 rows selected.
Elapsed: 00:00:00.10
09:55:33 SQL>
3 關於order by 排序的最佳化
關於SQL最佳化中有一個原則叫:避免使用耗費資源的操作(DISTINCT、UNION、MINUS、INTERSECT、ORDER BY、group by、SMJ、created index)
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序.
例如,一個UNION查詢,其中每個查詢都帶有GROUP BY子句, GROUP BY會觸發嵌入排序(NESTED SORT) ; 這樣, 每個查詢需要執行一次排序, 然後在執行UNION時, 又一個唯一排序(SORT UNIQUE)操作被執行而且它只能在前面的嵌入排序結束後才能開始執行. 嵌入的排序的深度會大大影響查詢的效率.
通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫.
ORDER BY語句決定了Oracle如何將返回的查詢結果排序。Order by語句對要排序的列沒有什麼特別的限制,也可以將函式加入列中(象聯接或者附加等)。任何在Order by語句的非索引項或者有計算表示式都將降低查詢速度。
仔細檢查order by語句以找出非索引項或者表示式,它們會降低效能。解決這個問題的辦法就是重寫order by語句以使用索引,也可以為所使用的列建立另外一個索引,同時應絕對避免在order by子句中使用表示式。
●在頻繁進行排序或分組(即進行group by或order by操作)的列上建立索引。
●如果待排序的列有多個,可以在這些列上建立複合索引(compound index)。
磁碟排序的開銷是很大的,有幾個方面的原因。首先,和記憶體排序相比較,它們特別慢;而且磁碟排序會消耗臨時表空間中的資源。Oracle還必須分配緩衝池塊來保持臨時表空間中的塊。無論什麼時候,記憶體排序都比磁碟排序好,磁碟排序將會令任務變慢,並且會影響Oracle例項的當前任務的執行。還有,過多的磁碟排序將會令free buffer waits的值變高,從而令其它任務的資料塊由緩衝中移走。
3.1 總結
(1)採用索引避免排序:排序資料較多時
(2)去掉不必要的distinct,很多distinct是由於程式設計師對資料的瞭解不自信而多加的。
總而言之,排序是非常耗費CPU資源的,能不排序就不要排序,如果非得排序,可以考慮在排序列上建立合適的索引。
記得之前有個SQL,不加排序的話,秒級可以出結果,即響應速度很快,但是加上排序後得5或6分鐘才可以,看了下是結果集很大,又得排序造成的。
這裡簡單舉個例子吧:
3.2 例子
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu May 14 10:55:26 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:55:26 SQL> conn lhr/lhr
Connected.
12:08:08 SQL> create table test_index_lhr as select * from dba_objects;
Table created.
Elapsed: 00:00:03.70
12:08:27 SQL> insert into test_index_lhr select * from test_index_lhr;
77241 rows created.
12:08:39 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
12:08:41 SQL> set autot traceonly explain stat
12:08:41 SQL> select object_name from test_index_lhr where object_name is not null order by object_name;
154482 rows selected.
Elapsed: 00:00:01.18
Execution Plan
----------------------------------------------------------
Plan hash value: 1466335622
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 155K| 9M| | 3078 (1)| 00:00:37 |
| 1 | SORT ORDER BY | | 155K| 9M| 10M| 3078 (1)| 00:00:37 |
|* 2 | TABLE ACCESS FULL| TEST_INDEX_LHR | 155K| 9M| | 623 (1)| 00:00:08 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
10 recursive calls
6 db block gets
2808 consistent gets
614 physical reads
34996 redo size
3787521 bytes sent via SQL*Net to client
113801 bytes received via SQL*Net from client
10300 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
154482 rows processed
12:08:48 SQL> create index ind_test_inde on test_index_lhr(object_name) ;
Index created.
Elapsed: 00:00:02.45
12:08:58 SQL> EXEC dbms_stats.gather_table_stats(ownname => 'LHR', tabname=> 'test_index_lhr', cascade => TRUE );
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.62
12:09:04 SQL> select object_name from test_index_lhr where object_name is not null order by object_name;
154482 rows selected.
Elapsed: 00:00:01.35
Execution Plan
----------------------------------------------------------
Plan hash value: 712275200
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 3771K| 766 (1)| 00:00:10 |
|* 1 | INDEX FULL SCAN | IND_TEST_INDE | 154K| 3771K| 766 (1)| 00:00:10 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME" IS NOT NULL)
Note
-----
- SQL plan baseline "SQL_PLAN_8kcy12j8f3s5n2a6f2b1f" used for this statement
Statistics
----------------------------------------------------------
704 recursive calls
64 db block gets
11715 consistent gets
37 physical reads
33236 redo size
3787521 bytes sent via SQL*Net to client
113801 bytes received via SQL*Net from client
10300 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
154482 rows processed
12:09:09 SQL> select owner, object_name from test_index_lhr where object_name is not null order by object_name;
154482 rows selected.
Elapsed: 00:00:01.28
Execution Plan
----------------------------------------------------------
Plan hash value: 1466335622
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 4676K| | 1947 (1)| 00:00:24 |
| 1 | SORT ORDER BY | | 154K| 4676K| 6072K| 1947 (1)| 00:00:24 |
|* 2 | TABLE ACCESS FULL| TEST_INDEX_LHR | 154K| 4676K| | 623 (1)| 00:00:08 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
6 recursive calls
6 db block gets
2241 consistent gets
895 physical reads
680 redo size
4232382 bytes sent via SQL*Net to client
113801 bytes received via SQL*Net from client
10300 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
154482 rows processed
12:09:22 SQL> select /*+index(a,IND_TEST_INDE)*/ owner, object_name from test_index_lhr a where object_name is not null order by object_name;
154482 rows selected.
Elapsed: 00:00:09.59
Execution Plan
----------------------------------------------------------
Plan hash value: 880046030
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 154K| 4676K| 109K (1)| 00:21:57 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_INDEX_LHR | 154K| 4676K| 109K (1)| 00:21:57 |
|* 2 | INDEX FULL SCAN | IND_TEST_INDE | 154K| | 766 (1)| 00:00:10 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
6 recursive calls
4 db block gets
122955 consistent gets
2198 physical reads
724 redo size
4392715 bytes sent via SQL*Net to client
113801 bytes received via SQL*Net from client
10300 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
154482 rows processed
12:14:23 SQL>
相關連線:
【書評:Oracle查詢最佳化改寫】第一章 http://blog.itpub.net/26736162/viewspace-1652985/
...........................................................................................................................................................................................
本文作者:小麥苗,只專注於資料庫的技術,更注重技術的運用
ITPUB BLOG:http://blog.itpub.net/26736162
本文地址:http://blog.itpub.net/26736162/viewspace-1654252/
本文pdf版: 提取碼:af2d
QQ:642808185 註明:ITPUB的文章標題
...........................................................................................................................................................................................
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-1654252/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【書評: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查詢最佳化
- MySQL查詢最佳化MySql
- 第二章 :查詢與排序---------遞迴、查詢與排序補充排序遞迴
- 第二章 :查詢與排序-------希爾排序排序
- 查詢重寫
- 第二章 :查詢與排序-------二分查詢的遞迴解法排序遞迴
- Oracle 樹查詢 效能最佳化紀實(start with, connect by)Oracle
- Oracle 最佳化器與sql查詢執行順序OracleSQL
- StoneDB 子查詢最佳化
- 最佳化星型查詢
- MySQL查詢效能最佳化MySql
- [Mysql]慢查詢最佳化MySql
- Oracle 查詢Oracle
- 美團搜尋中查詢改寫技術的探索與實踐
- Oracle查詢最佳化4大方面的主要途徑Oracle
- 【效能最佳化】Oracle 效能最佳化:降低列值聚簇因子 提高查詢效率Oracle
- 慢查詢最佳化及分析
- 【Mysql】MySQL查詢最佳化-explainMySqlAI
- oracle 精確查詢和模糊查詢Oracle
- 在Oracle中進行大小寫不敏感的查詢Oracle
- oracle子查詢Oracle