An access path is the way Oracle reads the rows of a table: all of it in a full scan, or a part of it through an index. Each access path appears as an operation in the execution plan, such as TABLE ACCESS FULL, INDEX RANGE SCAN, or INDEX SKIP SCAN, and the choice between them decides much of a query's speed. This guide produces each common access path on one table, shows its plan, and explains when the optimizer picks it, including why a full scan is sometimes the better choice.
Sample Table
100,000 orders with a primary key, an index on CUSTOMER_ID, and a composite index on STATUS and ORDER_DATE:
Example:
create table demo_orders (
order_id number constraint demo_orders_pk primary key,
customer_id number not null,
status varchar2(10) not null,
order_date date not null,
amount number(10,2) not null);
insert into demo_orders
select level, mod(level * 7, 5000) + 1,
decode(mod(level, 3), 0, 'NEW', 1, 'SHIPPED', 'DELIVERED'),
date '2025-01-01' + mod(level, 600), mod(level * 13, 500) + 10
from dual connect by level <= 100000;
commit;
create index demo_orders_customer_ix on demo_orders (customer_id);
create index demo_orders_status_date_ix on demo_orders (status, order_date);
exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS')Output:
Table DEMO_ORDERS created. 100,000 rows inserted. Commit complete. Index DEMO_ORDERS_CUSTOMER_IX created. Index DEMO_ORDERS_STATUS_DATE_IX created. PL/SQL procedure successfully completed.
The plans below use the format BASIC +ROWS +COST +PREDICATE, which keeps the columns that matter here: the operation, the estimated rows, the cost, and the predicates.
TABLE ACCESS FULL
A full table scan reads every block of the table and checks each row against the filter:
Example:
explain plan for select * from demo_orders where amount > 400; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________________
Plan hash value: 1815706504
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21844 | 137 (1)|
|* 1 | TABLE ACCESS FULL| DEMO_ORDERS | 21844 | 137 (1)|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("AMOUNT">400)
13 rows selected.There is no index on AMOUNT, so the full scan is the only way. The condition shows as a filter: rows are read first and then discarded when they do not match.
INDEX UNIQUE SCAN
An equality condition on a primary key or unique index finds at most one entry:
Example:
explain plan for select * from demo_orders where order_id = 42; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
______________________________________________________________________________
Plan hash value: 969379040
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO_ORDERS | 1 | 2 (0)|
|* 2 | INDEX UNIQUE SCAN | DEMO_ORDERS_PK | 1 | 1 (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_ID"=42)
14 rows selected.The index returns the ROWID, the row's address, and TABLE ACCESS BY INDEX ROWID reads that one row. A cost of 2 is about as cheap as a read gets.
INDEX RANGE SCAN
A condition that can match several entries, on a non-unique index or a range of values, scans a part of the index:
Example:
explain plan for select * from demo_orders where customer_id = 77; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________
Plan hash value: 3515847313
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 21 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 20 | 21 (0)|
|* 2 | INDEX RANGE SCAN | DEMO_ORDERS_CUSTOMER_IX | 20 | 1 (0)|
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTOMER_ID"=77)
14 rows selected.The 20 ROWIDs from the index are sorted by block before the table is read, which is what BATCHED means: rows in the same block are read together.
Index Only: No Table Access
When every column the query needs is in the index, Oracle does not visit the table at all:
Example:
explain plan for select customer_id, count(*) from demo_orders where customer_id between 100 and 120 group by customer_id; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
________________________________________________________________________________
Plan hash value: 1772361368
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 2 (0)|
| 1 | SORT GROUP BY NOSORT| | 22 | 2 (0)|
|* 2 | INDEX RANGE SCAN | DEMO_ORDERS_CUSTOMER_IX | 440 | 2 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTOMER_ID">=100 AND "CUSTOMER_ID"<=120)
14 rows selected.There is no TABLE ACCESS line. The index is also in CUSTOMER_ID order, so the grouping needs no sort: SORT GROUP BY NOSORT.
INDEX FAST FULL SCAN
When a query needs all rows but only indexed columns, Oracle can read the whole index instead of the table, because the index is smaller:
Example:
explain plan for select count(*) from demo_orders; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained. PLAN_TABLE_OUTPUT _________________________________________________________________________________ Plan hash value: 4268292308 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 62 (0)| | 1 | SORT AGGREGATE | | 1 | | | 2 | INDEX FAST FULL SCAN| DEMO_ORDERS_CUSTOMER_IX | 100K| 62 (0)| ------------------------------------------------------------------------------ 9 rows selected.
COUNT(*) can count the entries of any index on a NOT NULL column, and the optimizer picked the one it estimated cheapest. A fast full scan reads the index blocks in any order, with multi-block reads like a table scan, so the result is not sorted.
INDEX FULL SCAN
An index full scan reads the index in key order. The optimizer uses it when sorted rows are worth it, for example to avoid a sort:
Example:
explain plan for select order_id from demo_orders order by order_id fetch first 10 rows only; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained. PLAN_TABLE_OUTPUT ____________________________________________________________________ Plan hash value: 1959688145 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 2 (0)| |* 1 | COUNT STOPKEY | | | | | 2 | VIEW | | 10 | 2 (0)| | 3 | INDEX FULL SCAN| DEMO_ORDERS_PK | 100K| 2 (0)| ----------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=10) 15 rows selected.
The plan shows 100K rows for the index, but COUNT STOPKEY stops after the first 10, so the cost is only 2.
INDEX SKIP SCAN
A composite index can help even when the query does not filter on its first column. Oracle then scans the index once for each distinct value of the leading column:
Example:
explain plan for select status, order_date from demo_orders where order_date = date '2026-03-15'; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
_______________________________________________________________________________
Plan hash value: 1300709721
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 167 | 5 (0)|
|* 1 | INDEX SKIP SCAN | DEMO_ORDERS_STATUS_DATE_IX | 167 | 5 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ORDER_DATE"=TO_DATE(' 2026-03-15 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
filter("ORDER_DATE"=TO_DATE(' 2026-03-15 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
16 rows selected.The index starts with STATUS, which has only three values, so three small scans find the rows for the date. Skip scans pay off only when the leading column has few distinct values.
When a Full Scan Is the Better Choice
An index is not always faster. Here a third of the orders have the status NEW:
Example:
-- a third of the rows: the optimizer's choice ... explain plan for select * from demo_orders where status = 'NEW'; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE'); -- ... and the same query forced to use the index explain plan for select /*+ index(demo_orders demo_orders_status_date_ix) */ * from demo_orders where status = 'NEW'; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________________
Plan hash value: 1815706504
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33333 | 136 (0)|
|* 1 | TABLE ACCESS FULL| DEMO_ORDERS | 33333 | 136 (0)|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='NEW')
13 rows selected.
Explained.
PLAN_TABLE_OUTPUT
__________________________________________________________________________________________________
Plan hash value: 4170396774
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33333 | 33463 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 33333 | 33463 (1)|
|* 2 | INDEX RANGE SCAN | DEMO_ORDERS_STATUS_DATE_IX | 33333 | 125 (0)|
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='NEW')
14 rows selected.The full scan costs 136; reading the same 33,333 rows through the index costs 33,463. The index finds the entries cheaply, at a cost of 125, but the rows are spread over the whole table, so nearly every row means another table block to read. A full scan reads each block once, in large multi-block reads. The optimizer rejects the index for good reason.
Things to Know
- An index pays off when the query returns a small share of the table. How small depends on how the rows are spread over the blocks, the index's clustering factor.
- access in the predicates means the index was used to find rows; filter means rows were read and then checked.
- Adding the selected columns to an index can remove the table access entirely, at the cost of a larger index.
- Index hints, as in the last example, are useful for comparing plans, not as a fix; the next step when a plan looks wrong is to check the statistics.
Related Guides
Conclusion
Access paths are how Oracle reads a table: full scans, index unique and range scans, index-only access, fast full and full index scans, and skip scans. Read them in the plan together with the estimated rows: indexes win for few rows, full scans for many, and an index that covers the query avoids the table altogether.
