當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

oracle书评,【书评:Oracle查询优化改写】第二章

發(fā)布時間：2025/4/5 编程问答 17 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle书评,【书评:Oracle查询优化改写】第二章小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

BLOG文檔結(jié)構(gòu)圖

在上一篇中http://blog.itpub.net/26736162/viewspace-1652985/，我們主要分析了一些單表查詢的時候需要注意的內(nèi)容，今天第二章也很簡單，主要是關(guān)于排序方面的內(nèi)容，以下貼出第二章的內(nèi)容：

第 2 章給查詢結(jié)果排序

2.1 以指定的次序返回查詢結(jié)果

2.2 按多個字段排序

2.3 按子串排序

2.4 TRANSLATE

2.5 按數(shù)字和字母混合字符串中的字母排序

2.6 處理排序空值

2.7 根據(jù)條件取不同列中的值來排序

排序基本上沒有什么可以講的，不過書中著重介紹了下translate的用法。

一.1translate用法

語法：TRANSLATE(char, from, to)

用法：

1.返回將出現(xiàn)在from中的每個字符替換為to中的相應字符以后的字符串。

2.若from比to字符串長，那么在from中比to中多出的字符將會被刪除，或者認為from中多出的字符在to中與空對應

3.三個參數(shù)中有一個是空，返回值也將是空值。

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;

Elapsed: 00:00:00.00

SELECTtranslate('ab 你好 bcadefg','abcdefg','1234567'),translate('ab 你好 bcadefg','1abcdefg','1')FROMdual;

一.2按數(shù)字和字母混合字符串中的字母排序，采用translate函數(shù)來實現(xiàn)

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 ADAMSADAMS

7499 ALLENALLEN

7698 BLAKEBLAKE

7782 CLARKCLARK

7902 FORDFORD

7900 JAMESJAMES

7566 JONESJONES

7839 KINGKING

7654 MARTINMARTIN

7934 MILLERMILLER

7788 SCOTTSCOTT

7369 SMITHSMITH

7844 TURNERTURNER

7521 WARDWARD

9001 lastwinerlastwiner

9000 lastwinerlastwiner

16 rows selected.

Elapsed: 00:00:00.10

09:55:33 SQL>

一.3關(guān)于order by排序的優(yōu)化

關(guān)于SQL優(yōu)化中有一個原則叫：避免使用耗費資源的操作(DISTINCT、UNION、MINUS、INTERSECT、ORDER BY、group by、SMJ、created index)

帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執(zhí)行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序.

例如,一個UNION查詢,其中每個查詢都帶有GROUP BY子句, GROUP BY會觸發(fā)嵌入排序(NESTED SORT) ; 這樣, 每個查詢需要執(zhí)行一次排序, 然后在執(zhí)行UNION時, 又一個唯一排序(SORT UNIQUE)操作被執(zhí)行而且它只能在前面的嵌入排序結(jié)束后才能開始執(zhí)行. 嵌入的排序的深度會大大影響查詢的效率.

通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫.

ORDER BY語句決定了Oracle如何將返回的查詢結(jié)果排序。Order by語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中(象聯(lián)接或者附加等)。任何在Order by語句的非索引項或者有計算表達式都將降低查詢速度。

仔細檢查order by語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在order by子句中使用表達式。

●在頻繁進行排序或分組(即進行g(shù)roup by或order by操作)的列上建立索引。

●如果待排序的列有多個，可以在這些列上建立復合索引(compound index)。

磁盤排序的開銷是很大的，有幾個方面的原因。首先，和內(nèi)存排序相比較，它們特別慢；而且磁盤排序會消耗臨時表空間中的資源。Oracle還必須分配緩沖池塊來保持臨時表空間中的塊。無論什么時候，內(nèi)存排序都比磁盤排序好，磁盤排序?qū)钊蝿兆兟?#xff0c;并且會影響Oracle實例的當前任務的執(zhí)行。還有，過多的磁盤排序?qū)頵ree buffer waits的值變高，從而令其它任務的數(shù)據(jù)塊由緩沖中移走。

一.3.1總結(jié)

(1)采用索引避免排序：排序數(shù)據(jù)較多時

(2)去掉不必要的distinct，很多distinct是由于程序員對數(shù)據(jù)的了解不自信而多加的。

總而言之，排序是非常耗費CPU資源的，能不排序就不要排序，如果非得排序，可以考慮在排序列上建立合適的索引。

記得之前有個SQL，不加排序的話，秒級可以出結(jié)果，即響應速度很快，但是加上排序后得5或6分鐘才可以，看了下是結(jié)果集很大，又得排序造成的。

這里簡單舉個例子吧：

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

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

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

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

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

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

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

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

0sorts (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

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

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

|?? 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_lhrawhere object_name is not null order by object_name;

154482 rows selected.

Elapsed:00:00:09.59

Execution Plan

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

Plan hash value: 880046030

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

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

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

《新程序員》：云原生和全面數(shù)字化實踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的oracle书评,【书评:Oracle查询优化改写】第二章的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇：早期坦克支援战车概念非常实用
下一篇： php try报错程序中断,php7异常