Other related specifications

This topic describes other specifications in OceanBase Database.

Derivative Specification

You must split the DataX exported files into smaller files of 1 GB each, and import each file into the database separately.

Other

• Use the FORCE INDEX, IGNORE INDEX, STRAIGHT_JOIN, and SQL_NO_CACHE hints and options in a production environment.

• Compound statements such as BEGIN...END, LOOP...END LOOP, REPEAT...UNTIL...END REPEAT, and WHILE...DO...END WHILE are not supported.

  Note

  Keep business logic in the application and avoid complex SQL statements.

• Page searches cannot use left or full fuzzy searching.

  Note

  For SQL queries that include LIKE clauses, you must use left prefix matching.

• You cannot scan all partitions of a partitioned table without specifying the partitioning key.

• Joining more than three tables is not recommended.

• In a multiple-table join query, the driver table must be the table with the smaller result set.

• It is not recommended to use multiple layers of subqueries. Consider rewriting the queries as table joins.

• Multi-table join operations are not allowed in the INSERT、UPDATE、DELETE、REPLACE statements.

• To avoid backtracking, queries can be performed by using a covering index.

• Reduce the use of custom functions, user-defined types, and stored procedures.

  Note

  StoredProcedure procedures are not conducive to troubleshooting, poor portability, and from a performance standpoint, stored procedure SQL statements are typically involve multiple tables.

  Statements such as multiple comparisons and calculations in stored procedures themselves are high-performance consuming. Therefore, they have a long execution time, which reduces the concurrent performance of the system. In addition, if stored procedures contain arithmetic operations, serious performance issues will occur.

• Avoid using the !=, <>, or NOT operators in non-logical conditions.

• Use fewer aggregation functions.

• We recommend that you avoid the IN clause. If you must use the IN clause, make sure the number of constants in the IN list is less than 100. We recommend that you rewrite subqueries containing the IN clause into JOIN statements.

• We recommend that you specify the WHERE or LIMIT clause. Otherwise, the entire table will be scanned.

• Control the size of intermediate result sets.

  Note

  A query that contains the distinct, order by, and group by clauses, and has a result set with more than 10,000 rows may be limited in a way that cannot fully sort or group the result set. Therefore, you must implement sorting and grouping in your application for a large result set that exceeds 10,000 rows.

• The number of partitions for hash partitioning should be a power of 2 to ensure that the data is evenly distributed across partitions.

• Avoid using the full-text search feature.

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

• OceanBase and test databases cannot be used, modified, or created in the procedure.

• Do not use custom user variables in OceanBase Database.

Do not perform real-time statistical or summary computations on business data in the database. You can run statistics and perform data aggregation in offline backups or using other tools after exporting data.

• Avoid using the HAVING clause.

  The HAVING clause filters data after the GROUP BY operation, while the WHERE clause filters data during the processing of the query. Therefore, the WHERE clause can eliminate a lot of sorting and I/O time.