Db2 Anti-Pattern: Unnecessary DISTINCT and Sorts

The anti-pattern in one example

Suppose a screen needs customers who placed an open order. CUSTOMER is the parent and SALES_ORDER is the child. A customer with four open orders matches four child rows. If the query projects only customer columns, those four joined combinations look identical. Adding DISTINCT reduces them to one displayed customer, but Db2 still had to perform the join and then enforce uniqueness.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Symptom treatment: multiply rows, then remove duplicates
SELECT DISTINCT C.CUSTOMER_ID,
                C.CUSTOMER_NAME
  FROM CUSTOMER AS C
  JOIN SALES_ORDER AS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
 WHERE O.ORDER_STATUS = 'OPEN';
 
-- Intentional existence test
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME
  FROM CUSTOMER AS C
 WHERE EXISTS (
       SELECT 1
         FROM SALES_ORDER AS O
        WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
          AND O.ORDER_STATUS = 'OPEN'
       );

EXISTS states the real question: does at least one qualifying order exist? It does not promise that the rewrite is always faster; Db2 remains cost based and can transform SQL internally. It does, however, remove accidental row multiplication from the logical request. A useful index beginning with CUSTOMER_ID and including or continuing with ORDER_STATUS can make the existence probe efficient, depending on cardinality and statistics.

What sort-heavy SQL asks Db2 to do

A sort reads input rows, compares sort keys, and produces ordered runs or a final ordered result. If the input exceeds available sort resources, processing can use the Db2 work file database. Work files are shared subsystem resources used for sorts and other intermediate results. A large or concurrent sort workload can therefore affect more than one statement. The exact implementation can include in-memory work, work-file pages, merge phases, and optimizer techniques that avoid a separate sort altogether.

Cost grows with more than row count. Wide sort rows require more storage and movement. Long character keys cost more to compare than compact numeric keys. Duplicate elimination may inspect many rows before producing a small answer. Concurrent reports compete for CPU, buffer pools, and work-file capacity. The right goal is not “ban sorting”; it is “request only the ordering and uniqueness the result actually needs.”

When DISTINCT is semantically correct

DISTINCT applies to the complete projected row. If the query selects DEPARTMENT_ID and JOB_CODE, Db2 returns each unique pair, not one row per department and not one row per job. This is correct when the business result really is a set of combinations, such as the job codes currently represented in each department.

1
2
3
4
5
6
7
8
9
10
11
12
-- Correct: the requirement is one row per represented pair
SELECT DISTINCT DEPARTMENT_ID,
                JOB_CODE
  FROM EMPLOYEE
 WHERE EMPLOYMENT_STATUS = 'ACTIVE';
 
-- Also correct: count unique active job codes per department
SELECT DEPARTMENT_ID,
       COUNT(DISTINCT JOB_CODE) AS ACTIVE_JOB_CODE_COUNT
  FROM EMPLOYEE
 WHERE EMPLOYMENT_STATUS = 'ACTIVE'
 GROUP BY DEPARTMENT_ID;

COUNT(DISTINCT expression) has different semantics from SELECT DISTINCT. It counts distinct non-null values of the expression within the applicable group. Do not remove it merely because uniqueness costs resources. First establish whether repeated values should count once or many times. Performance tuning must not change a business metric.

A practical correctness test

• Write down the logical key of one output row.
• Ask whether two source combinations may legitimately map to that same output key.
• Decide whether repeated projected values carry meaning or must collapse into one.
• Verify null behavior, because DISTINCT treats duplicate null-containing rows as duplicates.
• Keep DISTINCT when uniqueness is part of the result contract, then tune its access path.

Find the root cause of duplicates

SQL joins do not randomly create duplicates. They produce one row for every combination satisfying the join predicates. Duplicate-looking output usually means the selected columns hide the columns that distinguish those combinations. Before adding DISTINCT, temporarily select identifiers from every joined table and inspect the relationships.

