Related specifications

This topic describes the specifications of OceanBase Database.

Data export specifications

After data is exported by using DataX, split the data into multiple small files and import the small files one by one. The size of each small file should be less than 1 GB.

Other specifications

• The FORCE INDEX, IGNORE INDEX, STRAIGHT_JOIN, and SQL_NO_CACHE hints are not supported 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

  Complex logic should be handled in applications to avoid writing complex SQL statements.

• Left-side wildcard searches and full-wildcard searches are not supported in page searches.

  Note

  When a LIKE query is used in an SQL statement, the leftmost prefix must match.

• A partitioned table must have a partitioning key. Do not scan all subtables without using a partitioning key.

• We recommend that you join no more than two tables.

• In a multi-table join query, select the table with the smallest result set as the driving table.

• We do not recommend that you rewrite SQL statements with nested subqueries into sequential table join statements.

• Do not perform multi-table joins in the INSERT, UPDATE, DELETE, or REPLACE statements.

• Use covering indexes to avoid table relookups.

• Reduce the use of custom functions, custom data types, and stored procedures.

  Note

  Stored procedures are not conducive to locating problems and have poor portability. Additionally, from a performance perspective, the SQL in stored procedures generally involves multiple tables.

  or judgments, and other statements that consume a large amount of performance and take a long time to execute, which will lead to a decline in system concurrency. Arithmetic operations in stored procedures can also cause serious performance problems.

• Reduce the use of non-logical conditions such as !=, <>, and NOT.

• Reduce the use of aggregate functions.

• Avoid the IN operation. If you use a subquery with the IN keyword, rewrite it into a JOIN statement. If you must use it, the IN list must not contain more than 100 constants.

• Use WHERE or LIMIT in statements. Otherwise, full-table scans will occur.

• Control the size of intermediate result sets.

  Note

  For queries with the distinct, order by, or group by clause, the size of the intermediate result set must not exceed 10,000 rows. Otherwise, sorting and grouping of result sets with more than 10,000 rows must be implemented in applications.

• The number of hash partitions should be a power of 2. Otherwise, data distribution will be uneven among the partitions.

• Avoid full-text search.

• Avoid the use of the EVENT feature.

• Do not use or operate on the OceanBase or test databases in your programs. You cannot create a test database or a database whose name starts with test.

• Do not use user-defined variables in OceanBase Database.

• Do not perform real-time business statistics or summarization in the database. You can export the data and then perform the operations in another tool or in the offline backup database.

• Avoid the use of the HAVING clause.

  The HAVING clause is used to filter data after the GROUP BY operation, whereas the WHERE clause is used to filter data before the GROUP BY operation. Therefore, the WHERE clause can eliminate a large amount of sorting and I/O time.