Sql query with group by takes too long

Question

I have a very simple query but it takes too long to load when I use Max and group by. Could you please propose an alternative?. I use Oracle 18g for running this query. a_num_ver, id, site_id is a primary key.

SELECT id
       , site_id
       , sub_id
       , max(a_num_ver) as a_num_ver
       , ae_no
       , max(aer_ver) AS aer_ver
FROM table_1
GROUP BY id
         , site_id
         , sub_id
         , ae_no

thanks, thats a good way to go but I cannot create index since I am just consuming this table. The index has to be set in oracle table which I am not allowed at the moment. Please propose another way?. — KSp, Aug 26 '21 at 10:20
Since my columns are already primary keys, should I index them again? — KSp, Aug 26 '21 at 10:28
You may not have a whole lot of options. If you edit your question to include the indexes that exist, the query plan, execution statistics, and information about the table (i.e. are you aggregating 100,000 rows? 1 million rows? 1 billion rows?) we may be able to help. But if you don't have the right indexes and can't cause the right indexes to be created, what you perceive as "too long" may simply be how long it's going to take Oracle to produce the result you want. — Justin Cave, Aug 26 '21 at 10:31
As @JustinCave points out, we need a lot more detail to help with performance questions. Please read [this handy guide to asking Oracle tuning questions on SO](https://stackoverflow.com/a/34975420/146325) — APC, Aug 26 '21 at 11:19
I would added: 1. execute `alter session set statistics_level=all;` 2. run your query and fetch all results; 3. run this query `select * from table(dbms_xplan.display_cursor('','','allstats last'));` and show its results — Sayan Malakshinov, Aug 26 '21 at 11:52
Arrgh! Database Administrators considered harmful! @GordonLinoff has it right: you need an index to support this query. If creating the index is not an option, your alternative is to tell your users that this is slow because your DBA is uncooperative. In most Oracle shops, they've heard that before. Several times. You might also try asking on https://dba.stackexchange.com/ Be sure to mention that you're not permitted to create an index; maybe somebody there has a workaround of some kind. — O. Jones, Aug 26 '21 at 14:01
WOA! Before running off and belittling the uncooperative DBA perhaps you need to talk to the DBA. Most, and any good one, will have no problem creating that index. And maybe even looking deeper into it than the SO community can possible get. Further you have not defined *too slow*. Do you have written Service Level Agreements (SLAs) in place, - if not why? Does the query satisfy those SLAs? If not it is a very strong business case. — Belayer, Aug 31 '21 at 03:05

Siddhi · Answer 1 · 2021-08-29T08:32:23.573

Try using parallel hints 4 OR 8 if that is allowed from DBA. I have tried a similar query in a table with around 296,292,720 rows. Without hints, it took around 2 minutes to execute. It comes down to 20 seconds with PARALLEL 8.

SELECT /*+ PARALLEL(8) */ 
id
       , site_id
       , sub_id
       , max(a_num_ver) as a_num_ver
       , ae_no
       , max(aer_ver) AS aer_ver
FROM table_1
GROUP BY id
         , site_id
         , sub_id
         , ae_no

Sql query with group by takes too long

1 Answers1