Proficient in Oracle SQL (2nd edition) PDF

One: Download route

II: Book Drawing

Three: Catalogue

The 1th Chapter SQL core
1.1 SQL language
1.2 Interface of the database
1.3 Sql*plus Review
1.3.1 Connecting to a database
1.3.2 Configuring the Sql*plus environment
1.3.3 Execute command
1.4 5 Core SQL statements
1.5 SELECT statement
1.5.1 FROM clause
1.5.2 WHERE clause
1.5.3 GROUP BY clause
1.5.4 HAVING clause
1.5.5 SELECT list
1.5.6 ORDER BY clause
1.6 INSERT statement
1.6.1 Single Table Insertion
1.6.2 Multi-table Insert
1.7 UPDATE statement
1.8 DELETE Statement
1.9 Merge Statement
1.10 Summary

The 2nd Chapter SQL execution
2.1 Oracle Architecture Fundamentals
2.2 SGA Shared Pool
2.3 Library Cache
2.4 Exactly the same statement
2.5 SGA Buffer Cache
2.6 Query Conversions
2.6.1 Query block
2.6.2 View Merge
2.6.3 Sub-query solution nesting
2.6.4 Coupling elimination
2.6.5 Sort Elimination
2.6.6 Predicate Propulsion
2.6.7 using materialized views for query rewriting
2.7 Determining the execution plan
2.8 Execute plan and get data row
2.9 SQL Execution-Overview
2.10 Summary

Chapter 3rd Access and Linking methods
3.1 Full Scan access method
3.1.1 How to select Full scan operation
3.1.2 Full Scan and discard
3.1.3 Full scan and multiple block reads
3.1.4 full scan and high water mark line
3.2 Index Scan access method
3.2.1 Index Structure
3.2.2 Index Scan Type
3.2.3 Index Unique Scan
3.2.4 Index Range Scan
3.2.5 Index Full Scan
3.2.6 Index Jump Scan
3.2.7 Index Fast Full scan
3.3 Coupling method
3.3.1 Nested Loops Join
3.3.2 Sort-merge junction
3.3.3 Hash Junction
3.3.4 Descartes Junction
3.3.5 outer coupling
3.4 Summary

The 4th chapter is about the collection of SQL
4.1 Thinking in a set-oriented way
4.1.1 from process-oriented to set-based mode of thinking
4.1.2 process-oriented and set-based thinking: an example
4.2 Integrated operations
4.2.1 Union and UNION ALL
4.2.2 Minus
4.2.3 INTERSECT
4.3 Set and Null values
4.3.1 null and non-visual results
4.3.2 Null value and set operation
4.3.3 Null value with GROUP BY and order by
4.3.4 Null value and aggregation function
4.4 Summary

The 5th Chapter raises questions
5.1 Ask a good question
5.2 Purpose of the question
5.3 Types of problems
5.4 Problems with the problem
5.5 Problems with Data
5.6 Creating a logical expression
5.7 Summary

6th. SQL Execution Plan
6.1 Interpretation Plan
6.1.1 Use interpretation plan
6.1.2 Plan_table
6.1.3 Decomposition plan
6.1.4 reasons for the failure to explain the plan
6.1.5 Reading Program
6.1.6 Accessing and filtering predicates
6.1.7 make the plan easy to read
6.2 Execution Plan
6.2.1 to view recently generated SQL statements
6.2.2 View related execution plans
6.2.3 Collecting Execution Plan statistics
6.2.4 Identifying SQL statements for later retrieval of plans
6.2.5 in-depth understanding of Dbms_xplan
6.2.6 Using SQL monitoring reports
6.2.7 using planning information to solve problems
6.3 Summary

7th Chapter Advanced Grouping
7.1 Basic GROUP by usage
7.2 HAVING clause
7.3 GROUP by's "new" feature
7.4 GROUP by Cube extension
Practical application of 7.5 cube
7.6 Using the grouping () function to exclude null values
7.7 Extended Report with grouping ()
7.8 Extended Report with grouping_id ()
7.9 GROUPING Sets and Rollup ()
7.10 GROUP by limitations
7.11 Summary

