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

[book Review: Oracle query Optimization rewriting] Chapter 2

BLOG document structure diagram

In the previous article, http://blog.itpub.net/26736162/viewspace-1652985/, we mainly analyzed some contents that should be paid attention to when querying a single table. Today, the second chapter is also very simple, mainly about sorting. The content of the second chapter is posted below:

Chapter 2 sorts the query results

2.1 return query results in the specified order

2.2 sort by multiple fields

2.3 sort by substring

2.4 TRANSLATE

2.5 sort by letters in a mixed numeric and alphabetic string

2.6 handle sort null values

2.7 sort the values in different columns according to the conditions

Sorting basically has nothing to say, but the book focuses on the use of translate.

I. 1 translate usage

Syntax: TRANSLATE (char, from, to)

Usage:

1. Returns the string that replaces each character that appears in from with the corresponding character in to.

two。 If the from is longer than the to string, the extra characters in from than in to will be deleted, or the extra characters in from will be considered to correspond to null in to.

3. One of the three parameters is empty, and the return value will also be null.

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 Hello bcadefg','abcdefg','1234567'), translate ('ab Hello bcadefg','1abcdefg','1') FROM dual

I. 2 sort by letters in a mixed string of numbers and letters, which is implemented by the translate function

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 >

1. 3 Optimization of order by sorting

There is a principle about SQL optimization: avoid using resource-consuming operations (DISTINCT, UNION, MINUS, INTERSECT, ORDER BY, group by, SMJ, created index)

The SQL statement with DISTINCT,UNION,MINUS,INTERSECT,ORDER BY starts the SQL engine to perform the resource-consuming sort (SORT) function. DISTINCT requires one sort operation, while others need to perform at least two sort operations.

For example, a UNION query in which each query has a GROUP BY clause, GROUP BY triggers an embedded sort (NESTED SORT); thus, each query needs to perform a sort, and then when UNION is executed, another unique sort (SORT UNIQUE) operation is executed and it can only be executed after the previous embedded sort ends. The depth of embedded sorting will greatly affect the efficiency of the query.

Usually, SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways.

The ORDER BY statement determines how Oracle sorts the returned query results. The Order by statement does not have any special restrictions on the columns to be sorted, and you can add functions to the columns (such as joins or attachments, etc.). Any non-indexed items or expressions that are evaluated in the Order by statement will slow down the query.

Carefully examine the order by statement to find non-index items or expressions, which can degrade performance. The solution to this problem is to rewrite the order by statement to use the index, or to create another index on the column used, while absolutely avoiding the use of expressions in the order by clause.

● builds indexes on columns that are sorted or grouped frequently (that is, group by or order by operations).

● if there are multiple columns to be sorted, you can build a composite index (compound index) on those columns.

Disk sorting is expensive for several reasons. First, they are particularly slow compared to memory sorting; and disk sorting consumes resources in temporary tablespaces. Oracle must also allocate buffer pool blocks to maintain blocks in temporary tablespaces. Whenever memory sorting is better than disk sorting, disk sorting will slow down tasks and affect the execution of the current task of the Oracle instance. Also, too much disk sorting will make the value of free buffer waits higher, causing blocks of data for other tasks to be removed from the buffer.

I. 3.1 Summary

(1) use index to avoid sorting: when there is more sort data

(2) get rid of unnecessary distinct, many distinct are added because programmers are not confident in their understanding of the data.

All in all, sorting is very CPU-intensive. If you don't have to sort, you don't have to sort. If you have to sort, consider creating an appropriate index on the sorted column.

Remember that there was a SQL before, without sorting, the result can be obtained in seconds, that is, the response speed is very fast, but it takes 5 or 6 minutes to add sorting.

Here's a simple example:

I. 3.2 exampl

[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 | | 623K (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 (an owner INDIZINDINDIZHESTSTESTION 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 >

Related connections:

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

. .

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-1654252/

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

QQ:642808185 notes: the title of the ITPUB article

. .

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.