1
2
3
4
5
6
7
8
9
10
11
-- Diagnostic query: expose the rows that differ
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       O.ORDER_ID,
       O.ORDER_STATUS
  FROM CUSTOMER AS C
  JOIN SALES_ORDER AS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
 WHERE O.ORDER_STATUS = 'OPEN'
 ORDER BY C.CUSTOMER_ID,
          O.ORDER_ID;

• Expected one-to-many relationship: use EXISTS when only parent existence matters, aggregate children when a summary is needed, or return child details when every child is meaningful.
• Incomplete join predicate: include every column that defines the relationship, such as tenant, company, version, sequence, or effective date.
• Many-to-many relationship: confirm that the bridge table and result grain are intentional rather than assuming one row per entity.
• Dirty or weakly constrained data: repair governance and enforce an appropriate unique key where the business rule truly guarantees uniqueness.
• Multiple current rows: define effective-date and status rules rather than using DISTINCT to choose no particular row.

Never “fix” duplicates by adding a join predicate that merely happens to discard data. The predicate must represent the actual relationship. Likewise, adding a unique index is safe only when existing data and future business rules permit exactly one row for the key. A performance symptom often reveals a missing data-model decision.

Safer alternatives for common intentions

Existence: use EXISTS or NOT EXISTS

When the result needs parent columns and only asks whether matching children exist, EXISTS is usually the clearest expression. For customers without open orders, use NOT EXISTS. Avoid a left join followed by DISTINCT unless the outer-join result itself is required.

1
2
3
4
5
6
7
8
9
10
-- Customers with no open orders
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME
  FROM CUSTOMER AS C
 WHERE NOT EXISTS (
       SELECT 1
         FROM SALES_ORDER AS O
        WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
          AND O.ORDER_STATUS = 'OPEN'
       );

Summary: aggregate at the required grain

If the screen needs one row per customer plus order facts, summarize the child rows. GROUP BY is not a magic faster DISTINCT; here it is correct because COUNT and SUM are defined per customer. Confirm whether a customer with no orders should appear, and use an outer join only when that requirement exists.

1
2
3
4
5
6
7
8
9
10
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       COUNT(*) AS OPEN_ORDER_COUNT,
       SUM(O.ORDER_TOTAL) AS OPEN_ORDER_TOTAL
  FROM CUSTOMER AS C
  JOIN SALES_ORDER AS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
 WHERE O.ORDER_STATUS = 'OPEN'
 GROUP BY C.CUSTOMER_ID,
          C.CUSTOMER_NAME;

One chosen child: state the choice explicitly

DISTINCT cannot choose the latest order because orders with different dates are not duplicates, and projecting fewer columns loses required detail. Use a window function or another valid greatest-per-group technique. Window ordering can itself require a sort, but that sort implements a real rule rather than hiding arbitrary duplication.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT CUSTOMER_ID,
       ORDER_ID,
       ORDER_TIMESTAMP
  FROM (
       SELECT O.CUSTOMER_ID,
              O.ORDER_ID,
              O.ORDER_TIMESTAMP,
              ROW_NUMBER() OVER (
                PARTITION BY O.CUSTOMER_ID
                ORDER BY O.ORDER_TIMESTAMP DESC,
                         O.ORDER_ID DESC
              ) AS RN
         FROM SALES_ORDER AS O
        WHERE O.ORDER_STATUS = 'OPEN'
       ) AS R
 WHERE RN = 1;

ORDER_ID is a tie-breaker, making the selected row deterministic when timestamps match. An index whose leading columns align with status, customer, and descending order may help, but key order should be designed from the full workload. Do not add a large index for one statement without measuring insert, update, logging, utility, storage, and buffer-pool costs.

DISTINCT versus GROUP BY

SELECT DISTINCT A, B and SELECT A, B GROUP BY A, B commonly describe the same set of unique pairs when no aggregates are present. Replacing one spelling with the other does not remove the uniqueness requirement. Db2 may choose the same or a similar access path. Use DISTINCT to communicate unique projection and GROUP BY to communicate grouping for aggregates or group-level HAVING conditions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Unique projection
SELECT DISTINCT REGION_CODE,
                PRODUCT_CODE
  FROM SALES;
 
