Table scanning mode specification

This topic describes the table scanning mode specification in OceanBase Database SQL statements.

• In OceanBase Database SQL statements, for SELECT, UPDATE, DELETE, and REPLACE statements, if the WHERE clause is used, the conditions in the WHERE clause should be queried through indexes. You can use the EXPLAIN command to check the execution plan and confirm whether index queries are used.

• Avoid full table scans for large tables: Generally, most queries on large tables use index scans instead of full table scans. However, for static tables with fewer than 100 rows, full table scans are acceptable. Use the EXPLAIN<sql_statement> command to view the execution plan of the SQL statement. The execution plan command has three modes:

  • The EXPLAIN BASIC command is used for the most basic plan display.

  • The EXPLAIN EXTENDED command is used for the most detailed plan display (usually used when troubleshooting).

  • The information displayed by the command helps ordinary users understand the execution method of the entire plan.

• Full table scans and full table exports (dump) for large tables are recommended to be performed in the backup database or offline read-only database.

• In the clause, you cannot use only full fuzzy or left fuzzy LIKE conditions for queries. Other query conditions must be provided.

• Functions cannot be used on indexed columns or leading columns of composite indexes in the WHERE clause.