All Products
Search
Document Center

Overview

Last Updated: Jun 18, 2021

The access path refers to the method of accessing a table in the database, or which index is used to access the table.

Analyzing access paths is one of the most important jobs in querying a single table. If you scan a table based on primary keys, the execution time is positively proportional to the volume, or range, of the data to be scanned. You can run the EXPLAIN EXTENDED command to display the scan range of the table. For queries with appropriate indexes, the use of indexes can greatly reduce the data access workload. Therefore, for a query that involves a primary table scan, analyze the reason for not using the index, be it the unavailability of the index, or the huge index scan range that causes extremely high cost.

The selection method of access paths in OceanBase Database combines both the rule-based and cost-based methods. The rule-based method is used first in OceanBase Database. If only one path is available, this path is used. Otherwise, the cost-based method is applied to select the path with the lowest cost.

OceanBase Database allows you to specify the access path in a hint in the following format: /+INDEX(table_name index_name)/.

In the preceding hint example, the table_name field indicates the table name, and the index_name field indicates the index name. If you set index_name to PRIMARY, a table is scanned based on primary keys.

The following code shows how to specify an access path in a hint:

obclient>CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, d INT, INDEX k1(b,c));
Query OK, 0 rows affected (0.10 sec)

obclient>EXPLAIN SELECT/*+INDEX(t1 PRIMARY)*/ * FROM t1;
| ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |1000     |476 |
===================================

Outputs & filters:
-------------------------------------
  0 - output([t1.a], [t1.b], [t1.c], [t1.d]), filter(nil),
      access([t1.a], [t1.b], [t1.c], [t1.d]), partitions(p0)


obclient>EXPLAIN SELECT/*+INDEX(t1 k1)*/ * FROM t1;
| =====================================
|ID|OPERATOR  |NAME  |EST. ROWS|COST|
-------------------------------------
|0 |TABLE SCAN|t1(k1)|1000     |5656|
=====================================

Outputs & filters:
-------------------------------------
  0 - output([t1.a], [t1.b], [t1.c], [t1.d]), filter(nil),
      access([t1.a], [t1.b], [t1.c], [t1.d]), partitions(p0)