SQL statement execution sequence

May 20 posted in Oracle (RSS), 122 views.

A, sql statement the implementation of these steps:
1) Syntax analysis syntax statement with the norms, measured statement of the meaning of the expression.
2) semantic analysis, to check all statements relating to the existence of database objects, and users have the appropriate permissions.
3) view of conversion, will involve the view query is converted to the corresponding base form of the query.
4) The expression of conversion, complex SQL expression is converted to a simpler expression equivalent connection.
5) Select Optimizer, Optimizer different in general have a different "implementation plan"
6) Select connection, ORACLE there are three connections on the multi-table connection ORACLE way to choose the appropriate connection.
7) Select the order to connect to multi-table select which ORACLE connection to connect to the table, select the two tables in which the source table as a data table.
8) Select the data search path, select the appropriate conditions for the basis of the above data search path, in the case of selection or use of the entire search index table or in other ways.
9) run "implementation plan"
Second, oracle shared principles of:
ORACLE will be implemented over the SQL statements stored in the shared memory pool (shared buffer pool), you can by all users to share the database when you implement a SQL statement (sometimes called a cursor), if it is before carried out exactly the same statement, ORACLE has been able to quickly resolve the statement, as well as the implementation of the best path. This feature greatly enhance the performance of the SQL and save the use of memory, oracle statement to increase the efficiency of the method of inquiry : 1: where column in (select * from ... where ...); 2: ... where exists (select 'X' from ... where ...); the second format to the first than efficient formats. Oracle can be in almost all of the IN operator subquery rewritten to use the EXISTS subquery use EXIST, Oracle will first check the system operator, and then run until it subquery to find the first match, which saves time Oracle system IN subquery in the implementation, the first implementation of sub-queries, and results in a list stored in a temporary increase in the index table to avoid the use of words to avoid having to use HAVING clause, HAVING retrieved only after all records should filter the result set. this sort to deal with the need for a total of such operations. If the WHERE clause can limit the number of records that will be able to reduce spending in this area

SQL Select statement complete the implementation of the order of:

1, from clause assemble data from different data sources;
2, where clause based on the conditions specified on the record to carry out screening;
3, group by clause will be sub-divided into a number of data;
4, calculated using the aggregation function;
5, the use of having sub-clause filter;
6, the calculation of all the expressions;
7, using the order by sorting on the result set.