Efficiency Rank |
Access Method |
Notes |
1 |
Single row by rowid |
Fastest access: direct lookup via known ROWID. |
2 |
Single row by cluster join |
Fetch single row through a cluster join — very fast. |
3 |
Single row by hash cluster key with unique or primary key |
Hash-based lookup using unique/primary keys. |
4 |
Single row by unique or primary key |
Direct index lookup via unique constraint. |
5 |
Cluster join |
Join operation within a clustered table. |
6 |
Hash cluster key |
Access via hash cluster's key without primary/unique constraints. |
7 |
Indexed cluster key |
Access via index on cluster key. |
8 |
Composite key |
Access using multiple columns in an index (composite). |
9 |
Single-column indexes |
Simple indexes on single columns, relatively efficient. |
10 |
Bounded range search on indexed columns |
Searching within a defined range (BETWEEN, >=, <=) using indexes. |
11 |
Unbounded range search on indexed columns |
Open-ended range search, less efficient (> or < without a limit). |
12 |
Sort-merge join |
Costlier than index lookups; used when indexed joins are not feasible. |
13 |
MAX or MIN of indexed column, ORDER BY on indexed columns |
Sorting operations even if using indexes can be moderately expensive. |
14 |
Full table scan |
Least efficient; scanning every row in the table sequentially. |