This topic describes the writing specifications for SELECT statements in OceanBase AI Database.
It is recommended to specify the field names in the SELECT statement. Writing "*" is prohibited.
Increases the parsing cost for the query analyzer.
The addition and deletion of fields are prone to inconsistency with the resultMap configuration.
We recommend that you remove unnecessary columns from the SELECT statement: In an SQL statement, list only the necessary columns in the SELECT clause. This is because including unnecessary columns will cause the database to perform a table access (reaching the data page to fetch the specific requested column), which results in more I/O operations.
In SQL statements that use partitioned tables, the partitioning key must be included in all predicates.
We recommend that you do not use
count(column_name)orcount(constant)to replacecount(*).count(*)is a standard SQL92 syntax for counting rows, which is database-independent and independent of NULL and non-NULL values.Note
count(*)counts rows where the value is NULL, whereascount(column_name)does not count rows where this column has a NULL value.Avoid using the UNION clause in SELECT statements unless necessary. Instead, use UNION ALL. Also, limit the number of UNION clauses to five.
Note
UNION automatically performs a final sort to eliminate duplicates, but in practice, duplicate rows are usually absent between different queries.
The IN operation is not recommended. Subqueries with IN should be rewritten as JOINs whenever possible. If unavoidable, carefully evaluate the number of elements in the set following IN; it is recommended to keep it within 100.
The read lock
SELECT ... for update (with cs)is not recommended. If a transaction is large, it can easily lead to lock waits under high concurrency, affecting business operations.Using non-equivalent columns in an equality query is prohibited.
Note
If the field type is VARCHAR and the value in the WHERE condition is an INT, implicit conversion occurs, which may lead to an out-of-bounds index access and potentially crash the database.
In an ORDER BY query statement, the fields specified in ORDER BY must be unique or uniquely combinable (if non-unique fields are used for sorting, the behavior of OceanBase AI Database may differ from that of MySQL).
It is not recommended to include complex queries such as computation operations, multi-table joins, or table traversal CASE WHEN statements in the SQL statements of core business processes. It is recommended to split them into simple single-table queries.
For SQL statements with poor query condition filtering, an emergency workaround is to bind the execution plan to the SQL. However, for long-term use, it is recommended to modify the SQL logic. Excessive use of hints may impact subsequent upgrades and maintenance.
For queries with DISTINCT, ORDER BY, and GROUP BY clauses, the intermediate result set is limited to within 10,000 rows. For large result sets (intermediate result sets exceeding 10,000 rows), sorting and grouping are implemented on the application side.
When setting the number of parallel tasks, the optimal value is the number of shards in the table.
We recommend that you do not perform arithmetic operations or function calculations on table fields in the
WHEREcondition.Avoid assigning values to variables in SQL statements, especially when assigning results to variables during distributed processing.
The data types of the condition fields in the SQL statements must be consistent to avoid implicit type conversion.
Reserved database keywords are prohibited for
SELECTprojection fields in SQL statements.