SQL and PLSQL development practices

Editor's note: when talking about performance optimization and cases, we need to think about how to understand from the developer's perspective, how to execute SQL, and how to learn advanced SQL, this article provides a brief summary of the above issues.

 

▲Itpub moderator Ding Jun

 

▲Comprehensive oracle SQL Knowledge System

Speaking of the logic and physical execution sequence of SQL, Ding Jun mentioned two points: 1. Understanding the logic execution sequence of SQL can greatly reduce the compilation of erroneous SQL statements. 2. Understanding the logic execution sequence of SQL can deepen the understanding of SQL and facilitate the processing of more complex business logic.

 

▲Understand the logic and physical execution sequence of SQL

PLSQL is a good supplement to SQL.

 

▲Understand the logic of SQL and the logic sequence of physical execution.

The physical EXECUTION sequence of SQL statements is the actual EXECUTION PATH. The physical EXECUTION sequence depends on the CBO optimizer component and is very complicated.

 

▲Introduction to the logic and physical execution sequence of SQL

 

▲Understanding of the logic and physical execution sequence of SQL

 

▲Learn how to learn SQL from analysis functions

Extract analysis function features from documents:

The analysis function relies on the analysis clause to analyze the group to which the current row belongs. It is used to calculate complex inter-line and accumulative values.

The analysis function is different from the group function. According to the characteristics of 1, the analysis function can calculate the group value and retain the values of other columns in the current row. -- Important features

Analysis Function Classification: ranking (rank, dense_rank, row_number, first/last, ntile, etc.), aggregate report functions (sum, count, and ratio_to_report), row comparison (lead/lag, first_value/last_value), mathematical statistics (stddev, var_pop, etc.) and other functions.

How to Learn SQL summary from Analysis Function Learning

Analysis functions can implement complex inter-line computing functions, such as accumulative values, comparison between rows, and report statistics. To learn and analyze functions, you must grasp the characteristics of each analysis function and master the relationship between key elements in the analysis function, such as the difference between ROWS and RANGE, and the relationship between window clauses and partition and order, master the concepts of windows and current rows. ORACLE also supports custom Aggregate functions to implement analysis functions. For details, refer to Data Cartridge Developer's Guide.