低Cost的SQL查询反而更慢？嵌套查询为何大幅提升Cost？

Hey there, let's break down your two key questions and unpack what's happening with your SQL execution plans:

1. Why is the query with the lowest Cost the slowest to run? #

First, a critical point to remember: the optimizer's Cost is an estimate based on statistics, not a direct measure of actual runtime. It's calculated using projected IO and CPU overhead, but real-world performance depends on factors the optimizer can't always account for:

• Data Caching: Your first query (highest Cost, fastest runtime) likely benefited from cached data in the database buffer pool. The full table scan on ORDERS might have hit warm data that was already in memory, so actual IO was minimal. The later queries, by contrast, might have been accessing cold data that needed to be read from disk, adding significant latency.
• Estimation vs. Reality: Look at the execution plan for your third query: the optimizer estimates only 547 rows from ORDERS, but if the actual number of matching rows is way higher, the nested loops join will run far more times than expected, blowing up runtime. In the first query, the estimate is 113 rows—if that's closer to reality, the nested loops are much cheaper to execute.
• Index Overhead: The third query uses an INDEX SKIP SCAN on BATCH_PK_IDX. While this has a low estimated Cost, skip scans can be surprisingly slow on high-cardinality indexes, especially when the skipped column (PARTITION_TYPE) isn't a prefix of the index. Compare that to the first query's INDEX UNIQUE SCAN on BATCH—that's one of the fastest index access methods available.

2. Why does the nested query for partition_key jack up the Cost so much? #

That tiny subquery (SELECT actual_partition_key FROM MYTABLE.CALENDAR WHERE is_active = 1) has a big impact on Cost estimation, even though it returns just one row:

• Partition Scanning Logic: When using a subquery to get the PARTITION_KEY, the optimizer can't resolve the value at plan time. It has to assume the subquery could return a value that matches multiple partitions, so it estimates the cost of scanning all partitions of ORDERS (via PARTITION RANGE ITERATOR + PARTITION HASH ALL). This full-table-scan-sized Cost gets added to the total, hence the 20M+ Cost number.
• Fixed Value Optimization: When you replace the subquery with a hardcoded 123, the optimizer knows exactly which partition to target. It can estimate the cost of scanning only that single partition, so the Cost drops dramatically. But again, this estimate doesn't always reflect reality—if that specific partition is huge, the actual runtime can be higher than the low Cost suggests.

The kicker? Even though the first query has a massive estimated Cost, during execution the subquery runs first, returns the actual partition key, and ORDERS only scans that one partition. The optimizer just can't account for that "late binding" in its initial Cost calculation, so the estimate is way off from the real runtime.

Quick Recommendations #

1. Refresh Statistics: Outdated table/index stats are a common cause of bad execution plans. Make sure ORDERS, BATCH, and CALENDAR have up-to-date statistics.
2. Rewrite the Subquery: Try using a WITH clause for the calendar lookup to help the optimizer see it's a single-row result:

WITH active_partition AS (
    SELECT actual_partition_key FROM MYTABLE.CALENDAR WHERE is_active = 1
)
SELECT CASE WHEN COUNT(*) > 0 THEN 0 ELSE 1 END AS result 
FROM MYTABLE.ORDERS O 
JOIN active_partition AP ON O.PARTITION_KEY = AP.actual_partition_key
INNER JOIN MYTABLE.BATCH B 
    ON B.BATCH_ID = O.IN_BATCH_ID 
    AND B.PARTITION_KEY = O.PARTITION_KEY 
    AND B.PARTITION_TYPE = O.PARTITION_TYPE 
    AND B.INSTANCE_NUMBER = O.INSTANCE_NUMBER 
WHERE O.partition_type = 3 
AND B.START_TIME BETWEEN SYSDATE - 1/48 AND SYSDATE - 10/1440
AND O.STATE NOT IN ('993890', '999990') 
AND O.RECEIVER IN (
    SELECT ra.RECEIVER_ID 
    FROM MYTABLE.RECEIVER_AVAILABILITY ra
    JOIN (
        SELECT RECEIVER_ID, MAX(CREATION_DATE) AS max_creation
        FROM MYTABLE.RECEIVER_AVAILABILITY 
        GROUP BY RECEIVER_ID
    ) ra_max ON ra.RECEIVER_ID = ra_max.RECEIVER_ID 
        AND ra.CREATION_DATE = ra_max.max_creation
    WHERE ra.state IN (1, 2) 
    AND ra.creation_date < SYSDATE - 30/1440
)

3. Tune the Receiver Subquery: Create a composite index on RECEIVER_AVAILABILITY (RECEIVER_ID, CREATION_DATE, STATE) to speed up the grouping and filtering.

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