Table scan

This topic describes the requirements and suggestions on writing SQL statements for table scans in OceanBase Database.

• Use a WHERE clause in a SELECT, UPDATE, DELETE, or REPLACE statement to specify index-based queries. You can run the EXPLAIN command to check whether the execution plan performs index-based queries.

• Avoid full scans of large tables because index scans show better performance in most queries of large tables. However, a full scan can be performed for a static table with less than 100 rows. You can run the EXPLAIN<sql_statement> command to view the execution plan of an SQL statement. OceanBase Database allows you to run the following three EXPLAIN commands:

  • The EXPLAIN BASIC command shows the most basic framework of a plan.

  • The EXPLAIN EXTENDED command extends a plan to its full frame with the most details and is usually used in troubleshooting.

  • The EXPLAIN command shows information that helps you understand the entire execution process of a plan.

• We recommend that you perform full table scans and full table dumps on large tables in a backup database or when you perform data reads in an offline database.

• You cannot perform a query that uses only the LIKE '%...%' or LIKE '%...' clause. You must specify other query conditions.

• You cannot use a function in the indexed column or the leading column of a combined index in a WHERE clause.