SELECT - Lindorm - Alibaba Cloud Documentation Center

This topic describes the syntax used to query data in a spatio-temporal table.

Syntax

SELECT [hint_clause] ( select_clause | '*' )
    FROM table_name
    [force index(index_name)]
    [ WHERE where_clause ]
    [ ORDER BY ordering_clause ]
    [ LIMIT integer ] | [LIMIT integer, integer]
select_clause    ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector         ::=  column_name
                      | term
                      | function_name '(' [ selector ( ',' selector )* ] ')'
                      | COUNT '(' '*' ')'
where_clause     ::=  relation ( AND|OR relation )*
                      | [st_contains] '(' [ selector ( ',' selector )* ] ')'    
relation         ::=  column_name operator term
                      '(' column_name ( ',' column_name )* ')' operator tuple_literal
operator         ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | IS NOT? NULL
ordering_clause  ::=  column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*

hint_clause::=/*+hint_items*/
hint_items::=hint_item(','hint_item)*
hint_item::=identifier('('hint_option(','hint_option)*')')
hint_option::=expression

Parameters

Take note of the following items when you configure parameters in SELECT statements:

If you want to force the system to select a specific index when the SELECT statement is executed, add force index(index_name) to the end of the statement.
If LIMIT is followed by only one number, this number specifies the number of rows to be returned. If LIMIT is followed by two numbers that are separated by a comma (,), the first number is the offset value that specifies the number of rows to be skipped in the query, and the second number specifies the number of rows to be returned for the query.
If you want to force the system to perform an inefficient query, add the /*+ _l_allow_filtering_ */ hint to the end of the SELECT statement. If LindormTable determines that a SELECT query is an inefficient query, LindormTable does not process the query by default and throws an exception to ensure stable performance. For example, a table named dt is stored in your Lindorm database. When you execute the SELECT * FROM dt WHERE nonPK=100; statement, the following error message is returned. For more information about the solution to an inefficient query, see FAQ.
```
DoNotRetryIOException: Detect inefficient query: SELECT * FROM dt WHERE nonPK=100 supportEmptyResult true. This query may be a full table scan and thus may have unpredictable performance.
```
For more information about hints, see Overview.

Examples

Use spatio-temporal functions in SELECT statements to perform simple queries. For more information about spatio-temporal functions, see Overview.
```
SELECT id, ST_AsText(g) FROM gps_data;
SELECT id, ST_Buffer(g, 1.0) AS buffer FROM gps_data;
```
Use spatio-temporal functions in SELECT statements to perform simple queries. Currently, statements that include the following three functions as spatial conditions can be efficiently executed: ST_Contains, ST_DWithin, and ST_DWithinSphere.
- Use the ST_Contains function in the statement to query data that corresponds to the points within the specified spatial range.
```
SELECT id FROM gps_data WHERE ST_Contains(ST_GeomFromText('POLYGON((...))'), g);
```
- Use the ST_DWithin function in the statement to query data that corresponds to the points that are within the specified plane distance from the central point. The distance specified in the function is measured in degrees.
```
SELECT id FROM gps_data WHERE ST_DWithin(ST_GeomFromText('POINT(0 0)'), g, 100);
```
- Use the ST_DWithinSphere function in the statement to query data that corresponds to the points that are within the specified spherical distance from the central point. The distance specified in the function is measured in meters.
```
SELECT id FROM gps_data WHERE ST_DWithinSphere(ST_GeomFromText('POINT(0 0)'), g, 100);
```
Add the /*+ _l_allow_filtering_ */ hint to the end of inefficient query statements to skip the query check and execute the statements by force.
```
SELECT /*+ _l_allow_filtering_ */ * FROM gps_data WHERE ST_Within(g,ST_GeomFromText('POLYGON((...))'));
```
Note
If the query conditions specified in a SELECT statement meet all the following conditions, the SELECT query is considered inefficient:
- The value range of the first primary key column in your table is not specified in the WHERE clause.
- The value range of the first index key column in your index table is not specified in the WHERE clause.
- One or more conditions that are specified in the WHERE clause are not related to the first primary key column or the first index key column.

Enable parallel queries

By enabling parallel queries, you can increase the performance of spatio-temporal queries by more than twice. By default, parallel queries are disabled. To enable parallel queries, submit a ticket. After you enable parallel queries, you must use hints in SQL statements to configure the _l_enable_parallel_ parameter to specify the number of allowed parallel queries for each SQL statement. If you do not specify the number of allowed parallel queries for a SQL statement, parallel queries cannot be performed by this statement. For more information, see Overview.

Limits

If you use an index to query the primary table, parallel queries cannot be used. To avoid querying the primary table, we recommend that you create redundant columns for the columns that are required in queries when you create a index.

Examples

Use the default number of allowed parallel queries
Note
By default, parallel queries are disabled. To enable parallel queries, submit a ticket.
If you do not specify the number of allowed parallel queries, the system determines an optimal number based on the query complexity.
```
SELECT /*+_l_enable_parallel_*/ id FROM gps_data WHERE ST_Contains(ST_GeomFromText('POLYGON((...))'), g);
```
Customize the number of allowed parallel queries
In the following example, the number of allowed parallel queries is set to 8.
```
SELECT /*+_l_enable_parallel_(8)*/ id FROM gps_data WHERE ST_Contains(ST_GeomFromText('POLYGON((...))'), g);
```
Note
- The custom number of allowed parallel queries must be an integer that is equal to or larger than 1. If the custom number of allowed parallel queries is larger than the default number of allowed parallel queries, the default number of allowed parallel queries prevails.
- If you do not know how to specify an appropriate number of allowed parallel queries, we recommend that you do not specify the number. In this case, the system determines an optimal number.

FAQ

What is an inefficient query?
If the query conditions specified in a SELECT statement meet all the following conditions, the SELECT query is considered inefficient.
- The value range of the first primary key column in your table is not specified in the WHERE clause.
- The value range of the first index key column in your index table is not specified in the WHERE clause.
- One or more conditions that are specified in the WHERE clause are not related to the first primary key column or the first index key column.
What can I do if my query is identified as an inefficient query?
You can use one of the following methods to solve this issue:
- Add the /*+ _l_allow_filtering_ */ hint to the end of inefficient query statements to skip the query check and execute the statements by force.
```
SELECT /*+ _l_allow_filtering_ */ * FROM dt WHERE nonPK=100;
```
- Add one or more conditions associated with the primary key of your table to the WHERE clause.
- Change the primary key of your table to prevent inefficient queries. For more information, see Design primary keys for Lindorm wide tables.
- Use high-performance native secondary indexes. For more information, see Secondary index.