使用主键关联的SQL语句对JOIN表执行全表扫描的原因及优化方法咨询

Great question—let's break down what's happening here and walk through actionable fixes to speed up your query.

First, let's recap your query and execution plan for clarity:

Your Query #

SELECT max(pd.cre_dt) 
FROM D00ZVZ01.ZVZ_PRINT_DOCUMENT pd 
JOIN D00ZVZ01.ZVZ_BRIEF_REGISTRATIE br 
  ON pd.PRINT_DOCUMENT_ID = br.PRINT_DOCUMENT_ID 
  AND br.BRIEF_REG_GROEP_ID IN (2217, 2237, 2257);

Execution Plan #

---------------------------------------------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | 
---------------------------------------------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 24 | | 283K (2)| 00:00:15 | 
| 1 | SORT AGGREGATE | | 1 | 24 | | | | 
|* 2 | HASH JOIN | | 677K| 15M| 14M| 283K (2)| 00:00:15 | 
| 3 | INLIST ITERATOR | | | | | | | 
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| ZVZ_BRIEF_REGISTRATIE | 694K| 6779K| | 17430 (1)| 00:00:01 | 
|* 5 | INDEX RANGE SCAN | ZVZ_BRIEF_REGISTRATIE_IF4 | 694K| | | 1469 (2)| 00:00:01 | 
| 6 | TABLE ACCESS FULL | ZVZ_PRINT_DOCUMENT | 9567K| 127M| | 260K (1)| 00:00:14 | 
---------------------------------------------------------------------------------------------------------------------------- 

Why the Full Table Scan is Happening #

Looking at the plan, the optimizer chose a HASH JOIN strategy:

1. It first pulls 694k rows from ZVZ_BRIEF_REGISTRATIE using the index ZVZ_BRIEF_REGISTRATIE_IF4 (for the IN clause on BRIEF_REG_GROEP_ID).
2. It builds a hash table from those rows.
3. It then scans the entire ZVZ_PRINT_DOCUMENT table (9.5M rows) to probe the hash table for matches.

The primary key index on pd.PRINT_DOCUMENT_ID isn't being used here for a few likely reasons:

• Outdated statistics: If the database doesn't have accurate stats on how many rows match your IN clause, it might miscalculate that doing millions of index lookups (via nested loops) is more expensive than a full table scan + hash join.
• Hash join doesn't use indexes for probing: By design, hash joins scan the entire probe-side table (here, ZVZ_PRINT_DOCUMENT) instead of using indexes.
• Missing covering index: Even if nested loops were used, fetching cre_dt from the table after each index lookup might be costly enough that the optimizer prefers a full scan.

Optimization Strategies #

Let's go through practical fixes to speed this up:

1. Update Table Statistics First #

Outdated stats are one of the most common causes of bad execution plans. Refresh stats for both tables to give the optimizer accurate data to work with:

-- For Oracle (adjust syntax if you're using a different DB)
EXEC DBMS_STATS.GATHER_TABLE_STATS('D00ZVZ01', 'ZVZ_PRINT_DOCUMENT');
EXEC DBMS_STATS.GATHER_TABLE_STATS('D00ZVZ01', 'ZVZ_BRIEF_REGISTRATIE');

This alone might make the optimizer switch to a more efficient join strategy.

2. Hint for Nested Loops (If Appropriate) #

If the number of matching rows from ZVZ_BRIEF_REGISTRATIE is actually smaller than the stats suggest, you can hint the optimizer to use nested loops, which will leverage the primary key index on ZVZ_PRINT_DOCUMENT:

SELECT /*+ USE_NL(pd br) */ max(pd.cre_dt) 
FROM D00ZVZ01.ZVZ_PRINT_DOCUMENT pd 
JOIN D00ZVZ01.ZVZ_BRIEF_REGISTRATIE br 
  ON pd.PRINT_DOCUMENT_ID = br.PRINT_DOCUMENT_ID 
  AND br.BRIEF_REG_GROEP_ID IN (2217, 2237, 2257);

Or reorder the join to prioritize the smaller table first:

SELECT /*+ LEADING(br pd) USE_NL(pd) */ max(pd.cre_dt) 
FROM D00ZVZ01.ZVZ_BRIEF_REGISTRATIE br
JOIN D00ZVZ01.ZVZ_PRINT_DOCUMENT pd 
  ON pd.PRINT_DOCUMENT_ID = br.PRINT_DOCUMENT_ID 
WHERE br.BRIEF_REG_GROEP_ID IN (2217, 2237, 2257);

3. Create a Covering Index #

If you frequently run queries that need cre_dt for specific PRINT_DOCUMENT_IDs, create an index that includes both columns. This lets the database get all needed data directly from the index, avoiding table lookups:

-- For Oracle (includes both columns in the index)
CREATE INDEX ZVZ_PRINT_DOCUMENT_IDX1 ON D00ZVZ01.ZVZ_PRINT_DOCUMENT (PRINT_DOCUMENT_ID, cre_dt);

-- For SQL Server/PostgreSQL (INCLUDE clause to avoid making cre_dt part of the index key)
CREATE INDEX ZVZ_PRINT_DOCUMENT_IDX1 ON D00ZVZ01.ZVZ_PRINT_DOCUMENT (PRINT_DOCUMENT_ID) INCLUDE (cre_dt);

This makes index lookups far cheaper, so the optimizer will be much more likely to choose nested loops over a full scan.

4. Rewrite the Query to Filter Early #

Another approach is to first get all relevant PRINT_DOCUMENT_IDs from ZVZ_BRIEF_REGISTRATIE, then find the max cre_dt only for those IDs:

SELECT max(pd.cre_dt)
FROM D00ZVZ01.ZVZ_PRINT_DOCUMENT pd
WHERE pd.PRINT_DOCUMENT_ID IN (
  SELECT br.PRINT_DOCUMENT_ID
  FROM D00ZVZ01.ZVZ_BRIEF_REGISTRATIE br
  WHERE br.BRIEF_REG_GROEP_ID IN (2217, 2237, 2257)
);

This structure often prompts the optimizer to use the primary key index to look up only the matching rows instead of scanning the entire table.

内容的提问来源于stack exchange，提问作者Trace