Oracle Data Access Methods

Having not found an article on the hub that combines information on data access methods used by Oracle DBMS in an easy-to-read form, I decided to make a “pen test” and write this article.

general information

Without going into details, it can be argued that Oracle stores data in tables, along with which there may be special data structures - indexes, designed to speed up queries on tables. When executing queries, Oracle refers to tables and indexes in different ways - the methods of accessing data in various situations are the subject of this article.

For examples, we will use the following table and the data in it:

create table t1 (t1_key number, t1_value varchar2(10));
insert into t1 values(1, '1');
insert into t1 values(2, '2');
insert into t1 values(3, '3');
insert into t1 values(4, '4');
insert into t1 values(5, '5');
insert into t1 values(6, '6');
insert into t1 values(7, '7');
insert into t1 values(8, '8');
insert into t1 values(9, '9');

To analyze the query execution plan, we will use the following tools:

explain plan for [query goes here];
select * from table(dbms_xplan.display(null,null,'basic'));

After creating the index and using it in the examples and before creating the next index, it must be deleted. This can be done with the following query:

drop index index_name;

TABLE FULL SCAN

This access method, as the name implies, involves enumerating all the rows of the table with the exception of those that do not satisfy the where predicate (if any). It is used either when the predicate conditions are not in the index, or when the index is not in principle. Examples:

select t1_key, t1_value from t1 where t1_key = 5;
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------
create index key_index on t1 (t1_key);
select t1_key, t1_value from t1 where t1_value = '5';
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------

TABLE ACCESS BY ROWID, aka ROWID

This index is used in cases when we clearly know the internal identifier of the table row of interest to us (ROWID). This occurs in two cases:

• We specified the line identifier in the where predicate;
• The ROWID of the requested record was found in the index;

We move on to the access methods used by Oracle in the case of indexes.

INDEX FULL SCAN

This access method scans all leaf blocks of the index to match the predicate conditions. In order for Oracle to apply this access method, at least one of the key fields must have a NOT NULL constraint, because only in this case the corresponding table row will be indexed. This method is usually faster than TABLE FULL SCAN, but slower than INDEX RANGE SCAN (see below).

INDEX FAST FULL SCAN

This access method is used when all the requirements for INDEX FULL SCAN are met, as well as all the data selected by the query is contained in the index and thus access to the table itself is not required. Unlike INDEX FULL SCAN, this method can read index blocks in several parallel threads, and thus the order of returned values ​​is not regulated. Oracle also cannot use this method for bitmap indexes.

INDEX RANGE SCAN

This access method is used by Oracle in the event that the where predicate includes columns of the index with conditions = (in case the indexed values ​​are not unique),>, <, and also like 'pattern%', and wildcard-characters should be after the required substring. Unlike TABLE FULL SCAN, when using this access method, Oracle does not iterate over all leaf blocks and therefore, in most cases, INDEX RANGE SCAN is faster.
Example:

select * from t1 where t1_key = 3;
-------------------------------------------------
| Id  | Operation                   | Name      |
-------------------------------------------------
|   0 | SELECT STATEMENT            |           |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |
|   2 |   INDEX RANGE SCAN          | KEY_INDEX |
-------------------------------------------------

INDEX UNIQUE SCAN

This access method is used when, due to UNIQUE / PRIMARY KEY restrictions, as well as predicate conditions, the query must return zero or one value.
Example:

create unique index u_key_index on t1 (t1_key);
---------------------------------------------------
| Id  | Operation                   | Name        |
---------------------------------------------------
|   0 | SELECT STATEMENT            |             |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |
|   2 |   INDEX UNIQUE SCAN         | U_KEY_INDEX |
---------------------------------------------------

INDEX SKIP SCAN

This access method is used if the first index column is not used in the where predicate.
For an example of using this access method, we need another table (note that the number of rows, data, etc. will depend on what is in the used scheme, and therefore this example may not be reproduced immediately):

create table t2 as (select * from all_objects);
--столбец data_object_id должен иметь селективность значительно ниже, чем object_id
create index test_index on t2 (data_object_id, object_id);
--пересоберем статистику для таблицы
begin
  dbms_stats.gather_table_stats(user, 'T2', cascade=>true);
end;
--И наконец запрос
select * from t2 where object_id=370; --не забудьте сменить object_id
--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2         |
|   2 |   INDEX SKIP SCAN           | TEST_INDEX |
--------------------------------------------------

DISCLAIMER

Statements that under certain conditions cost-based-optimizer (CBO) will choose one or another access method may not be entirely true in some cases, since the logic of determining the optimal method by the optimizer is very complicated.