Lindorm:SELECT

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 greatly improve the query performance of some LindormTable instances.

SELECT /*+_l_enable_parallel_*/ * FROM dt WHERE (id > 0 AND id < 5) OR (id > 6 AND id < 15);

EXPLAIN SELECT /*+_l_enable_parallel_*/ * FROM dt WHERE (id > 0 AND id < 5) OR (id > 6 AND id < 15);

FAQ