Other specifications

This topic describes other specifications related to OceanBase Database.

DataX specifications

After data is exported by using DataX, split the data into multiple files, and then import the data to the database one by one. Each file must contain no more than 1 GB of data.

Others

• The FORCE INDEX, IGNORE INDEX, STRAIGHT_JOIN, and SQL_NO_CACHE hints are not supported in a production environment.

• Composite 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 matching or full-wildcard matching is not supported in page search.

  Note

  If a LIKE query is included in an SQL statement, the leftmost prefix matching mode must be used.

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

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

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

• We 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 statement.

• Use a covering index for queries to avoid table access.

• 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.

  and these statements consume a large amount of resources and take a long time to execute, which will degrade the system concurrency. Arithmetic operations in stored procedures can also cause serious performance issues.

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

• Reduce the use of aggregate functions.

• Avoid using the IN operator. If you use a subquery that contains the IN operator, rewrite it into a JOIN statement. If necessary, the subquery can contain no more than 100 constants in the IN list.

• Use WHERE or LIMIT in statements. Otherwise, the database scans the entire table.

• Control the size of intermediate result sets.

  Note

  For queries that contain the distinct, order by, or group by clause, the size of the intermediate result set must not exceed 10,000. If you need to sort or group a result set that contains more than 10,000 rows, implement the sorting or grouping in programs.

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

• Avoid using full-text search.

• Avoid using the EVENT feature.

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

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

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

• Avoid using the HAVING clause.

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