SELECT statements

This topic describes the requirements and suggestions on writing SELECT statements in OceanBase Database.

• We recommend that you specify the field name in a SELECT statement. An asterisk (*) that indicates the field name can lead to the following issues:

  • The parsing cost of the query analyzer is increased.

  • Field addition or deletion can cause inconsistencies with the resultMap configuration.

• We recommend that you specify only necessary columns in the SELECT clause of an SQL statement. If you specify unnecessary columns, the database accesses tables by the index primary keys to retrieve those unnecessary columns. This leads to more consumption of I/O resources.

• When an SQL statement uses a partitioned table, the predicate must contain the partitioning key.

• We recommend that you do not use count(column name) or count(constant) to replace count(*). As a syntax defined in the SQL92 standard, count(*) counts the number of rows in the specified table of a database, regardless of whether the row value is NULL or not.

  Note

  count(*) counts the rows with a NULL value, while count(column name) does not count a row if it contains a NULL value.

• We recommend that you use UNION ALL instead of UNION for the SELECT statements, but do not use more than five UNION clauses.

  Note

  A UNION clause automatically sorts the results to eliminate duplication while duplication is unlikely between different queries.

• We recommend that you use a JOIN operator instead of an IN operator in a subquery. When an IN operator is inevitable, do not specify more than 100 collection elements after the IN operator. Note that you can use no more than 8,192 conditions in one IN operator in a query. Otherwise, the size overflow error is returned.

• We recommend that you do not add a read lock to the SELECT ... for update (with cs) statement. Otherwise, it can cause a lock wait and impact the business in the case of highly concurrent large transactions.

• Do not perform equivalence queries on columns of different types.

  Note

  If the value of a field is of the VARCHAR type while that specified in the WHERE clause is of the INT type, the query can trigger implicit conversion and crash the database because the index cannot be used.

• The fields specified in the ORDER BY clause must be unique or unique in combination. This is because OceanBase Database and MySQL behave differently in sorting non-unique fields.

• We recommend that you do not include complex SQL queries in crucial business processes, such as computing, multi-table join, and table traversal by using the CASE WHEN clause. We recommend that you split complex queries into single-table queries.

• If the filter condition of an SQL statement shows poor performance, you can bind it to a proper execution plan as an emergency solution. For long-term benefits, we recommend that you modify the logic of the SQL statement. An excessive number of hints may affect the subsequent upgrade and maintenance.

• If a query involves the DISTINCT, ORDER BY, or GROUP BY clause, limit the number of rows in the intermediate result set to no more than 10,000. The sorting and grouping of large result sets with more than 10,000 rows can be performed by applications.

• When you set the query concurrency, the query shows the best performance when the number of concurrent query tasks for a table equals the number of table shards.

• We recommend that you do not perform arithmetic operations or function-based calculations for table columns in the WHERE condition.

• Do not assign values to variables in an SQL statement, especially during distributed processing.

• To avoid implicit type conversions, make sure that condition fields in an SQL statement are of the same data type.

• It is prohibited to use reserved keywords of OceanBase Database in a projected field in a SELECT statement.