Atitit.mysql the use of the Oracle with AS Mode temp table pattern CTE statement, reduces the sub-query of the structural MSSQL SQL Server.

Atitit.mysql the use of the Oracle with AS Mode temp table pattern CTE statement to reduce the structural MSSQL SQL for subqueries Server:

1. With ... as (...) in the MySQL what should be said in 1

2. Sub-query and query nesting is mainly easy to improve readability, 2

3. the solution in MYSQL :: temp table. 2

4. Seo blackheads keyword Replacement link statistics sample 2

4.1.//---------number of com sites queried 2

4.2.//----------------------number of query net sites 2

4.3.--------------- Joint Statistics 3

5. With AS + materialize is a good way to enforce subqueries first. 3

1. What should be represented in MySQL with ... as (...)

SQL statement is the most direct and most common data access and operation mode of relational database. It should be said that, among the success factors of RDBMS, the promotion of SQL language standard has indelible credit. Mainstream databases such as Oracle, SQL Server, and DB2 are based on standard SQL and are personalized to enable efficient database applications.

With AS is a statement structure that is often used within the Oracle SQL language family. Especially in some data warehouse mining systems, SQL statement structure and nested structure are very complex. With AS can help us simplify the sentence structure, improve the readability of the statement, and also improve the control of the statement execution plan.

Oracle 9i New with syntax, you can name the subquery in the query, and put it at the front of the SELECT statement.

Author :: Old Wow's paw attilax ayron, email:[email protected]

Reprint please indicate source: Http://blog.csdn.net/attilax

Db2,mssql,oracle All support the use of with ... as.

2. Sub-query and query nesting is mainly for readability improvement,

The vast majority of subqueries and nesting can be done through a series of connections. And the database system is good at connecting the operation

3. The solution in MySQL:: Temp table.

You can check the MySQL temp table creation using in the MySQL official manual.

4. SEO blackhead keyword Replacement link statistical sample

4.1.//---------EnquirycomNumber of sites

DROP Temporary TABLE

IF EXISTS tmp_table;

CREATE Temporary TABLE tmp_table SELECT

SUBSTR (URL, 1, LOCATE ('. com ', URL) + 3) as domain

From

Link_big

WHERE

LOCATE ('. com ', URL) > 0;

4.2.//----------------------EnquiryNetNumber of sites

DROP Temporary TABLE

IF EXISTS Tmp_table2;

CREATE Temporary TABLE tmp_table2

SELECT

SUBSTR (URL, 1, LOCATE ('. Net ', URL) + 3) as domain

From

Link_big

WHERE

LOCATE ('. Net ', URL) > 0;

4.3.---------------Joint Statistics

SELECT

Count (*),

Domain

From

Tmp_table

GROUP by

Domain

UNION

SELECT

Count (*),

Domain

From

Tmp_table2

GROUP by

Domain

5. With AS + materialize is a good way to enforce subqueries first.

Combined with the above discussion, we can get the conditions and benefits of the with as application. On the whole, with as can bring two benefits. The first is the definition of the sentence, the original complex sub-query content is disassembled into a number of statements block, improve the readability. Second, for the automatic temporary table, the subquery data can be retrieved first, in memory to organize the connection.

Finally, we want to discuss the characteristics of the subquery unnested. In most cases, we think that the Oracle feature is capable of generating better execution plans. However, in practice we have also found a lot of such scenarios: sub-query speed, the amount of data is not small, but the slow after the connection, the result is to find the execution plan subquery is opened.

By default, Oracle has a "small compute" feature when estimating the number of result sets for a multi-conditional statement. That is, the estimated number of result sets is less than the actual number, this is because the default statistics do not consider the reason for column correlation. Therefore, most of the query deployment is passed.

If this is the case, with AS + materialize is a good way to force a subquery to execute first.

Reference

MySQL Create and delete temporary table SQL statements -mysql tutorial - database - One poly tutorial net . htm

See with as using -realkid4-itpub blog from five SQL . htm

Atitit.mysql the use of the Oracle with AS Mode temp table pattern CTE statement, reduces the sub-query of the structural MSSQL SQL Server.