Tune SQL without changing its results

Development and modifications

26R1 reference · Documentation-based walkthrough · Sources reviewed 2026-09-28 · Not executed in IFS or Oracle. Match the database, build home, and tools to your installed release. Example names are fictional.

Tune a query by proving its result contract first. Use original fixtures to expose differences that a faster-looking rewrite can hide, then measure the representative real query.

Before you start

Use an authorized SQL worksheet and approved data sources. Start with the read-only SQL semantics fixtures. Obtain DBA assistance for actual-plan statistics if your role does not have the required diagnostic access. Do not grant broad privileges just to run a tutorial.

1. Define the result contract

Record selected columns, expected grain, keys, ordering, null behavior, and the caller’s data restrictions. Choose only the needed columns. Preserve company/site restrictions when replacing API lookups with joins; a direct base table may omit protections enforced by the supported view or service.

Use bind values of the correct datatype. Avoid implicit conversion of indexed values and avoid unnecessary DISTINCT, sorting, or grouping used to hide a broken join. The reporting SQL guide supplies a separate calculation fixture.

2. Compare membership and anti-membership correctly

Run test 1 in the download. The candidate keys are 1 and 2; the accepted set contains 1 and null.

Expression	Expected number of candidate rows
`IN`	1
Correlated `EXISTS`	1
`NOT IN`	0
Correlated `NOT EXISTS`	1

The null in the subquery changes NOT IN semantics. Do not substitute an anti-join simply because the positive forms looked equivalent. If candidate keys can also be null, define their intended treatment explicitly.

EXISTS expresses a matching-row requirement clearly, but is not universally faster than IN. The optimizer may transform equivalent forms into similar plans. Verify equivalence and measure the actual workload; documentation examples are not a universal ranking of operators.

3. Distinguish a maximum value from a selected row

Run tests 2 and 3. Two events share amount 20.

MAX(amount) answers the scalar-value question and returns null for an empty/all-null input.
Filtering rows against that maximum can return multiple tied records.
ORDER BY amount DESC NULLS LAST, event_id DESC FETCH FIRST 1 ROW ONLY selects one row using an explicit tie-breaker; it returns no row for an empty set.

The sample’s chosen row is event 203 with amount 20. A unique, stable tie-breaker makes the result reproducible. Do not use an unordered first row to conceal unexpected duplicates in a supposedly unique lookup. Appropriate indexes and optimizer choices can avoid or reduce sorting; neither syntax is always faster.

4. Keep predicates meaningful and searchable

Compare stored enumeration/database values where the supported source exposes them, rather than translated display labels that vary by language.
Keep date/number types until presentation. For an agreed day boundary, use a typed half-open range instead of converting every row to text.
Review per-row PL/SQL calls in SELECT and WHERE. Determine whether a correctly keyed join or precomputed invariant removes work without bypassing business logic.
Inspect the actual execution plan and row estimates. Functions do not invariably disable every index; function-based indexes and transformations affect the result.
Compare actual rows, elapsed time, and logical reads over representative bind values, including skewed cases.

Example predicate, after defining the real timestamp/day-zone contract:

WHERE scheduled_at >= :day_start
  AND scheduled_at < :next_day_start

Do not move all filtering into a PL/SQL loop and fetch every row merely to remove a function from the WHERE clause.

5. Use SELECT INTO for a one-row contract

When exactly one row is expected, SELECT ... INTO ... naturally exposes NO_DATA_FOUND and TOO_MANY_ROWS. Define what each means. Returning null for a missing optional lookup may be correct; returning null for a duplicate supposedly unique key hides a defect.

An aggregate query without GROUP BY normally returns one row even when no data matches, so a null aggregate is not the same as NO_DATA_FOUND. Test both shapes deliberately.

6. Use function features for their actual contracts

PRAGMA UDF tells Oracle that a function is primarily used from SQL and may improve performance. It is not a general speed guarantee or a reason to wrap simple SQL expressions in functions. Check function placement/toolchain compatibility and benchmark it.

DETERMINISTIC is a promise about repeatable outputs and side effects, not a general result-cache switch. Do not mark a function deterministic when it depends on mutable tables, session/user state, current time, or other hidden inputs. Follow Oracle’s full semantic restrictions and dependency maintenance requirements.

A pipelined table function returns a supported collection type, not SYS_REFCURSOR. The original pipelined-function exercise creates a bounded function in a disposable schema:

CREATE FUNCTION c_demo_even_numbers (count_ IN PLS_INTEGER)
   RETURN SYS.ODCINUMBERLIST PIPELINED

Run the full file, inspect compilation errors, then query it with 3: expected values are 2, 4, and 6. Zero returns no rows; null, negative, or more than 1000 raises the defined error. This is standalone Oracle DDL, not an IFS deployment file. Creating/dropping objects commits implicitly. Confirm the name is unused and remove the exercise function only after checking its dependencies.

Troubleshooting

Unexpected counts suggest join or null semantics. Unstable first-row results suggest a missing tie-breaker. A slower plan needs actual statistics and bind distributions, not operator substitution by habit. A pipelined compilation error needs a valid collection return type and complete implementation.

Exercise and solution

Exercise: a lookup expects a unique key but returns two rows. Is adding FETCH FIRST 1 ROW ONLY the fix?

Show the solution

No. Investigate the key, query joins, and data integrity. Selecting an arbitrary row changes the contract. A top-one clause is appropriate only when selecting one of several valid rows is the actual requirement, with an explicit ordering rule.

Cleanup and delivery evidence

The CTE fixtures need no cleanup. Drop the optional standalone function only in the disposable exercise schema. Retain equivalent-result evidence and representative before/after plans with the reviewed change.