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 execute the EXPLAIN statement 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 execute the EXPLAIN<sql_statement> statement to view the execution plan of an SQL statement. OceanBase Database allows you to execute the EXPLAIN statement in the following three modes:

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

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

  • The EXPLAIN statement 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.