DML statements

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

• You must specify the name of the column to be inserted in an INSERT statement. Do not write the statement in the INSERT VALUES(...) format.

• Do not use the FORCE INDEX, IGNORE INDEX, STRAIGHT_JOIN, or SQL_NO_CACHE syntax in your production environment.

• To prevent implicit data type conversion, we recommend that you keep the type of the parameter value passed into the application by the SQL statement consistent with the type of the field in the database.

• We recommend that you use the partitioning keys in the WHERE clause of a DML statement for a partitioned table.

• The partition or subpartition name must be enclosed in parentheses in the statement. Sample code:

  obclient> SELECT * FROM tbl1_l2 PARTITION(p2,p3);
  +------+------+
  | col1 | col2 |
  +------+------+
  |    8 | 8    |
  |    9 | 9    |
  +------+------+
  2 rows in set
  

• A change made by a DML statement is persisted only when the transaction is committed. A DML statement can also be a transaction.

• When you execute an UPDATE statement that involves hundreds of thousands of records without using the WHERE clause, a large transaction is generated. If the transaction is too large, the update may fail.

  • Control the rows to be changed in an UPDATE operation to limit the transaction to a proper size.

  • A WHERE clause is optional in the DELETE statement. If no WHERE clause is specified, all records in the table are deleted. If the statement involves hundreds of thousands of records, a large transaction is generated, which may reduce the performance. We recommend that you specify the WHERE clause to delete data in batches or use the TRUNCATE TABLE statement.

• If a cache table is subject to frequent DML operations, such as INSERT or DELETE, and the data lifecycle is short, you must specify hints for queries.

  OceanBase Database allows you to perform Row Purge to recycle the resources of deleted nodes, which improves the query performance. Considering the diverse business scenarios, however, we recommend that you specify hints for queries to prevent performance degradation when the resources cannot be recycled by Row Purge.

• To optimize the performance of the DELETE or UPDATE statement, we recommend that you do not perform the operation on more than 100 records, and use indexes in the WHERE clause.

• In comparison with a DELETE statement, a TRUNCATE TABLE statement is faster and uses fewer system and transaction log resources. However, if no transaction is used in a TRUNCATE operation, it cannot be rolled back. Therefore, we recommend that you proceed with caution when you use a TRUNCATE TABLE statement.