-- Logically equivalent grouping when no aggregate is present
SELECT REGION_CODE,
       PRODUCT_CODE
  FROM SALES
 GROUP BY REGION_CODE,
          PRODUCT_CODE;
 
-- GROUP BY has a real purpose here
SELECT REGION_CODE,
       PRODUCT_CODE,
       SUM(SALES_AMOUNT) AS TOTAL_SALES
  FROM SALES
 GROUP BY REGION_CODE,
          PRODUCT_CODE
HAVING SUM(SALES_AMOUNT) > 100000;

Do not infer performance from syntax alone. Existing index order, estimated cardinality, parallelism, selected join sequence, and Db2 optimizer transformations influence the implementation. Compare explained plans with the same catalog statistics and bind environment.

ORDER BY: required guarantee or wasted work?

ORDER BY is the only SQL clause that guarantees final result order. Rows read through an index may appear ordered today, but Db2 can select a different index, scan a table space, use parallelism, change join methods, or merge partitions differently tomorrow. Keep ORDER BY whenever a user, file, API, cursor process, ranking rule, or FETCH FIRST request depends on sequence.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Deterministic newest orders
SELECT ORDER_ID,
       CUSTOMER_ID,
       ORDER_TIMESTAMP
  FROM SALES_ORDER
 WHERE CUSTOMER_ID = :CUSTOMER_ID
 ORDER BY ORDER_TIMESTAMP DESC,
          ORDER_ID DESC
 FETCH FIRST 25 ROWS ONLY;
 
-- Any 25 qualifying rows: valid only if the caller truly has no preference
SELECT ORDER_ID,
       CUSTOMER_ID,
       ORDER_TIMESTAMP
  FROM SALES_ORDER
 WHERE CUSTOMER_ID = :CUSTOMER_ID
 FETCH FIRST 25 ROWS ONLY;

Unnecessary ordering often appears inside reusable views, subqueries, exports that later sort again, or service queries whose consumers treat results as an unordered set. Remove it only after tracing every consumer. Conversely, never remove ORDER BY from a top-N query that means “latest 25”; without ordering, it means “some 25.”

A compatible index can let Db2 retrieve keys in the requested sequence and avoid a separate sort. Equality predicates on leading keys followed by ordering keys often form a useful pattern. Yet index-only coverage, filtering, clustering, prefetch, reverse scans, mixed ASC and DESC requirements, and the number of qualifying rows affect the decision. ORDER BY describes semantics; the optimizer decides whether sorting is needed.

Other hidden sources of sort pressure

• UNION removes duplicate rows across inputs. Use UNION ALL when duplicates are acceptable or impossible and no uniqueness contract exists.
• INTERSECT and EXCEPT have set semantics that can require comparison and duplicate handling. Keep them when they express the requirement.
• Merge join can require ordered inputs. A sort may support an otherwise efficient join, so evaluate the total plan instead of one operator.
• Window functions use PARTITION BY and ORDER BY specifications. Different window orderings can require separate processing.
• GROUP BY and aggregate DISTINCT can require grouping or uniqueness work even when no final ORDER BY appears.

1
2
3
4
5
6
7
8
9
-- UNION removes duplicates
SELECT CUSTOMER_ID FROM CURRENT_CUSTOMER
UNION
SELECT CUSTOMER_ID FROM ARCHIVE_CUSTOMER;
 
-- Use only when duplicate IDs are acceptable or impossible
SELECT CUSTOMER_ID FROM CURRENT_CUSTOMER
UNION ALL
SELECT CUSTOMER_ID FROM ARCHIVE_CUSTOMER;

If current and archive tables can overlap during migration, UNION ALL changes the answer. Prove disjointness through time ranges, status rules, constraints, or data governance before choosing it. “UNION ALL is faster” is not enough.

Indexes, access paths, and work files

Indexes can provide useful ordering because their keys are maintained in a defined sequence. For a query filtering one CUSTOMER_ID and ordering by ORDER_TIMESTAMP descending, an index beginning with CUSTOMER_ID followed by ORDER_TIMESTAMP can be a candidate. Additional selected columns might permit index-only access. However, a wider index costs disk, logging, buffer space, INSERT and UPDATE processing, COPY, RECOVER, REORG, and RUNSTATS resources.

