DML statement writing standards

This topic describes the standards for writing DML statements in OceanBase Database.

• You must specify the field names in an INSERT statement. You cannot write an INSERT statement in the INSERT VALUES(......) format. You must specify the column names to be inserted.

• Do not use ignore index, straight_join, and SQL_no_cache in a production environment.

• The data types of parameters passed to an SQL statement in an application must be the same as the data types of the corresponding fields in the database. Otherwise, implicit type conversion occurs.

• In a DML statement for a partitioned table, we recommend that you use the partitioning key in the WHERE clause.

• Add parentheses around the partition or subpartition name. Example:

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

• The changes made by an SQL statement take effect only after the transaction is committed. A single DML statement can be a transaction.

• If you do not specify a WHERE clause in an UPDATE statement and the number of rows to be updated reaches hundreds of thousands or millions, a large transaction may be generated, which may fail.

  • We recommend that you control the number of rows to be modified in an UPDATE statement to prevent the transaction from being too large.

  • The WHERE clause in a DELETE statement is optional. If you do not specify a WHERE clause, all rows in the table are deleted. If the number of rows to be deleted reaches hundreds of thousands or millions, a large transaction may be generated, which may cause performance issues. We recommend that you specify a WHERE clause to delete the rows in batches or use the TRUNCATE TABLE statement.

• In scenarios where data is frequently inserted or deleted and has a short data lifecycle, such as business cache tables, you must specify a query hint.

  OceanBase Database optimizes row purging for such scenarios. This optimization can reclaim deleted nodes and improve query performance. To prevent performance degradation due to scenarios where row purging cannot reclaim data, we recommend that you specify a query hint.

• Optimize the DELETE and UPDATE statements. The core rule is to keep the granularity as small as possible (no more than 100 rows) and to ensure that the WHERE clause has an index for efficient retrieval.

• TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources. However, TRUNCATE TABLE does not support transactions, which may cause data loss. Therefore, we recommend that you use this statement in development code with caution and ensure that it is correct.

  Note

  TRUNCATE TABLE is functionally equivalent to a DELETE statement without a WHERE clause.