Dear readers, this writing is going to be about access methods to the data in the Oracle Database. Those methods show how to access the data from the tables or indexes. You can see those access methods in the ‘explain plan’ of SQL queries. Oracle promotes the access methods below.
Full Table SCAN (FTS)
Table Access by ROW-ID
Index Unique Scan
Index Range Scan
Index Skip Scan
Full Index Scan
Fast Full Index Scans
Index Joins
Bit Map Index
1.FULL TABLE SCAN (FTS):
Full Table Scan is one of the access methods that are used by Optimizer. All the blocks in the table (till HWM) are scanned and ‘where’ filter conditions are applied and the lines providing the filter condition are returned.
You can read more blocks on a single I/O with the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
Full Table Scans look like below in the explain plan.
How to Avoid FTS?
In most cases, FTS is seen as a performance killer but as opposed to that, FTS isn’t always a bad choice. Optimizer may choose FTS rather than accessing the table with index. Because you can do big I/Os with FTS and it’s good to do less but big I/Os than many but small I/Os. So, rather than taking action immediately when you see FTS in the plan you should make a good analysis.
• Check if the table analyses are up to date or not.
• Check if the indexes are correct.
• Even if the indexes are correct, if there is parallel hint in the query, FTS may be an option for optimizer.
2.TABLE ACCES BY ROW-ID
RowID is the physical place of a line. It’s the quickest way to access data. You can access a table with RowID by either entering ‘rowid = **’ on where condition or getting RowID from an index.
SQL> select * from hr.employees where rowid ='AAAR0RAADAAAHovABH'
If you see ‘Table Access by Rowid’ in the plan of the query, this is generally the second step coming after the index. But, you don’t always need to access the table with rowid after index, if index has all the necessary records the result directly comes from the index.
SQL> select * from hr.employees where employee_id > 160;
Difference Between ROWID and ROWID BATCHED
Table Access by Rowid batched is a feature that came with 12c and it is aimed to work with a better performance than ‘Table Access rowid’.
According to the result from index, it is necessary to access the blocks above. Table access by rowid brings those with order:
•Line 015-000123 from the block numbered 15.
•Line 034-000527 from the block numbered 34.
•Line 088-000285 from the block numbered 88.
•Line 015-000889 from the block numbered 15.
•Line 088-000632 from the block numbered 88.
Table Access by RowID Batched first alines the result from the index according to blcok order and accesses the blocks according to that order. Therefore, the access is as follows:
•Line 015-000123 and 015-000889 from the block numbered 15.
•Line 034-000527 from the block numbered 34.
•Line 088-000285 and 088-000632 from the block numbered 88.
As you can understand from the example, rather than reading the blocks 5 times, they are read 3 times with the rowid Batched.
3.INDEX UNIQUE SCAN:
It returns at most 1 rowid from the index. You need to use the equal sign (=) to do Index Unique Scan. Generally, it’s said that index unique scan is done in the areas of unique or primary key but this isn’t enough alone. On a column with non-unique index, even if you do primary or unique constraint later, Oracle prefers using index range scan rather than index unique scan in the queries here. Because it won’t create a new unique index due to the fact that there already exists an index in that column.
SQL> CREATE TABLE example(col Varchar2(10));
SQL> CREATE INDEX example_idx ON example(col);
SQL> ALTER TABLE example ADD CONSTRAINT example_pk PRIMARY KEY(col);
SQL> SELECT * FROM example WHERE col='Example';
SQL> CREATE TABLE example2 (col varchar2(10), CONSTRAINT example2_pk PRIMARY KEY(col));
SQL> SELECT * FROM example2 WHERE col='Example';
4.INDEX RANGE SCAN:
Index range scan is one of the most common accessing methods. Oracle accesses the index records, finds the rowid equivalents and reaches the table. Index range scan is used in the conditions below:
• col1 = :b1
• col1 < :b1
• col1 > :b1
Indexes are created in a sorted way, that’s why in the queries with like:
• Index range scan is done on the query col1 like ‘ASD%’.
• Index range scan isn’t done on the queries col1 like ‘%ASD’.
SQL> CREATE TABLE test (NAME VARCHAR2(100));
SQL> INSERT INTO test VALUES ('Ekrem');
SQL> INSERT INTO test VALUES ('Oguz');
SQL> INSERT INTO test VALUES ('Alperen');
SQL> COMMIT;
SQL> CREATE INDEX I#test#NAME ON test(NAME);SQL> SELECT * FROM test WHERE NAME LIKE 'E%'; 
SQL> SELECT * FROM test WHERE NAME LIKE '%E';
As in the examples above, index range scan isn’t preferred in the queries like ‘%ASD’. Our example table is single column, that’s why the optimizer preferred doing index full scan because all the result to be returned already exists in the index. So, it returned the result via index without accessing the table. If there were 2 or more columns in the table, full table scan would be preferred.
5.INDEX SKIP SCAN:
It is preferred in columns with composite index. It’s slower than index range scan but faster than FTS.
SQL> CREATE TABLE example3 (NAME VARCHAR2(20), SURNAME VARCHAR2(20), AGE NUMBER);
SQL> INSERT INTO example3 VALUES('EKREM OGUZ','SAHIN',24);
SQL> INSERT INTO example3 VALUES('ALPEREN','ULKU',25);
SQL> INSERT INTO example3 VALUES('MURAT CAN','COBAN',25);
SQL> COMMIT;
SQL> CREATE INDEX I#EXAMPLE3#NAME_AGE ON example3(NAME,AGE);SQL> SELECT * FROM example3 WHERE NAME='EKREM';
SQL> SELECT /*+ index_ss(example3 I#EXAMPLE3#NAME_AGE) */ * FROM example3 WHERE AGE>22;
6.FULL INDEX SCAN:
All the index segment is read. If the reason requested to return is existing the table isn’t read and the result is directly returned via index. If index columns will be sorted, again the result is returned with index full scan. If there is grouping in the query and the columns to be grouped are indexed, do index full scan.
SQL> SELECT employee_id FROM hr.employees;
In this table, the employee_id column is defined as the primary key. Therefore, there is no need to access to the table when only the employee_id column is requested as result. The result is directly returned via index.
SQL> SELECT employee_id FROM hr.employees order by 1 desc;
Again, the result is returned via the same index. Order by didn’t cost anything because the records are already sorted in the index.
SQL> CREATE INDEX I#HR#GROUP ON hr.employees(employee_id, salary);SQL> SELECT employee_id,sum(salary) FROM hr.employees group by employee_id;
SQL> DROP INDEX I#HR#GROUP;SQL> SELECT employee_id,sum(salary) FROM hr.employees group by employee_id;
7.FAST FULL INDEX SCAN:
Its different feature from the full index scan is that it doesn’t read the data in an order, it can read parallel. Oracle put forward that kind of access method to create an alternative other than Full Table Scans. As opposed to full index scan, it can multi block read like FTS. Generally it is preferred in the queries with count(*) and all the columns put in the SELECT and WHERE conditions need to be in the index.
SQL> select /*+ index_ffs(a EMP_EMP_ID_PK) */ count(*) from hr.employees a;
8.INDEX JOINS:
It is joining of the indexes with hash join as a result of the columns requested to return be present in 2 or more indexes. It's an effective way of access in tables that have many columns.
9.BIT MAP INDEX:
Bit map indexes are generally used in Data Warehouse environments. Even though they lessen the query time, they slow down the DML operations. That’s why they aren’t really preferred in OLTP environments. They involve null values differently from the B*Tree indexes.
Hope to see you in new posts, Take care.