Oracle查询优化求助：含SUM聚合的慢查询优化方案咨询

Hey there, let's dig into why your query is dragging on for hours and fix it—first off, the SUM functions aren't the bottleneck here. Aggregations like SUM are cheap once the database has the right data to work with. The real issue is almost always how the database retrieves and filters the data before doing those sums. Here are concrete steps to speed things up:

1. Add a Covering Composite Index #

Your query filters on DATETIME and CELLNAME, groups by RNC and CELLNAME, and sums four specific columns. A covering index will let Oracle grab all needed data directly from the index without hitting the main table (no full table scan or costly rowid lookups), which is way faster.

Create this index:

CREATE INDEX IDX_UCELLCAP1_DT_CELL_RNC_COVER 
ON ZTE_UTRAN.UCELLCAP1 (DATETIME, CELLNAME, RNC)
INCLUDE (C310484605, C310484607, C310484609, C310484611);

• The leading columns (DATETIME, CELLNAME) match your WHERE clause filters, so Oracle can quickly narrow down to the rows you need.
• Including the SUM columns means the index has all data required for the aggregation—no need to jump back to the main table.

2. Optimize the CELLNAME Filter #

Your current CELLNAME LIKE '_____B%' uses 5 wildcards followed by B%. While Oracle can use index prefix matching for LIKE 'X%', the leading wildcards (_____) mean it can't leverage an index on CELLNAME for that part of the filter. If your CELLNAME has a consistent structure (e.g., 5 characters followed by B), rewrite the filter to something index-friendly:

-- If CELLNAME is fixed-length and the 6th character is 'B'
SUBSTR(CELLNAME, 6, 1) = 'B'
-- OR define a range (adjust values based on your actual CELLNAME format)
CELLNAME >= 'AAAAAB' AND CELLNAME < 'AAAAAC'

Test both options to see which plays nicer with your index.

3. Refresh Table Statistics #

Oracle's optimizer relies on up-to-date statistics to choose the best execution plan. If stats are outdated, it might pick a slow full table scan even if indexes exist. Refresh stats for the table:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => 'ZTE_UTRAN',
    TABNAME => 'UCELLCAP1',
    CASCADE => TRUE
);

4. Check for Partitioning (or Add It) #

If UCELLCAP1 isn't partitioned by DATETIME, consider range partitioning on that column. Your query only looks at the last 7 days, so partitioning would let Oracle scan only the relevant partitions instead of the entire table—this can cut runtime drastically for large datasets.

5. Analyze the Execution Plan #

Before making any changes, run an explain plan to see exactly what Oracle is doing:

EXPLAIN PLAN FOR
SELECT RNC, CELLNAME, SUM(C310484605) AS DL_PWR_FAIL, SUM(C310484607) AS UL_PWR_FAIL, SUM(C310484609) AS HSDPA_FAIL, SUM(C310484611) AS HSUPA_FAIL 
FROM ZTE_UTRAN.UCELLCAP1 
WHERE CELLNAME LIKE '_____B%' AND DATETIME >=TRUNC(SYSDATE)-7 
GROUP BY RNC, CELLNAME 
ORDER BY HSDPA_FAIL DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Look for key clues:

• TABLE ACCESS FULL: Means Oracle is scanning the entire table—this is bad for large datasets. Your covering index should eliminate this.
• INDEX RANGE SCAN: A good sign the index is being used to filter rows efficiently.
• SORT GROUP BY: If this shows up, check if your index can avoid the sort (the covering index we suggested is ordered to support grouping without extra sorting).

6. Minor Tweaks #

• Ensure DATETIME is a DATE or TIMESTAMP type—implicit conversions (e.g., storing dates as strings) will kill performance.
• If the optimizer still isn't picking the right index, use hints like /*+ INDEX(ZTE_UTRAN.UCELLCAP1 IDX_UCELLCAP1_DT_CELL_RNC_COVER) */—but only as a last resort, since fixing stats/indexes is a more sustainable solution.

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