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

[book Review: Oracle query Optimization rewriting] chapters 5 to 13

1. 1 BLOG document structure map

I. 2 preface part 1. 2.1 introduction

Technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) Olympiad:

Processing of ① string

Common analysis functions of ②

③ uses sql to output 99 multiplication tables.

If there are any mistakes or imperfections in this article, please correct them as much as you can, ITPUB or QQ. Your criticism is the biggest motivation of my writing.

I. 2.2 introduction to the experimental environment

Oracle 11g

I. 2.3 links to related reference articles

The links in the first 4 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/

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

I. 2.4 introduction to this article

We are strange, why not write a chapter, directly jump, wheat seedlings tell you, because the content of chapters 5 to 13 is mostly the content of development, and SQL tuning is too far away, here listed the contents of these chapters, although it is the content of the development of sql, but many examples are still more practical, such as the application of translate function.

Chapter 5 deals with strings, including translate functions and parsing functions for individual oracle.

Chapter 6 deals with numbers and introduces analysis functions

Chapters 7 and 8 cover common uses of the DATE type.

Chapter 9 still introduces analysis functions.

Chapter 10 focuses on the paging of the result set

Chapter 11 describes the column-column conversion function, using UNPIVOT to optimize UNION ALL, and ROLLUP and CUBE can let you write less UNION ALL statements.

Chapter 12 explains tree query

Chapter 13 selects some netizens' demand cases, hoping that readers can find ways to realize their needs through the inspiration of these cases.

Chapter 5 using strings

5.1 traversal string

5.2 string text contains quotation marks

5.3 calculate the number of times a character appears in a string

5.4 remove unwanted characters from a string

5.5 separating character and numeric data

5.6 queries contain only alphabetic or numeric data

5.7 extract the initials of a name

5.8 sort by numeric value in a string

5.9 create a delimited list based on the rows in the table

5.10 extract the nth separated substring

5.11 decompose IP address

5.12 convert delimited data to multi-valued IN lists

5.13 arrange strings alphabetically

5.14 distinguish a string that can be used as a numeric value

Chapter 6 using numbers

6.1 Common aggregation functions

6.2 generate cumulative sum

6.3 calculation of cumulative difference

6.4 change the value of the cumulative sum

6.5 return to the top three employees in each department

6.6 calculate the value with the most occurrences

6.7 returns the row data of the most valuable value

6.8 first_value

6.9 the percentage of the sum

Chapter 7 date Operation

7.1 plus or minus day, month, year

7.2 add and subtract hours, minutes, seconds

7.3 hours, minutes, seconds between dates

7.4 the day, month and year of the date interval

7.5 determine the number of working days between two dates

7.6 calculate the number of days in the week of the year

7.7 determine the number of days of difference between the current record and the next record

Chapter 8 date Operations

8.1 Information available to SYSDATE

8.2 INTERVAL

8.3 EXTRACT

8.4 determine whether a year is a leap year

8.5 week calculation

8.6 determine all dates in a year that belong to a day of the week

8.7 determine the date of the first and last "day of the week" in a month

8.8 create a calendar for the month

8.9 full year Calendar

8.10 determine the start and end dates of the quarter in the specified year

8.11 missing values within the supplementary range

8.12 search based on a given unit of time

8.13 Special part of the use date comparison record

8.14 identify overlapping date ranges

8.15 summarize data at specified intervals

Chapter 9 scope processing

9.1 locate the range of continuous values

9.2 find the difference between rows in the same group or partition

9.3 locate the start and end points of a continuous range of values

9.4 merge time period

Chapter 10 Advanced find

10.1 pagination the result set

10.2 regenerate the room number

10.3 Skip n rows in the table

10.4 weight removal by permutation and combination

10.5 find the record that contains the maximum and minimum values

Chapter 11 report and data Warehouse Operations

11.1 Row to column

11.2 column Wrap

11.3 invert the result set to a column

11.4 suppress duplicate values in the result set

11.5 use "row-to-column" for calculation

11.6 data packets

11.7 pairs of data packets

11.8 simple subtotals

11.9 identify rows that are not subtotal

11.10 calculate the subtotal of all expression combinations

11.11 Distribution of personnel in the workplace

11.12 create sparse matrix

11.13 aggregation for different groups / partitions at the same time

11.14 aggregate the values of the moving range

11.15 windowed explanation of commonly used analytical functions

11.16 listagg and Xiao 99

Chapter 12 hierarchical query

12.1 simple tree query

12.2 Root node, branch node, leaf node

12.3 sys_connect_by_path

12.4 sorting in tree query

12.5 WHERE in tree query

12.6 A branch of the query tree

12.7 cut off a branch

Removing duplicates by list values in the 12.8 field

Chapter 13 Application case implementation

13.1 extract the elements of a string from an unfixed position

13.2 search for alphanumeric strings

13.3 classify the results and convert them to columns

13.4 the importance of building basic data

13.5 return data in different columns according to incoming conditions

13.6 split strings for concatenation

13.7 sorting out junk data

13.8 use "row-to-column" to get implicit information

13.9 Row transfer with hidden data

13.10 using regular expressions to extract text format recordsets in clob

Below, I would like to share with you what I am interested in according to different chapters:

I. 3 the handling of partial content strings in chapter 5. 3.1 traversal strings

Create or replace view v as

Select 'every day up' as Chinese characters, 'TTXS' as first spell from dual

Select v. Chinese characters, v. First spell, level from v connect by level

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.