The orders dashboard is slow, so you pull up the Oracle execution plan. There it is: TABLE ACCESS FULL on a million-row table. The query filters on status, and there’s already an index on that column. You run the same query with a different status and see an index range scan. It’s tempting to conclude that Oracle missed an obvious optimization on the first run.
Now look at what the two queries ask for. One totals nearly every order the system has ever shipped. The other totals a small set of orders held for review. Both have the same SQL shape and use the same filter column, but they ask Oracle to retrieve very different amounts of data. We’ll follow both access paths, see where the work goes, and use the executed plan to check whether the full scan makes sense.
An index finds the rows. Oracle may still need the table.
An access path is how the database retrieves rows. With a B-tree index on status, Oracle can navigate to the first matching entry and walk the matching leaf entries. That operation appears as INDEX RANGE SCAN. Each entry carries a rowid, which identifies where a row lives in the table.
If the query also needs amount and the index contains only status, Oracle uses those rowids to fetch amount from the table. In the plan, look for TABLE ACCESS BY INDEX ROWID above the range scan. Oracle can also batch rowids and try to visit rows in block order. That variant appears as TABLE ACCESS BY INDEX ROWID BATCHED. A block is the unit in which Oracle stores and accesses table data. One block can hold many rows.
The trade-off depends on how many blocks you touch
With a full scan, Oracle reads the table’s data blocks and applies the filter as it scans. It can read multiple adjacent blocks in a single I/O request. With a range scan that fetches table rows, Oracle reads the matching index entries and then visits the blocks those entries point to.
Imagine a table with 10,000 blocks. If a query’s matching rows occupy 20 of them, the index can help Oracle skip most of the table. If the matching rows are spread across almost all 10,000 blocks, the index path can visit almost the whole table anyway, on top of reading the index. Batching can improve the order of those visits. It still has to retrieve the rows.
Oracle’s clustering factor describes how closely table row placement follows a particular index’s key order. A value near the table’s block count suggests that rows encountered close together in index order tend to share blocks. A value near the row count suggests more scattered visits. That’s one reason the same percentage of matching rows can favor different plans on different tables.
Skew can give the same query two sensible plans
Selectivity is the fraction of rows that satisfy a predicate. When values occur at very different frequencies, the data is skewed. For an orders table where SHIPPED is common and HELD is rare, an average across all statuses can hide the difference between these two queries. A histogram, a statistic that describes the distribution of values in a column, can help Oracle estimate their cardinalities, meaning the numbers of rows expected from each operation.
The columns you request matter too. An index on (status, amount) could supply both columns needed by the example below and avoid table rowid fetches. That changes the cost comparison. The worked example uses an index on status alone so you can see the cost of going back to the table.
Worked example in Oracle
Run this in a scratch schema. The table has one million rows. Every thousandth row is HELD, so those rows are spread across the load. SHIPPED accounts for 900,000 rows. The padding keeps the rows closer in size to a wider application table.
CREATE TABLE scan_demo_orders AS
SELECT LEVEL AS order_id,
CASE
WHEN MOD(LEVEL, 1000) = 0 THEN 'HELD'
WHEN MOD(LEVEL, 1000) <= 900 THEN 'SHIPPED'
ELSE 'PENDING'
END AS status,
MOD(LEVEL, 10000) / 100 AS amount,
RPAD('x', 100, 'x') AS padding
FROM dual
CONNECT BY LEVEL <= 1000000;
CREATE INDEX scan_demo_orders_status_ix
ON scan_demo_orders (status);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'SCAN_DEMO_ORDERS',
estimate_percent => 100,
method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 3 STATUS',
cascade => TRUE
);
END;
/
Run each aggregate and display its cursor plan immediately afterward in the same session. The GATHER_PLAN_STATISTICS hint collects execution statistics for the statement.
SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(amount)
FROM scan_demo_orders
WHERE status = 'HELD';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(amount)
FROM scan_demo_orders
WHERE status = 'SHIPPED';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
Illustrative cursor-plan excerpts. These show possible plans and example row counts. Actual plans depend on the optimizer environment.
Predicate: STATUS = 'HELD'
Predicate: STATUS = 'HELD'
| Id | Operation | Name | E-Rows | A-Rows |
|----|---------------------------------------|----------------------------|--------|--------|
| 1 | SORT AGGREGATE | | 1 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | SCAN_DEMO_ORDERS | 1000 | 1000 |
| 3 | INDEX RANGE SCAN | SCAN_DEMO_ORDERS_STATUS_IX | 1000 | 1000 |
Predicate: STATUS = 'SHIPPED'
| Id | Operation | Name | E-Rows | A-Rows |
|----|---------------------------------------|----------------------------|--------|--------|
| 1 | SORT AGGREGATE | | 1 | 1 |
| 2 | TABLE ACCESS FULL | SCAN_DEMO_ORDERS | 900000 | 900000 |
Both aggregates return one row. The difference is in the work beneath them. The HELD query can use the index to identify 1,000 rowids and fetch their amounts. The SHIPPED scan emits 900,000 rows after applying the filter. To check how much work that took, look at Buffers in your own cursor plan. It counts logical block accesses. E-Rows and A-Rows show estimated and actual output rows, respectively. For these single-start access operations, you can compare them directly.
Top comments (0)