When no access path naturally supplies the required order or uniqueness, Db2 can use the work file database for intermediate data. Work-file tuning and capacity matter, but adding capacity does not excuse wasteful SQL. First remove semantics-free operations, reduce rows early with correct predicates, project only needed columns, maintain representative statistics, and then size subsystem resources for legitimate workloads.

Verify with EXPLAIN

Explain both the original and proposed SQL under comparable conditions. PLAN_TABLE has indicators that describe sorts for ordering, grouping, unique elimination, joins, and composite table processing. Column names and interpretation depend on the installed Db2 version and PLAN_TABLE format, so use the IBM documentation and tooling for that level. Also inspect access type, selected index, matching columns, join method, join sequence, index-only access, estimated rows, and cost.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
EXPLAIN PLAN SET QUERYNO = 9401 FOR
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME
  FROM CUSTOMER AS C
 WHERE EXISTS (
       SELECT 1
         FROM SALES_ORDER AS O
        WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
          AND O.ORDER_STATUS = 'OPEN'
       );
 
SELECT QUERYNO,
       PLANNO,
       METHOD,
       ACCESSTYPE,
       MATCHCOLS,
       ACCESSNAME,
       INDEXONLY,
       SORTN_UNIQ,
       SORTN_ORDERBY,
       SORTN_GROUPBY,
       SORTN_JOIN
  FROM PLAN_TABLE
 WHERE QUERYNO = 9401
 ORDER BY PLANNO;

An N value in a sort indicator does not prove the statement is fast, and a Y value does not prove it is wrong. A required sort over 500 rows can be harmless; an index access that reads millions of poorly clustered rows can be expensive. EXPLAIN is a model of the chosen path, not a replacement for measurement.

Measure after EXPLAIN

• Confirm that original and rewritten statements return the same required result.
• Compare rows read and returned, getpages, CPU time, elapsed time, and synchronous I/O.
• Review work-file usage and sort-related monitoring data during representative concurrency.
• Check statement frequency; a modest saving on a high-volume query can matter greatly.
• Recheck after RUNSTATS, schema changes, Db2 maintenance, or access-path changes.

A safe tuning workflow

• Define the grain and logical key of one output row.
• Mark each DISTINCT, ORDER BY, GROUP BY, UNION, and window ordering requirement.
• Expose joined table keys to diagnose why duplicate-looking rows occur.
• Repair incomplete joins and model constraints before suppressing symptoms.
• Use EXISTS for existence, aggregation for summaries, and deterministic ranking for one chosen row.
• Evaluate indexes that support filtering and order, including their write and utility costs.
• EXPLAIN both forms, test edge cases, and measure under realistic data and concurrency.

Explain It Like I'm Five

Imagine dumping a box of mixed cards onto the floor. DISTINCT says, “Put matching cards together and keep only one of each.” ORDER BY says, “Arrange every card from smallest to largest.” Those jobs take time and space. If someone accidentally copied every card four times, removing copies makes the pile look right, but fixing the copying machine is better. Sometimes you truly need one card of each kind or a sorted deck. Then the work is correct. EXPLAIN tells you how Db2 plans to organize the cards.

Exercises

• Rewrite a DISTINCT customer query over CUSTOMER and PAYMENT when the requirement is simply “customers with at least one late payment.” Use EXISTS and explain the result grain.
• Create a diagnostic SELECT list that exposes keys from a three-table join producing duplicate-looking account rows. Identify the expected relationship at each join.
• Compare SELECT DISTINCT REGION_CODE, PRODUCT_CODE with the equivalent GROUP BY form. EXPLAIN both and record whether their access paths differ.
• Review a UNION and decide whether UNION ALL preserves correctness. Document how you proved that the inputs are disjoint or that duplicates are acceptable.
• Design an index candidate for “latest 20 events for one device.” Include a stable tie-breaker and list the index's write and utility costs.
• Find an ORDER BY in application SQL and trace the consumer. Decide whether ordering is required, repeated downstream, or unused, then verify any change with tests.