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 in batches. The size of each file should not exceed 1 GB.

Others

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

• Complex 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 allowed in search conditions.

  Note

  In SQL queries that use the LIKE statement, use the leftmost prefix matching mode.

• A partitioned table must have a partitioning key. Do not scan all partitions 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 covering indexes to avoid table relookups.

• Use custom functions, types, and stored procedures sparingly.

  Note

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

  or judgments such as the statement, which consumes a large amount of performance and takes a long time to execute. This will cause a decline in system concurrency performance. If there is an arithmetic operation in the stored procedure, it will also cause serious performance problems.

• Use non-logical conditions such as !=, <>, or NOT sparingly.

• Use aggregation functions sparingly.

• Avoid the IN operation. If you must use a subquery that contains the IN keyword, rewrite it into a JOIN statement. If you must use the IN keyword, ensure that the number of constants in the IN list does not exceed 100.

• Use the WHERE clause or the LIMIT clause in a statement. Otherwise, the database scans the entire table.

• Control the size of intermediate result sets.

  Note

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

• The number of hash partitions should be a power of 2. Otherwise, data is unevenly distributed among partitions.

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

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

• Do not use or operate on the OceanBase database or the test database in your programs. You can create only one test database, and its name must start with the test prefix.

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

• Do not perform real-time business statistics or summary calculations in databases. 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 sorts data, whereas the WHERE clause is used to filter data during processing. Therefore, the WHERE clause can eliminate a large amount of sorting and I/O time.