Table scan

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

• When writing SQL in OceanBase Database, for SELECT, UPDATE, DELETE, and REPLACE statements, if a WHERE clause is used, the conditions in the clause should utilize index lookups. You can use the EXPLAIN command to check the execution plan to confirm whether an index query is being used.

• 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.