8th analysis Function
8.1 Overview
8.2 Sample Data
8.3 parsing function anatomy
8.4 Function List
8.5 aggregate functions
8.5.1 aggregate functions that span the entire partition
8.5.2 Fine-grained window declarations
8 .5.3 Default window declares
8.6 lead and Lag
8.6.1 syntax and sort
8.6.2 Example 1: Returns a value from the previous row
8.6.3 Understanding the displacement of the data row
8.6.4 Example 2: Returns a value from the next row
8.7 fi　Rst_value and Last_value
8.7.1 Example: Use First_value to calculate the maximum value
8.7.2 Example: Use Last_value to calculate the minimum value
8.8 Other analytic functions
8.8.1 Nth_value
8.8.2 rank
8.8.3 dense_rank
8.8.4 row_number
8.8.5 ratio_to_report
8.8.6 percent_rank
8.8.7 Perce Ntile_cont
8.8.8 percentile_disc
8.8.9 ntile
8.8.10 stddev
8.8.11 listagg
8.9 performance Tuning
8.9.1 Execution Plan
8.9.2 predicate
8.9.3 Index
8.10 Advanced Topic
8.10.1 Dynamic SQL
8.10.2 Nested analysis Functions
8.10.3 Parallel
8.10.4 PGA size
8.11 Organizational Behavior
8. 12 Summary

9th Chapter MODEL clauses
9.1 Spreadsheets
9.2 Using the model clause to implement cross-row references
9.2.1 Sample Data
9.2.2 Parsing model clauses
9.2.3 Rules
9.3 Position and symbol references
9.3.1 Position Markers
9.3.2 symbol Markers
9.3.3 for Loop
9.4 Returns the updated row
9.5 Solve Order
9.5.1 Row Solve Order
9.5.2 Rule Solving Order
9.6 Aggregation
9.7 iterations
9.7.1 Example
9.7.2 Presentv and Null values
9.8 lookup Table
9.9 Null value
9.10 Performance tuning using the model clause
9.10.1 Execution Plan
9.10.2 predicate Propulsion
9.10.3 materialized View
9.10.4 Parallel
9.10.5 partition in execution of the model clause
9.10.6 Index
9.11 factorial of sub-query
9.12 Summary

