SQL Execution procedure

In general, database processing SQL goes through three processes: parsing, executing, and returning results, such as when Cognos ReportNet completes the presentation layer by drag-and-drop, or generates SQL automatically, and then passes SQL to Oracle for processing.

1. Analysis

Parsing is the first step in the processing of SQL statements, which is an important step in the SQL statement processing process and contains several aspects:

(1) syntax analysis, Oracel is a database used in the bottom-up analysis method, including check syntax specification, naming specification, it is the most time-consuming and most expensive steps in SQL statements, mainly in the binding variables and stored procedures, and so on:

A. Binding variables: This is why using a binding variable in some cases can be several times more efficient and performance than the unbound mode, mainly because the bound variable is compiled only once, and then the query plan is stored in a shared pool for later retrieval and reuse of the query plan.

B. Stored procedures: Stored procedures are faster than normal SQL statements, that is, it does not need to be compiled again, but also can take advantage of many of the features in the database, making it faster than the general SQL statements much faster

(2) Semantic analysis, learning the principles of compiling all know that this step is very important, Oracle is mainly to analyze the SQL statement is the correct format, the existence of each object, and whether it has sufficient permissions to execute

(3) View conversion, it will be involved in the view of the query into a simpler equivalent connection expression, some people think that the view must be executed faster than the use of the table, in fact, not necessarily, the main specific situation of the specific analysis, as long as this article really understand, their analysis is not difficult;

(4) Expression conversion. Converts a complex SQL expression to the corresponding query statement on the base table. Standard for writing SQL

A. Do not calculate in the where sentence, such as select at_id from article where at_id >10*10000 and at_id< (10+1) *10000 is not allowed is this principle

B. Note the matching of data types

Select bk_id from book where bk_price>30 is inferior to select bk_id from book where bk_price>30.00 OK

(5) Select Optimizer, learn how to compile the principle of the time to know that the use of the optimizer will have different execution efficiency, of course, it is best to build a specific optimizer based on the bulk of SQL

(6) Select the connection mode. Informix has a simple merge join and sort-merge connection technology, mainly to see whether to use the index problem.

(7) Select the connection order. Multi-table connection, Oracle selects the one to first connect to the table and select the Source data table

(8) Select the data search path, according to the above criteria to select the appropriate data search the Soviet path, such as the use of full-table search or index or other ways to search

(9) A shared SQL area is found in SQL for the SQL statement, and if there is already a shared SQL area, the content of the share is executed directly.

2. Perform

The main thing is that when you use the update and DELETE statements, you must lock the rows to prevent other users from modifying them. Oracle first looks for the desired block of data from the database buffer and, if it exists, reads it directly or modifies it, or reads it from the physical file into the database buffer.

3. return results

The statement that needs to return the result for the SELECT statement, first of all to see if the order needs to be sorted, then returned to the user after sorting, and then depending on the size of the memory, you can fetch one row of data at a time, one for each set of data. At this point, it may be necessary to use the data structure of the external sorting, and sorting algorithms, such as memory permitting, as far as possible to the General Assembly to mention high performance;

Finally, summarize the processing steps for the select used by durable:

Open cursor
|
---------view shared SQL area, do you have the same SQL statement
| |
| Analysis
| |
| Defined
------------------------ |
|
-------------------------Bound variables
| |
| Bundle
--------------- ----------- |
|
Parallel processing
|
Execute Query
|
return results
|
Close Cursors