Home » SQL & PL/SQL » SQL & PL/SQL » Low performance with group by
Low performance with group by [message #200123] Sat, 28 October 2006 05:09 Go to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
I have an sql query like this:

select table1.column1, sum(table1.column2), sum(table1.column3), sum(table1.column4), sum(table1.column5)
from table1, table2
where table1.id=table2.table1_id
and table2.column1=?
and table1.column6=?
and table1.column7=?
and table1.column8=?
and table1.column9=?
and table1.column10=?
and table1.column11=?
and table1.column12=?
and table1.column13=?
and table1.column14=?
and table1.column15=?
and table1.column16=?
group by table1.column1

This query takes about ten seconds to execute.
table1 has about 500 000 rows, table2 about 2000.
Is there a way to speed this up? Can I add an index to all the columns, both on table1 and table2?
Re: Low performance with group by [message #200125 is a reply to message #200123] Sat, 28 October 2006 06:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If the combination of all those WHERE clauses on table1 reduces the rows returned to 5000 or less, then an index on column6,
column7, column8, column9, column10, column11, column12, column13, column14, column15, column16
will help.

Assuming that each row in table1 joins to a small number (hopefully 1) of rows in table2, and index on table2.table1_id will also help.

After you create the index, gather statistics on both table with DBMS_STATS.GATHER_TABLE_STATS and try again.

Ross Leishman
Re: Low performance with group by [message #200146 is a reply to message #200125] Sat, 28 October 2006 10:54 Go to previous messageGo to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
Thanks for your quick reaply. I will try that.
Re: Low performance with group by [message #200148 is a reply to message #200123] Sat, 28 October 2006 11:10 Go to previous message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
Thank you, your answer really helped! I had included table1.column1 in the index, it seems that if I remove it the query executes a whole lot faster.
Previous Topic: how to avoid repetitive data from multiples data
Next Topic: Bulk Binding -FORALL
Goto Forum:
  


Current Time: Sun Dec 04 18:37:42 CST 2016

Total time taken to generate the page: 0.07749 seconds