This topic describes the specifications for table scanning in SQL writing of OceanBase AI Database.
In SQL writing for OceanBase AI Database, for SELECT, UPDATE, DELETE, and REPLACE statements, if a WHERE clause is used, the conditions in that clause should be filtered through indexes. You can use the EXPLAIN command to check the execution plan to confirm whether an index query is being used.
Avoid full-table scans on large tables whenever possible. Generally, index scans are preferred over full-table scans for most queries on large tables. However, full-table scans can be used for static tables with fewer than 100 rows. Use
EXPLAIN <sql_statement>to view the execution plan of an SQL statement. The execution plan command has three modes:The
EXPLAIN BASICcommand is used to display the most basic execution plan.The
EXPLAIN EXTENDEDcommand is used for the most detailed plan display (this display mode is typically used when troubleshooting issues).The information displayed by the command helps ordinary users understand how the entire plan is executed.
For large tables in your business, we recommend that you perform full table scans and full table exports (dumps) on a backup database or offline.
In a WHERE clause, you cannot use only a full-text fuzzy search or a left-fuzzy search condition for the LIKE keyword. Other query conditions must also be specified.
Functions cannot be used on index columns or leading columns of composite indexes in the WHERE clause.