This topic describes other relevant specifications of OceanBase AI Database.
Guidelines for derivatives
After exporting with DataX, the data needs to be split into multiple small files, and then each file should be imported into the database individually. Keep each small file within 1 GB.
Others
The use of
force index,ignore index,straight_join, andSQL_no_cacheis prohibited in production environments.Compound statements such as
BEGIN...END,LOOP...END LOOP,REPEAT...UNTIL...END REPEAT, andWHILE...DO...END WHILEare prohibited.Note
Complex logic should be processed within the application to avoid writing complex SQL.
Page search is strictly prohibited from being left-fuzzy or full-fuzzy.
Note
When using LIKE queries in SQL, always use the leftmost prefix match.
A partitioned table must have a partitioning key. Scanning all tablets without a partitioning key is prohibited.
Joining more than three tables is not recommended.
In queries with multiple table joins, the driver table should be selected from those with a smaller result set.
It is not recommended to write SQL statements with multiple levels of nested subqueries. Instead, it is recommended to rewrite them in the form of sequential table joins.
Multi-table join operations are prohibited in
INSERT,UPDATE,DELETE, andREPLACEstatements.Use covered indexes for query operations to avoid table access.
Reduce the use of custom functions, custom types, and stored procedures.
Note
Stored procedures are not conducive to troubleshooting, have poor portability, and from a performance perspective, their SQL typically involves multiple tables or complex judgments. Such statements consume significant resources and take a long time to execute, leading to degraded system concurrency. Furthermore, arithmetic operations within stored procedures can also cause severe performance issues.
Reduce the use of non-logical condition queries such as
!= <> not.Reduce the use of aggregate functions.
We recommend that you avoid
INoperations. Subqueries withINshould be rewritten asJOINstatements whenever possible. IfINoperations are unavoidable, the number of constants in theIN listshould be less than 100.We recommend that you use the
WHEREorLIMITclause in your statement. Otherwise, the entire table will be scanned.Controls the size of intermediate result sets.
Note
For queries with
distinct,order by, andgroup byclauses, the intermediate result set is limited to within 10,000 rows. Sorting and grouping for large result sets exceeding 10,000 rows must be implemented on the application side.It is recommended that the number of
hashpartitions be a power of 2. Otherwise, data distribution among partitions may become uneven.We recommend that you do not use the full-text search feature.
We recommend that you do not use the
EVENTfeature.Using or operating the OceanBase database and test database is prohibited in the program. Creating a database named test or starting with test is also prohibited.
Using user-defined variables is prohibited in OceanBase AI Database.
Avoid performing real-time statistical or summarization calculations on data in the database. These operations can be completed after data export using other tools or in an offline backup database.
Avoid using
HAVING.The
HAVINGclause filters data after theGROUP BYoperation, whereas theWHEREclause filters data during the processing. Therefore, theWHEREclause can eliminate a significant amount of sorting and I/O time.