10th Zhang Ziyi (of query factor
10.1 Standard usage
10.2 Use with to define PL/SQL functions
10.3 SQL Optimization
10.3.1 Test Execution Plan
10.3.2 the impact of test query changes
10.3.3 looking for other optimization opportunities
10.3.4 applying subquery factorial to PL/SQL
10.4 Recursive subqueries
10.4.1 CONNECT by example
10.4.2 RSF Example
Restrictions on 10.4.3 RSF
10.4.4 different points from connect by
10.5 Copy the function of Connect by
10.5.1 level Pseudo-column
10.5.2 Sys_connect_by_path function
10.5.3 connect_by_root operator
10.5.4 connect_by_iscycle Pseudo-column and nocycle parameters
10.5.5 connect_by_isleaf Pseudo-column
10.6 Summary

Chapter 11th Semi-coupling and inverse coupling
11.1 Half Coupling
11.2 Semi-junction execution plan
11.3 Controlling the semi-junction execution plan
11.3.1 using hints to control the semi-junction execution plan
11.3.2 controlling the semi-junction execution plan at the instance level
11.4 Semi-junction limiting conditions
11.5 Semi-coupling requirements
11.6 Anti-coupling
11.7 Anti-coupling execution plan
11.8 Control anti-coupling execution plan
11.8.1 using hints to control anti-junction execution plans
11.8.2 control anti-junction execution plan at the instance level
11.9 anti-junction restriction conditions
11.10 Anti-coupling requirements
11.11 Summary

12th Chapter Index
12.1 Understanding Indexes
12.1.1 when to use the index
Selection of 12.1.2 columns
12.1.3 Empty value problem
12.2 Index Structure type
12.2.1 B-Tree Index
12.2.2-bit Graph index
12.2.3 Index Organization table
12.3 Partition Index
12.3.1 Local Index
12.3.2 Global Index
12.3.3 hash partition and range partitioning
12.4 solutions that match the application characteristics
12.4.1 Compression Index
12.4.2 Function-based indexes
12.4.3 Reverse Key index
12.4.4 Descending Index
12.5 Solutions to management problems
12.5.1 Non-visible index
12.5.2 Virtual Index
12.5.3-bit Graph junction index
12.6 Summary

13th. Content other than Select
13.1 INSERT
13.1.1 Direct Path Insertion
13.1.2 Multi-table Insert
13.1.3 Conditional Insertion
13.1.4 DML error Log
13.2 UPDATE
The difference between 13.2.1 CTAs and update
13.2.2 the difference between INSERT append and update
13.3 DELETE
13.4 MERGE
13.4.1 syntax and usage
13.4.2 Performance Comparison
13.5 Summary

Chapter 14th Transaction Processing
14.1 What is a transaction
14.2 acid properties of a transaction
14.3 Transaction ISOLATION LEVEL
14.4 Multi-Version Read consistency
14.5 Transaction Control Statements
14.5.1 Commit (Submit)
14.5.2 savepoint (save point)
14.5.3 Rollback (rollback)
14.5.4 set Transaction (SET transaction)
14.5.5 set Constraints (set constraint)
14.6 grouping operations into transactions
14.7 Order Entry Mode
14.8 Active transactions
14.9 using save points
14.10 Serialization Transactions
14.11 Isolating transactions
14.12 Autonomous Affairs
14.13 Summary

The 15th chapter test and Quality Assurance
15.1 Test Cases
15.2 test methods
15.2.1 Unit Test
15.2.2 regression test
15.2.3 Mode Modification
15.2.4 Repeating unit tests
15.3 Execution Plan Comparison
15.4 Performance measurement
15.4.1 Adding performance measurements to your code
15.4.2 Performance Test
15.5 Destructive Testing
15.6 Using performance measurements for system overhaul
15.7 Summary

16th. Plan stability
16.1 Program Instability: Understanding the problem
16.1.1 Statistical information changes
16.1.2 changes in the operating environment
16.1.3 changes to SQL statements
16.1.4 binding Variable Peering
16.1.5 Adaptive cursor Sharing
16.2 Statistics Feedback
16.3 Identifying the instability of the execution plan
16.3.1 fetching data from the currently running query
16.3.2 View the performance history of a statement
16.3.3 aggregate statistics by execution plan
16.3.4 finding the statistical variance of the execution plan
16.3.5 check for deviations near a point in time
16.4 Summary

The 17th Chapter Plan control
17.1 Execution Plan control: solving Problems
17.1.1 adjusting the query structure
17.1.2 proper use of constants
17.1.3 give optimizer some hints
17.2 Execution Plan control: no direct access to code
17.2.1 option 1: Change statistics
17.2.2 option 2: Changing database parameters
17.2.3 option 3: Add or Remove access paths
17.2.4 Option 4: Apply a hint-based execution plan control mechanism
17.2.5 outline
17.2.6 SQL profile
17.2.7 SQL Execution Plan baselines
17.2.8 SQL Patch
17.2.9 Summary of the scheme control mechanism based on cue
17.3 Summary

18th Other SQL structure
18.1 logical structure of a piece
18.1.1 DECODE
18.1.2 case
18.1.3 NVL, NVL2 and coalesce
18.1.4 Nullif
18.2 Pivot/unpivot Query
18.2.1 PIVOT
18.2.2 UNPIVOT
18.3 SQL to generate test data
What kind of data 18.3.1 want to get
18.3.2 CONNECT by
18.3.3 model clause
18.3.4 recursive with clause
Summary of 18.3.5 data generation
18.4 Summary

Proficient in Oracle SQL (2nd edition) PDF