oracle query optimization issue

Question

Is there any way to optimize the query below?

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

it's taking like hours in execution. Function "sum" is slowing it down but i don't know how to fix it. Any help would be appreciated. thanks in advance.

Not related, but you can use substr(CELLNAME,5,1) = 'B' instead of CELLNAME like '_____B%' please adjust the position in substr according to requirement — Popeye
– Popeye, Commented Oct 19, 2019 at 11:30
yes you are right but "like" is not affecting the speed of query but "SUM" is. even if i comment CELLNAME like '_____B%' condition it's taking hours. so issue is with "SUM" — Dharmendra
– Dharmendra, Commented Oct 19, 2019 at 15:48
@Dharmendra It is unlikely that SUM is responsible for performance issues. The vast majority of performance issues are caused by retrieving data and joining it. The time spent in small functions like SUM is usually irrelevant. How large is the table UCELLCAP1? Do any of the predicates filter out a large percentage of the data? If so, they might be a good candidate for an index. — Jon Heller
– Jon Heller, Commented Oct 19, 2019 at 17:30
The expression suggested by @Tejash could also be used to create an index that might help performance (which is not the case with the LIKE condition) — user330315
– user330315, Commented Oct 28, 2019 at 9:53

Harshal Khachane · Accepted Answer · 2019-10-19 10:59:46Z

1

There could not be a concrete answer by just looking at query.

you should chech execution plan of query, which shows how database will be executing the query and where most of the time is spent.
then based on results, you may want to have indexes on columns used in "group by" or have a partitions etc

General observations-

Even if you have index on CELL_NAME column, there is no benefit as you are using like predicate where start is not constant. If its something like (a like "ab*") then index would be helpful.
Add index on group by columns.

edited Oct 19, 2019 at 10:59

answered Oct 19, 2019 at 10:46

Harshal Khachane

2662 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dharmendra Over a year ago

hi thanks for the answer but as i tried to check group by is not creating issue here. it's "SUM" which is slowing it down. is there anything else i can do for "SUM"??

BobC Over a year ago

You need to determine where time is being spent. As has been said earlier, SUM() is unlikely to be the culprit, unless you can provide diagnostics to show otherwise. I would suggest getting a SQL Monitor report.

Harshal Khachane Over a year ago

Try removing all the sum functions from select then and see what's the time. Next Instead of sum just use count function.

Collectives™ on Stack Overflow

oracle query optimization issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related