Chapter 45 SQL command from (I)

Time:2022-5-7

Chapter 45 SQL command from (I)

A select clause that specifies one or more tables to query.

outline

SELECT ... FROM [optimize-option] table-ref [[AS] t-alias][,table-ref [[AS] t-alias]][,...]

parameter

  • optimize-optioN – optional – specifies a single keyword or a series of keywords separated by spaces for query optimization options (optimizer tips). The following keywords are supported:%ALLINDEX%FIRSTTABLE TABLE TABNAME%FULL%IGNOREINDEX NAME%INORDER%NOFLATTEN%NOMERGE%NOREDUCE%NOSVSO%NOTOPOPT%NOUNIONOROPT%PARALLELand%STARTTABLE
  • table-ref-One or more tables, views, table valued functions, or subqueries from which data is retrieved, comma separated lists, or usingJOINSyntax assignment.
    In use withJOINThere are some restrictions on the syntax of the view.
    You can specify a subquery enclosed in parentheses.
  • AS t-alias-Optional – alias of the table name.
    Must be a valid identifier.

describe

FROMClause specified inSELECTStatement to query one or more tables (or views or subqueries) of data.
If there is no query table data, thenFROMClause is optional, as described below.

Multiple tables are specified as comma separated lists, or by otherJOINSyntax separated list.
You can provide an alias for each table name.

staySELECTTable name aliases are used when specifying field names for multiple tables in a statement.
IfFROMClause specifies two (or more) tables, which can be specified bytablenameTo indicate which table fields are required.
SELECT SELECT -itemThe field name of each field in the clause.
Since table names are usually long names, short table name aliases are useful in this context(t-alias.fieldname)。

The following example shows the use of table name aliases:

SELECT e.Name,c.Name
FROM Sample.Company AS c,Sample.Employee AS e

The as keyword can be omitted.
It is provided for compatibility and clarity.

Provide the schema name to the table reference

table-refThe name can be qualified(schema.tablename)Or unrestricted(tablename)。
The schema name of an unqualified table name (or view name) is provided using the schema search path or the system wide default schema name:

  1. If a schema search path is provided, iris searches for matching table names in the specified schema.
  2. If no pattern search path is provided or no match is generated from the pattern search path, iris will use the system wide default pattern name.

Table connection

When inFROMClause, SQL will perform join operations on these tables.
The type of join performed is specified by the join keyword phrase or symbol between each pair of table names.
When two table names are separated by commas, a cross join is performed.

The order in which joins are executed is determined automatically by the SQL query optimizer, not based on the order of the tables listed in the query.
If necessary, you can control the order in which joins are executed by specifying query optimization options.

The following threeSELECTStatement displays the number of rows in two separate tables and the number of rows that specify two tablesSELECTNumber of rows. The latter produces a larger table, the Cartesian product, in which each row in the first table matches each row in the second table. This operation is called cross join(Cross Join)。

SELECT COUNT(*)
FROM Sample.Company
SELECT COUNT(*)
FROM Sample.Vendor
SELECT COUNT(*)
FROM Sample.Company,Sample.Vendor

fromSample.Company、Sample.VendorSelect count from(*)

SELECT COUNT(*)
FROM Sample.Company CROSS JOIN Sample.Vendor

In most cases, a large amount of data replication of cross connections is not desirable, while some other types of connections are preferred.

If inSELECTSpecified in statementWHEREClause, perform a cross join, and thenWHEREClause predicate determines the result set. This is equivalent to usingONClause to perform an inner join. Therefore, the following two examples return the same result:

SELECT p.Name,p.Home_State,em.Name,em.Office_State
FROM Sample.Person AS p, Sample.Employee AS em
WHERE p.Name %STARTSWITH 'E' AND em.Name %STARTSWITH 'E'
SELECT p.Name,p.Home_State,em.Name,em.Office_State
FROM Sample.Person AS p INNER JOIN Sample.Employee AS em
ON p.Name %STARTSWITH 'E' AND em.Name %STARTSWITH 'E'

Query optimization options

By default, the SQL query optimizer uses complex and flexible algorithms to optimize the performance of complex queries involving join operations and / or multiple indexes. In most cases, these defaults provide the best performance. However, in rare cases, you may want to provide “hints” to the query optimizer to specify one or more aspects of query optimization. Therefore, SQLFROMProvided in ClauseOPTIMIZE-OPTIONkeyword. You can specify multiple optimization keywords in any order, separated by spaces.

Can be in a simpleSELECTStatementCREATE VIEW DEFINITION SELECTStatement orFROMSubquery of clauseSELECTUsed in statementsOPTIMIZE-OPTION FROMClause keyword.

%ALLINDEX

