Other specifications

This topic describes other specifications of OceanBase Database.

Specifications for derivatives

After data is exported by using DataX, it must be split into multiple small files and imported one by one. Each small file must be less than 1 GB in size.

Other specifications

• Do not use force index, ignore index, straight_join, or SQL_no_cache in a production environment.

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

  Note

  Complex logic should be handled in the application, not in SQL statements.

• Do not perform left or full fuzzy searches on pages.

  Note

  When an SQL statement contains a LIKE query, the left prefix matching must be used.

• Partitioned tables must contain partitioning keys. Scanning all partitions without partitioning keys is not allowed.

• Joining three or more tables is not recommended.

• In a query that joins multiple tables, the driving table must be the one that returns a smaller result set.

• Nested subqueries are not recommended. Instead, rewrite the query to use table joins.

• Do not join multiple tables in INSERT, UPDATE, DELETE, or REPLACE statements.

• Use covering indexes to avoid table lookups.

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

  Note

  Stored procedures are not recommended because they are difficult to troubleshoot and not easily portable. From a performance perspective, stored procedures typically contain complex SQL statements with multiple tables or conditions, which are resource-intensive and time-consuming to execute. This can degrade system concurrency. Additionally, arithmetic operations within stored procedures can lead to severe performance issues.

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

• Minimize the use of aggregate functions.

• Avoid using IN operations. If you must use IN, rewrite the subquery to use JOIN. If you must use IN, the number of constants in the IN list must be less than 100.

• Use WHERE or LIMIT in the statement. Otherwise, the entire table will be scanned.

• Control the size of intermediate result sets.

  Note

  For queries that contain the distinct, order by, or group by clause, the intermediate result set must be less than 10,000 rows. For large result sets that exceed 10,000 rows, sorting and grouping must be performed in the application.

• The number of partitions in a HASH partitioned table is recommended to be a power of 2. Otherwise, the data distribution will be uneven.

• Full-text search is not recommended.

• The EVENT feature is not recommended.

• Do not use or operate the OceanBase database or test database in the program. Do not 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 statistical or aggregation operations on the database. Instead, export the data and perform the operations by using other tools or in the offline backup database.

• Avoid using HAVING.

  The HAVING clause filters data after the GROUP BY clause is processed, while the WHERE clause filters data during processing. Therefore, the WHERE clause can significantly reduce sorting and I/O time.