Oracle data access methods
Not finding an article in the habre that combines information on data access methods used by Oracle DBMS in a readable form, I decided to try a pen and write this article.
Without going into details, it can be argued that Oracle stores data in tables, with which special data structures may exist — indexes designed to speed up queries to tables. When executing queries, Oracle treats tables and indexes differently — ways to access data in different situations and are the subject of this article.
For examples, we will use the following table and the data in it:
To analyze the query execution plan, we will use the following tools:
After creating the index and using it in the examples and before creating the next index, it should be deleted. This can be done using the following query:
This access method, as the name implies, involves iterating through all the rows of a table with the exception of those that do not satisfy the where predicate (if any). It is used either in the case when the predicate conditions are absent in the index, or when there is no index in principle. Examples:
This index is used in cases when we uniquely know the internal identifier of the table row we are interested in (ROWID). This happens in two cases:
Moving on to the access methods used by Oracle in the case of indexes.
This access method scans all leaf blocks of the index to match the predicate conditions. In order for Oracle to use this access method, at least one of the key fields must have a NOT NULL constraint, since only in this case the corresponding row of the table will be indexed. This method is usually faster than TABLE FULL SCAN, but slower than INDEX RANGE SCAN (see below).
')
This access method is used when all the requirements for INDEX FULL SCAN are fulfilled, as well as all the data selected by the query are 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 the returned values is not regulated. Oracle also cannot use this method for bitmap indexes.
This access method is used by Oracle if the where clause includes index columns with conditions = (if indexed values are non-unique),>, <, and also like 'pattern%', with wildcard characters following 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:
This access method is used when, due to UNIQUE / PRIMARY KEY constraints, as well as predicate conditions, the query must return zero or one value.
Example:
This access method is used when the first column of the index 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):
Assertions that under certain conditions cost-based-optimizer (CBO) will choose one or another access method may not be entirely fair in some cases, since the logic of determining the optimal method by the optimizer is very complicated.
general information
Without going into details, it can be argued that Oracle stores data in tables, with which special data structures may exist — indexes designed to speed up queries to tables. When executing queries, Oracle treats tables and indexes differently — ways to access data in different situations and 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 should be deleted. This can be done using the following query:
drop index index_name; TABLE FULL SCAN
This access method, as the name implies, involves iterating through all the rows of a table with the exception of those that do not satisfy the where predicate (if any). It is used either in the case when the predicate conditions are absent in the index, or when there is no index 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, also known as ROWID
This index is used in cases when we uniquely know the internal identifier of the table row we are interested in (ROWID). This happens in two cases:
- We specified the string identifier in the where predicate;
- The ROWID of the requested entry was found in the index;
Moving 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 use this access method, at least one of the key fields must have a NOT NULL constraint, since only in this case the corresponding row of the table 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 fulfilled, as well as all the data selected by the query are 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 the returned values is not regulated. Oracle also cannot use this method for bitmap indexes.
INDEX RANGE SCAN
This access method is used by Oracle if the where clause includes index columns with conditions = (if indexed values are non-unique),>, <, and also like 'pattern%', with wildcard characters following 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 constraints, 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 when the first column of the index 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
Assertions that under certain conditions cost-based-optimizer (CBO) will choose one or another access method may not be entirely fair in some cases, since the logic of determining the optimal method by the optimizer is very complicated.
Source: https://habr.com/ru/post/189574/
All Articles