This optional keyword specifies that all indexes that provide any benefit are used to query the first table in the join order. This keyword should only be used if multiple indexes are defined. The default setting of the optimizer is to use only those indexes that the optimizer considers most beneficial. By default, this includes all valid equality indexes and other types of selected indexes.%ALLINDEXUse all possible useful indexes of all types. Testing all indexes is expensive, but in some cases it may provide better performance than the default optimization. This option is particularly useful when using multiple range conditional indexes and inefficient equality conditional indexes. In these cases, the query optimizer may not be able to obtain accurate index selectivity.%ALLINDEXCan be with%IGNOREINDEXUsed together to include / exclude specific indexes. Usually,%ALLINDEXShould not be associated withTOPClause is used with a query.

Can%STARTTABLEAnd%ALLINDEXUsed together to specify%ALLINDEXTo which table.

have access to%NOINDEXThe condition level prompt is for specific conditions%ALLINDEXSpecifies the exception.
%NOINDEXThe prompt is placed in front of each query selection criteria that should not use an index.
For example,WHERE %NOINDEX hiredate < ?
This is most commonly used when the vast majority of data are not excluded.
For less than(<)Or greater than(>)Condition, it is usually beneficial to use% Noindex condition level prompt.
For equality conditions, use%NOINDEXConditional cues have no benefit.
For connection conditions,ONClause join support%NOINDEX

%FIRSTTABLE

%FIRSTTABLE tablename

This optional keyword specifies that the query optimizer should start performing joins with the specified table name.tablenameName the table specified later in the join sequence. The join order of the remaining tables is left to the query optimizer. This prompt is functionally similar to%STARTTABLESame, but gives you the flexibility to specify the join table sequence in any order.

tablenameMust be a simple identifier, either a table alias or an unqualified table name. Qualified table names cannot be used(schema.table)。 If the query specifies a table alias, the table alias must be used as the table name. For example:

FROM %FIRSTTABLE P Sample.Employee AS E JOIN Sample.Person AS P ON E.Name = P.Name

%FIRSTTABLEand%STARTTABLEBoth allow you to specify the initial table for the join operation.%INORDERAllows you to specify the order of all tables used for join operations. These three keywords are mutually exclusive; Specify only one and one. If these keywords are not used, the query optimizer will join the tables in the order it deems best, regardless of the order in which they are listed.

out of commission%FIRSTTABLEor%STARTTABLEStart the join sequence from the right side of the left outer join (or the left side of the right outer join). Trying to do this can lead toSQLCODE-34Error: ‘the optimizer cannot find an available join order’.

%FULL

This optional keyword specifies that the compiler optimizer checks all optional join sequences to maximize access performance. For example, when creating stored procedures, the increased compilation time may be worth providing more optimized access. The default optimization is whenFROMClause, do not check for unlikely join sequences.%FULLThis default behavior will be overridden.

WhenFROMClause can also be specified when it contains a table accessed using arrow syntax%INORDERand%FULLKeyword, the order of these tables is not constrained.

%IGNOREINDEX

This optional keyword specifies that the query optimizer ignores the specified index or index list. (for backward compatibility, deprecated synonyms are supported%IGNOREINDICES。)

Specify one or more index names after this keyword. Multiple index names must be separated by commas. You can specify an index name in one of the following formats:

%IGNOREINDEX [[schemaname.]tablename.]indexname [,...] %IGNOREINDEX [[schemaname.]tablename.]* [,...]

The scheme name and table name are optional. If omitted, the current default schema is used and specified asfrom table-refThe name of the table. Asterisk(*)Wildcards specify all index names for the specified table. Index names can be specified in any order. SQL does not validate the specified index names (or their schema names and table names); Nonexistent or duplicate index names will be ignored.

By using this optimization constraint, you can prevent the query optimizer from using indexes that are not optimal for a particular query. By specifying all index names except one, you can actually force the query optimizer to use the remaining indexes.

You can also add%noindexKeyword to ignore a specific index of a specific conditional expression.

%INORDER

This optional keyword specifies that the query optimizer is based on the tableFROMExecutes the join in the order listed in the clause. This minimizes compilation time. Flattening of subqueries and index usage are not affected.

%INORDERCannot be used with cross joins or right outer joins. If the specified table order is inconsistent with the requirements of the outer join, aSQLCODE-34Error: “optimizer cannot find an available join order.” To avoid this, it is recommended to use with external joins%INORDERUse only with ANSI style left outer joins or full outer joins when.

Views and table subqueries are displayed inFROMClause.

  • Streaming view:%INORDERIt does not affect the processing order of tables in the view.
  • Merge Views:%INORDERCauses the reference point of the view to be in accordance with the reference point of the viewFROMClause to process the view table sequentially.

Associate this keyword with%FIRSTTABLEand%STARTTABLEFor comparison, both keywords only specify the initial connection table, not the complete connection order.

Cannot be used at the same time%INORDERand%PARALLELOptimization; If both optimizations are specified, they are ignored%PARALLEL

%NOFLATTEN

