Home » RDBMS Server » Performance Tuning » Tune query for Sum function
Tune query for Sum function [message #647591] Wed, 03 February 2016 11:13 Go to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
Hi,

Is there a way to get the sum of column faster where my table is having around 10 billion records

select sum(col_1) from t1;
Re: Tune query for Sum function [message #647592 is a reply to message #647591] Wed, 03 February 2016 11:15 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
You could declare it NOT NULL and index it.

And btw, I wish you would not say "records" when you mean "rows".
Re: Tune query for Sum function [message #647593 is a reply to message #647592] Wed, 03 February 2016 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65955
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... or you could buy an Exadata. Smile

Re: Tune query for Sum function [message #647594 is a reply to message #647592] Wed, 03 February 2016 11:19 Go to previous messageGo to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
Is it possible to check without index as i am not having index in this table by using any hint

[Updated on: Wed, 03 February 2016 11:20]

Report message to a moderator

Re: Tune query for Sum function [message #647595 is a reply to message #647594] Wed, 03 February 2016 11:22 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
You could compress the table.

Or you could license the in-memory option and load the table into in-memory column store (marginally cheaper than Exadata)
Re: Tune query for Sum function [message #647596 is a reply to message #647593] Wed, 03 February 2016 11:22 Go to previous messageGo to next message
gazzag
Messages: 1034
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:

... or you could buy an Exadata.

Laughing I tried persuading the last company I worked for to do this. Needless to say they decided it was too expensive. So I spent the next few months trying to optimise their queries Sad
Re: Tune query for Sum function [message #647597 is a reply to message #647596] Wed, 03 February 2016 11:24 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
I've been looking at a project for positioning a another company's hardware against Exadata. With release 12.x, some Exadata-like capabilities are becoming available on non-engineered systems. The in-memory option is a critical part of this.
Re: Tune query for Sum function [message #647598 is a reply to message #647597] Wed, 03 February 2016 19:48 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
We all have to assume your "sample test case" is a realistic representation of your problem (one column being summed, no predicates in the query). Oh well.

Let us recap so far:

1. create an index on the column so that you have a very small "duplicate" object to be scanned instead of the actual full table.

2. put it on EXADATA and let SMARTSCAN do its magic of eliminating physical IO, doing parallel physical IO, and reducing network traffic.

3. pre-load it in memory and skip all the physical IO.

4. use compression to reduce the size of the table and hope the savings in IO outweighs the increased CPU needed for decompression (usually works).

------ and I add --------

5. use partitioning and parallel query to divide the task up into many pieces that multiple processes can do at the same time.

6. use a materialized view or other pre-aggregation strategies to pre-sum results for strategically reusable subsets of the rows.

7. redesign your solution so that you don't need a 10 billion row table or (I guess here) only one wide table with 100s of unrelated metrics on a single row.

8. explore your problem space to see if the SUM is really your problem or if it is something else (chained rows, joins to other tables, bug needing a patch (who knows?)).

Hmm... not a bad summary I think. You can always combine many of these options as well though in most cases, one of them will turn out to be the big winner.

[Updated on: Wed, 03 February 2016 19:51]

Report message to a moderator

Previous Topic: Wich index is used. Confusion in SQL-trace
Next Topic: Oracle SQL performance analysis
Goto Forum:
  


Current Time: Wed Nov 14 07:06:21 CST 2018