Oracle SQL statement Handling process

oracle| Process | statement

Starting in 07, a new company, the database using oracle10g, has been using Informix and Msserver, feel the Oracle function is really strong, more than Informi and Msserver are better, the architecture and management of a lot of changes , but the use of two months down, in fact, the basic principle of the database is the same, now combined with previous work experience and learning information, summed up some knowledge of Oracle database, today, first write Oracle SQL statement processing process:

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

1. Analysis

Analysis is the first step in processing SQL statements, which is an important step in the SQL statement processing process, and it includes several aspects:

(1) The grammar analysis, Oracel is uses the database commonly used bottom-up analysis method, contains examines the grammar specification, the naming specification, it is the processing SQL statement most consumes the time and the most expensive step, mainly manifests in the binding variable and the stored procedure and so on aspect:

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

B. Stored procedures: A stored procedure is faster than a typical SQL statement, it does not have to be compiled again, and it can take advantage of many features in the database, making it faster than a normal SQL statement

(2) Semantic analysis, learned that the principle of compiling all know that this step is very important, Oracle is mainly to analyze the correct format of SQL statements, the existence of individual objects, and whether it has sufficient permissions to execute

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

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

A. Do not compute 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 matching of data types

The select bk_id from the book where bk_price>30 is not comparable to the select bk_id from book where bk_price>30.00.

(5) Select the optimizer, learned 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 be able to according to the bulk of the SQL to make a specific optimizer

(6) Select the connection mode. Informix has a simple merge connection and sorting-merge connection technology, mainly to see if the problem of using indexes.

(7) Select the connection order. Multiple table joins, Oracle selects the one to connect to the table and select the source datasheet

(8) Select the data search path, according to the above conditions to choose the right data search path, such as the use of the whole table search or index or other ways to search

(9) To find a shared SQL area for the SQL statement in SQL, if there is already a shared SQL area, execute the shared content directly.

2. Perform

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

3. return results

For a SELECT statement that needs to return a result, first look at whether you need to sort, then return to the user after sorting, then, depending on the size of the memory, you can fetch one row of data at a time At this point, you may want to use the data structure of the external sorting, and sorting algorithms, so if the memory allows, as far as possible the assembly to raise high-performance;

Finally, summarize the process steps for using the select:

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