SQL statement execution sequence

Source: Internet
Author: User
1. SQL statement execution steps: 1) analyze the syntax to check whether the statement syntax complies with the specifications, and measure the meaning of each expression in the statement. 2) perform Semantic Analysis and check

1. SQL statement execution steps: 1) analyze the syntax to check whether the statement syntax complies with the specifications, and measure the meaning of each expression in the statement. 2) perform Semantic Analysis and check

1. SQL statement execution steps:
1) analyze the syntax and analyze whether the syntax of the statement complies with the specifications, to measure the meaning of each expression in the statement.
2) perform Semantic Analysis to check whether all database objects involved in the statement exist and the user has the corresponding permissions.
3) view conversion: converts query statements related to views into corresponding query statements for base tables.
4) expression conversion: converts complex SQL expressions into simple equivalent join expressions.
5) Select the optimizer. Different optimizers generally generate different "execution plans"
6) Select the connection mode. Oracle has three connection modes. You can select an appropriate connection mode for multi-Table ORACLE connection.
7) Select the connection sequence, select which table to connect to ORACLE for multi-Table connection, and select which table in the two tables as the source data table.
8) Select the data search path and select the appropriate data search path based on the preceding conditions. If you choose full table search or index or other methods.
9) run the "Execution Plan"
Ii. oracle sharing principles:
ORACLE stores the executed SQL statements in the shared buffer pool of the memory ,, it can be shared by all database users. When you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement, ORACLE can quickly obtain parsed statements and the best execution path. this function greatly improves SQL Execution performance and saves memory usage.
Iii. How to improve query efficiency using oracle statements: 1: where column in (select * from... where ...); 2 :... where exists (select 'x' from... where ...); the second format is far more efficient than the first one. IN Oracle, almost all IN operator subqueries can be rewritten to use EXIST for subqueries using EXISTS. The Oracle system first checks the primary query, then run the subquery until it finds the first match, which saves time. When the Oracle system executes the IN subquery, it first executes the subquery, store the obtained result list in a temporary table with an index. Avoid having clauses. HAVING filters the result set only after all records are retrieved. this process requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced.

The complete execution sequence of the SQL Select statement:

1. The from clause assembles data from different data sources;
2. The where clause filters record rows based on specified conditions;
3. The group by clause divides data into multiple groups;
4. Use Aggregate functions for computation;
5. Use the having clause to filter groups;
6. Calculate all expressions;
7. Use order by to sort the result set.

Column:

1. select column list from table list name/View list name where condition.

2. select column list from table list name/View list name where condition group by (column list) having Condition

3. select column list from table list name/View list name where condition group by (column list) having condition order by column list

4. select column list from table 1 join table 2 on table 1. column 1 = table 2. column 1... join table n on table n. column 1 = TABLE (n-1 ). column 1 where table 1. condition and table 2. condition... table n.

Execution sequence:

1. select

2. select

3. First where, then group, then having, then select, and then order

4. First join, then where, and then select

The complete execution sequence of the SQL Select statement:

1. The from clause assembles data from different data sources;
2. The where clause filters record rows based on specified conditions;
3. The group by clause divides data into multiple groups;
4. Use Aggregate functions for computation;
5. Use the having clause to filter groups;
6. Calculate all expressions;
7. Use order by to sort the result set.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.