DML statement writing standards

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

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

• Do not use the ignore index, straight_join, or SQL_no_cache option in a production environment.

• The data types of the parameters passed to an SQL statement in a program should 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, you are advised to use the partitioning key.

• 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 effects of a statement on data are not permanently effective until the transaction is committed. A single DML statement can be a transaction.

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

  • 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 records in the table will be deleted. If the number of records 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 the records in batches, or use the TRUNCATE TABLE statement.

• In scenarios where business cache tables are frequently inserted or deleted and the data has a short lifecycle, you must specify a HINT to query the data.

  OceanBase Database has optimized row purge for this scenario. It can recycle delete nodes and improve query performance. To prevent performance degradation due to scenarios where row purge cannot recycle data, we recommend that you specify a HINT for queries.

• 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 use an index in the WHERE clause for efficient retrieval.

• The TRUNCATE TABLE statement is faster than the DELETE statement 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 this statement in development code with caution and ensure that it is correct.

  Note

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