DML statement writing specifications

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

• You must specify the column names in the INSERT statement. Do not use the INSERT VALUES(......) syntax. Specify the column names that you want to insert.

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

• The data types of the parameters that you pass to an SQL statement in an application must be the same as the data types of the corresponding columns in the database. Otherwise, implicit type conversion will occur.

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

• You must enclose the name of a partition or subpartition in parentheses. Example:

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

• The effects of a statement on data are not permanent until 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 will be generated, which may fail.

  • We recommend that you control the number of rows to be modified in an UPDATE statement to ensure that the transaction is not too large.

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

• In scenarios where data is frequently inserted or deleted, such as in a business cache table, you must specify a HINT to query data.

  OceanBase Database has optimized row purge for such scenarios. This optimization can recycle deleted nodes and improve query performance. To prevent performance degradation caused by scenarios where row purge cannot recycle data, we recommend that you specify a HINT to query data.

• You must optimize DELETE and UPDATE statements. The core rule is to minimize the granularity (no more than 100 rows) and 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 accidents. Therefore, we recommend that you use the TRUNCATE TABLE statement in development code with caution and ensure that it is correct.

  Note

  TRUNCATE TABLE has the same functionality as the DELETE statement without a WHERE clause.