This optional keyword is used in quantized subqueries (subqueries that return Boolean values)FROMClause. It specifies that the compiler optimizer should suppress subquery flattening. This optimization option disables flattening (the default), which optimizes queries that contain quantified subqueries by effectively integrating subqueries into the query: adding the table of subqueries to the table of the queryFROMClause and convert the conditions in the subquery into the conditions of the queryWHEREClause.

Here’s how to use%NOFLATTENExample of quantized subquery:

SELECT Name,Home_Zip FROM Sample.Person WHERE Home_Zip IN 
      (SELECT Office_Zip FROM %NOFLATTEN Sample.Employee)
SELECT Name,(SELECT Name FROM Sample.Company WHERE EXISTS
             (SELECT * FROM %NOFLATTEN Sample.Company WHERE Revenue > 500000000))
 FROM Sample.Person

%INORDERand%STARTTABLEOptimize implicit assignment%NOFLATTEN

%NOMERGE

This optional keyword is in the of the subqueryFROMClause. It specifies that the compiler optimizer should prohibit the conversion of subqueries to views. This optimization option adds subqueries to the query by adding them as inline viewsFROMClause to disable the optimization of queries containing subqueries; The comparison of subqueries with query fields will be moved to the query as a joinWHEREClause.

%NOREDUCE

This optional keyword is used in the flow subqueryFROMClause – a subquery that returns the result set of rows, that is, the result set of a closed queryFROMClause. It specifies that the compiler optimizer should prohibit the merging of subqueries (or views) into included queries.

In the following example, the query optimizer typically executes a subquerySample.PersonCartesian product join to “reduce” the query.%NOREDUCEOptimization options prevent this. Iris changed toGNAMEBuild a temporary index on and perform a join on this temporary index:

SELECT * FROM Sample.Person AS p, 
   (SELECT Name||'goo' AS gname FROM %NOREDUCE Sample.Employee) AS e
    WHERE p.name||'goo' = e.gname

%NOSVSO

This optional keyword is used in quantized subqueries (subqueries that return Boolean values)FROMClause. It specifies that the compiler optimizer should prohibit set-valued subquery optimization(SVSO)。

In most cases, set-valued subquery optimization can improve[NOT] EXISTSand[NOT] InThe performance of subqueries, especially for subqueries with only one separable Association condition.
It does this by populating the temporary index with data values that meet the conditions.
Iris does not repeatedly execute subqueries, but looks up these values in the temporary index.
For example,SVSOOptimizedNOT EXISTS (SELECT P.num FROM Products P WHERE S.num=P.num AND P.color='Pink'), create a temporary index.

SVSOOptimizedALLorANYKeywords and relative operators(>>=<, or<=)And subqueries of subqueries, such as… WHERE S.num > ALL (SELECT P.num…)
It passes the subquery expressionsqbExpr(in this caseP.num)Replace withMIN(sqbExpr)orMAX(sqbExpr)To achieve this.
WhensqbExprWhen there is an index on, it supports fast calculation.

%INORDERand%STARTTABLEOptimization does not prohibit set-valued subquery optimization.

%NOTOPOPT

When using withORDER BYClauseTOPClause to specify this optional keyword.
By default,TOPandORDER ByThe fastest time to optimize to the first line.
Instead, specify%NOTOPOPT(no)TOPOptimization) will optimize the query to retrieve the complete result set as quickly as possible.

%NOUNIONOROPT

This optional keyword is used in the query or subqueryFROMClause.
It is disabled for multipleORConditions and targetsUNIONAutomatic optimization provided by subqueries of query expressions.
These automatic optimizations will be multipleORCondition conversion toUNIONSubquery, orUNIONConvert subquery toORConditions.
theseUNION/ORConversion allowedEXISTSAnd other low-level predicates are migrated to top-level conditions so that they can be used by the iris query optimizer index.
These default transformations are desirable in most cases.

However, in some cases, theseUNION/ORConversion will bring a great overhead burden.
%NOUNIONOROPTYes, with thisFROMClause associatedWHEREAll conditions in the clause disable these automaticUNION/ORtransformation.
Therefore, in a complex query, you can disable these automatic union / or optimizations for one subquery and allow them in other subqueries.

UNION %PARALLELKeyword disable automaticUNION-to- orOptimization.

%INORDERand%STARTTABLEOptimization suppressesOR-to-UNIONOptimization.
%INORDERand%STARTTABLEOptimization without inhibitionUNION-to-OROptimization.

Recommended Today

Tomcat 9 safety reinforcement

Apache Tomcat 9 (9.0.53) – Security Considerations NUM Reinforcement content explain remarks 1 Patch installation Use the latest version of Tomcat 2 directory right Modify Tomcat directory permission to run user 3 Running authority The user running Tomcat cannot be root 4 Delete files and directories unrelated to the project Delete the directory under webapps […]