Oracle 26ai · SQL Tuning · Module 8

Lesson 3

Data Distribution and Cost-Based Plan Choices

Cost-based planning makes information about the data relevant to choosing an execution strategy. Different values in the same column can match very different populations. Recognizing those differences can improve estimates and help compare the work of candidate plans.

One column, unequal populations

Consider an illustrative EMPLOYEE table with 100,000 rows. Finance accounts for 70,000 employees, Marketing for 5,000, and all other departments together for 25,000. These are invented teaching counts, not measurements of an organization.

Illustrative department distribution
Department groupRowsShare
Finance70,00070%
Marketing5,0005%
Other departments25,00025%
SELECT e.employee_name
FROM employee e
WHERE e.department = :department;

The bind placeholder is not enclosed in quotes. The calling application supplies its value using a datatype compatible with the column. Writing ':department' would compare against literal text instead.

Why distribution helps

An index on DEPARTMENT might be useful for a selective request. For a request returning many rows, obtaining those rows through scattered index lookups might cost more than scanning. But 5% and 70% are not universal thresholds. Table blocks, index structure, requested columns, cache conditions, and other operations all influence the work.

Statistics and relevant histograms can help estimate a skewed distribution. They do not guarantee perfect estimates or a globally fastest plan. A full scan is not an error merely because an index exists; its suitability depends on the actual query.

Bind values and cursor reuse

Do not assume Oracle constructs a fresh plan for every bind value. Cursor sharing allows reuse, while bind peeking and adaptive cursor sharing can affect how value-sensitive statements are handled. These mechanisms do not promise a distinct ideal plan for every value. Test representative common and uncommon values, and identify the relevant child cursor when investigating.

Benefits extend beyond indexes

The optimizer can compare join orders and methods and consider transformations that expose alternative strategies. A useful estimate early in a plan can improve downstream decisions about row processing. Conversely, a poor early estimate can affect later choices. Treat the plan as connected operations rather than independent good or bad icons.

Maintenance is part of the workload

Automatic statistics collection provides a starting point. Assess important loads, purges, and skew changes in relation to collection windows and query requirements. Do not gather statistics on every object after every row change, or prescribe an arbitrary tiny sample as universally sufficient.

A practical comparison records correct output and representative measurements before and after a justified change. If a plan helps Marketing but harms Finance, the investigation must account for both uses. The next lesson examines estimation limits and regressions.

Oracle references