Shulou(Shulou.com)06/01 Report--

[book Review: Oracle query Optimization rewriting] Chapter 14 concluding Chapter

I. 1 links to related reference articles

The links in the first 13 chapters refer to related links:

[book Review: Oracle query Optimization rewriting] Chapter 1 http://blog.itpub.net/26736162/viewspace-1652985/

[book Review: Oracle query Optimization rewriting] Chapter 2 http://blog.itpub.net/26736162/viewspace-1654252/

[book Review: Oracle query Optimization rewriting] Chapter 3 http://blog.itpub.net/26736162/viewspace-1660422/

[book Review: Oracle query Optimization rewriting] Chapter 4 http://blog.itpub.net/26736162/viewspace-1661906/

[book Review: Oracle query Optimization rewriting] Chapter 5 ~ 13 http://blog.itpub.net/26736162/viewspace-1665934/

Column-column interchange content link: http://blog.itpub.net/26736162/viewspace-1272538/

It's all the fault of scalar quantum query: http://blog.itpub.net/26736162/viewspace-1222431/

I. 2 A brief introduction to this article

Chapter 14 of this book selects cases that can cover most of the current rewriting methods. Originally, I didn't intend to write the last blog, but I think I have a beginning and an end, so I'd better write the last article. Why don't you want to write? I still feel that there is nothing to write about. The scope of sql optimization and rewriting is too wide, and the examples given in the book are just throwing a brick to attract jade. Different environments still have to be rewritten according to specific circumstances. For example, the author of this book thinks that scalar quantum query has poor performance and does not like to use it, but according to my experience, scalar quantum query is better in some cases. For example, when the response time of SQL is required, scalar quantum query is better.

Chapter 14 contents:

Chapter 14 case sharing of rewriting and tuning

14.1 Why is scalar quantum query not recommended

14.2 optimize scalar quantum query with LEFT JOIN

14.3 aggregate rewriting of scalar quantum queries optimized by LEFT JOIN

14.4 optimizing scalar quantum query with LEFT JOIN and row-transfer column

14.5 there is ROWNUM = 1 in scalars

14.6 scalar quantum query rewriting of unequal joins (1)

14.7 scalar quantum query rewriting of unequal joins (2)

The consistency between 14.8 scalar Quantum query and rewriting Logic

14.9 optimizing scalar Quantum query with Analytical function (1)

14.10 optimizing scalar Quantum query with Analytical function (2)

14.11 optimizing scalar Quantum query with Analytical function (3)

14.12 optimizing Scalar Quantum query with Analytical function (4)

14.13 optimize UPDATE 281 with MERGE rewriting

14.14 rewrite UPDATE with aggregation operation with MERGE (1)

14.15 rewrite UPDATE with aggregation operation with MERGE (2)

14.16 rewrite multiple subqueries of UPDATE with MERGE (1)

14.17 rewrite multiple subqueries of UPDATE with MERGE (2)

14.18 problems encountered when rewriting UPDATE to MERGE

14.19 organize and optimize paging statements

14.20 Let the paging statement go to the correct PLAN

14.21 remove DISTINCT from paging query

14.22 reduce self-association with WITH statement

14.23 optimize query by rewriting WITH

14.24 change OR to UNION with WITH

14.25Error WITH rewriting

14.26 incorrect usage of analysis function

14.27 optimizing multiple subqueries with LEFT JOIN (1)

14.28 optimize multiple subqueries with LEFT JOIN (2)

14.29 optimizing multiple subqueries with LEFT JOIN (3)

14.30 remove FILTER caused by EXISTS

14.31 overlap time count

14.32 rewrite optimization with analysis function

14.33 parts suppliers of equal sets

14.34 equal collection of hangars and pilots

14.35 rewrite the maximum filter condition with analysis function

14.36 use a tree query to find data at a specified level

14.37 Row and column Wrap

14.38 UPDATE, ROW_NUMBER and MERGE

14.39 rewrite optimized UPDATE statement

14.40 rewrite optimized UNION ALL statements

14.41 tangled MERGE statements

14.42 remove UNION ALL with CASE WHEN

14.43 inappropriate WITH and Standard Quantum query

14.44 optimize scalar quantum query with analysis function plus "row-to-row"

14.45 deal with problems with analytical functions

14.46 rewrite Table A multiple columns Associated with Table B with the same column

14.47 rewrite the most value statement with the analysis function

14.48 semi-joins and indexes associated with multiple columns

14.49 optimize autocorrelation by using analysis function skillfully

14.50 tangled UPDATE statements

14.51 skillfully using JOIN conditions to merge UNION ALL statements

14.52 remove NOT IN with Analytical function

14.53 read the tailoring statement of the requirements in the query

14.54 remove EXISTS from FILTER, learn how to use it.

One. 3 summary

Chapter 14 also does not give an example, about the individual optimized several sql, you can refer to the article link.

SQL optimization series

[recommended] the clever event of using function in index column in SQL optimization

Http://blog.itpub.net/26736162/viewspace-1329880/

[recommended] how does oracle estimate the size of the index to be created

Summary of oracle Row-Row Exchange

Http://blog.itpub.net/26736162/viewspace-1272538/

[recommended] explanation of under any table/view permissions

Http://blog.itpub.net/26736162/viewspace-1266876/

[recommended] about view vw_sql_rubbish_monitor_lhr

Http://blog.itpub.net/26736162/viewspace-1262559/

[recommended] inaccurate statistics lead to Cartesian product of the execution plan

Http://blog.itpub.net/26736162/viewspace-1254942/

[recommended] a bug of 11.2.0.2 SPM

Http://blog.itpub.net/26736162/viewspace-1248506/

[recommended] change update to merge (max+decode)

Http://blog.itpub.net/26736162/viewspace-1244055/

[recommended] change the cursor to build a table to improve efficiency

Http://blog.itpub.net/26736162/viewspace-1244050/

[recommended] A slow optimization of insert insert statement

Http://blog.itpub.net/26736162/viewspace-1244044/

[recommended] it's all caused by scalar quantum queries.

Http://blog.itpub.net/26736162/viewspace-1222431/

[recommended] use the unrelated form of merge sentence to show the power again.

Http://blog.itpub.net/26736162/viewspace-1222423/

[recommended] non-associative form of MERGE statement is used to improve performance-post

Http://blog.itpub.net/26736162/viewspace-1222417/

[recommended] v$diag_alert_ext view of alarm log of Oracle

Http://blog.itpub.net/26736162/viewspace-1221559/

[recommended] non-associative form of MERGE statement is used to improve performance

Http://blog.itpub.net/26736162/viewspace-1218671/

[recommended] from the estimated number of rows of the execution plan, we can see whether the execution plan is correct.

Http://blog.itpub.net/26736162/viewspace-1209796/

[recommended] Why is the index still like a snail?

Http://blog.itpub.net/26736162/viewspace-1208814/

One. 4 about me

. .

The author: wheat seedlings, only focus on the technology of database, pay more attention to the application of technology.

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

Address: http://blog.itpub.net/26736162/viewspace-1669247/

Pdf version of this article: http://yunpan.cn/QCwUAI9bn7g7w extraction code: af2d

If you add QQ to QQ:642808185, please indicate the title of the article you are reading

Time and place of creation: 2015-05-24 09: 00-2015-05-24 11:40 in Tangzhen

. .

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.