Others

This topic describes other requirements and suggestions for you to use OceanBase Database.

Import and export data

After you export data by using DataX, you must split the data into multiple small files and import them into the database one by one. The size of each small file must be less than 1 GB.

Others

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

• Do not use compound statements such as BEGIN...END, LOOP...END LOOP, REPEAT...UNTIL...END REPEAT, and WHILE...DO...END WHILE.

  Note

  Complex logic must be processed in the applications to avoid writing complex SQL statements.

• Do not use the LIKE '%...%' or LIKE '%...' clause in paging queries.

  Note

  If you must use LIKE clauses in an SQL statement, follow the leftmost prefix matching principle.

• You must specify partitioning keys for a partitioned table. Do not scan all partitioned tables without specifying the partition keys.

• We recommend that you do not join more than three tables.

• In a query that involves multi-table join, we recommend that you select the table with the smallest result set as the driving table.

• Do not write SQL statements with nested subqueries. We recommend that you rewrite the SQL statements that sequentially join tables.

• Do not perform the multi-table join operation in the INSERT, UPDATE, DELETE, or REPLACE statement.

• We recommend that you use the covering index in table queries to avoid table access by index primary key.

• We recommend that you do not use functions, data types, or stored procedures defined by yourself if possible.

  Note

  Stored procedures make error location difficult and have poor portability. In addition, the SQL statements of a stored procedure generally involve multiple tables or have many judgment conditions. Such statements are resource consuming and take a long time to execute, leading to lower system concurrency. If a stored procedure involves arithmetic operations, it also causes serious performance issues.

• Reduce the use of non-logical condition != <> not in queries.

• Reduce the use of aggregate functions.

• We recommend that you avoid the IN operator, and use a JOIN operator instead of an IN operator in a subquery. When an IN operator is inevitable, do not specify more than 100 constants in the IN list.

• We recommend that you use the WHERE or LIMIT clauses in statements. Otherwise, full table scans are performed.

• Control the size of intermediate result sets.

  Note

  If a query involves the DISTINCT, ORDER BY, or GROUP BY clause, limit the number of rows of the intermediate result set to no more than 10,000. The sorting and grouping of large result sets with more than 10,000 rows must be performed by applications.

• We recommend that you set the number of Hash partitions to the power of 2. Otherwise, the data distribution may be uneven among partitions.

• We recommend that you do not use full-text retrieval.

• We recommend that you do not use the EVENT feature.

• Do not execute SQL statements that operate an OceanBase database or test database in programs. Do not create a database whose name is test or starts with test.

• Do not use variables defined by yourself in OceanBase Database.

• Do not perform real-time statistics collection or aggregation in the database. You can export the data and perform such operations by using other tools or in an offline backup database.

• Avoid using the HAVING clause.

  A HAVING clause filters the data after data processing by the GROUP BY clause. However, you can use a WHERE clause to filter the data during the processing. Therefore, the WHERE clause can save a lot of time in sorting and I/O operations.