This topic describes the writing specifications for database DML statements in OceanBase AI Database.
For the INSERT statement, you must specify the column names. Do not use the
INSERT VALUES(......)syntax. Instead, specify the column_name to be inserted.The use of
ignore index,straight_join, andSQL_no_cacheis prohibited in production environments.The parameter types passed to SQL statements in your application should preferably match the corresponding column types in the database. Otherwise, implicit type conversion will occur.
It is recommended to use the partitioning key in the WHERE clause of DML statements for partitioned tables.
Statements must enclose partition or subpartition names in parentheses. Example:
obclient> SELECT * FROM tbl1_l2 PARTITION(p2,p3); +------+------+ | col1 | col2 | +------+------+ | 8 | 8 | | 9 | 9 | +------+------+ 2 rows in setThe modifications made by a statement to data only take effect permanently when the transaction is committed. A single DML statement can also be a transaction.
When performing an unconditional update, if the number of records reaches hundreds of thousands or millions, a large transaction may be generated, which could cause the operation to fail.
When using UPDATE, be careful to control the number of rows modified to prevent the transaction from becoming too large.
The WHERE clause of a DELETE statement is optional. If no WHERE clause is provided, the entire table is deleted. If the number of records reaches hundreds of thousands or millions, this can result in a large transaction and potential performance issues. It is recommended to use a WHERE clause for batch deletion or to use the TRUNCATE TABLE statement instead.
In business cache table scenarios, such as those with frequent inserts or deletes and a short data lifecycle, you need to add hints to specify the query.
OceanBase AI Database optimizes for this scenario with row purge, which allows the reclamation of deleted nodes and improves query performance. Considering the diversity of business scenarios, to prevent performance degradation due to situations where row purge cannot reclaim data, it is recommended that businesses specify HINT queries.
Delete or update SQL statements need optimization. The core rule is to keep the granularity as small as possible (no more than 100 records), and it is recommended to have indexes on the WHERE conditions for efficient retrieval.
TRUNCATE TABLE is faster than DELETE and consumes fewer system and transaction log resources. However, since TRUNCATE TABLE does not involve transactions, it may cause data loss. Therefore, when using this statement in your code, exercise caution and ensure that the operation is performed correctly.
Note
TRUNCATE TABLE is functionally equivalent to a DELETE statement without a WHERE clause.