'sum(x1+x2+..)' OR 'sum(x1) + sum(x2)+ ..' ????

[Benouche]

which one give the best performance ??

thanks in advance,

Benouche

 

[karluk]

My guess is that there would be very little difference in performance between the two expressions, unless the columns x1, x2, etc. contain more than one datatype. If that's the case, you would lose performance by needing to do implicit datatype conversions. The result would be superior performance by 'sum(x1) + sum(x2)+ ...', which needs to do the conversion only once after computing all the row sums. 'Sum(x1+x2+..)', on the other hand, would need to do type conversions for every row being processed.

 

[barbarul2001]

You perform mathematical operations on the indexed(i think) column(s), so in both situations you will have table access full, so both are bed or equally good senior rdbms specialist

 

[fmorel]

I think that this has nothing to deal with the indexes.
Indexes cannot be used if there is a function on a column in a where clause, but that is not the case.

I think the 2 queries are almost the same.
Suppose that you want to sum i columns,
there are n rows in your table

if you do a sum(x1+x2+..+xi) you make (i)*n sums.
if you do a sum(x1)+sum(x2)+.. sum(xi) you make
n sums for x1, n sums for x2 ... thats i sums.
So , with the final sum, you made n*(i-1)+1 sums.

So the difference is (1-n) sums which does not make a big difference for a computer than can compute millions of operations in a second.

But, as said Karluk, there can be a difference if computer has to make more operations (like for type conversion) than only sums.

Regards

 

[barbarul2001]

fmorel you're right. I got carried away and I said some nonsenses. Ok this had nothing to do with indexes, what you wrote is a variation of what my and garluk's stuff . I have no comment for that. The presence of an index on a column does not guarantee it will be used. The following is a small list of factors that will prevent an index from being used:

The optimizer decides it would be more efficient not to use the index. As a rough rule of thumb, on evenly distributed data an index will be used if it restricts the number rows returned to 5% or less of the total number of rows. In the case of randomly distributed data, an index will be used if it restricts the number of rows returned to 25% or less of the total number of rows.
You perform a function on the indexed column i.e. WHERE Upper(name) = 'JONES'
You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001
You concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
You do not include the first column of a concatenated index in the WHERE clause of your statement. For the index to be used in a partial match, the first column (leading-edge) must be used.
It is debatable whether the order of columns referencing the index is important. There are sometimes differences in the performance of the statement depending on column order, especially when using Rule Based Optimization (RBO).
The use of 'OR' statements confuses the Cost Based Optimizer (CBO). It will rarely choose to use an index on column referenced using an OR statement. It will even ignore optimizer hints in this situation. The only way of guaranteeing the use of indexes in these situations is to use the /*+ RULE */ hint.


but this is another story.
senior rdbms specialist

 

[r937]

the /*+RULE*/ hint, whatever that is, is probably not ansi sql

otherwise, barbarul, your comments are very worthwhile, and will apply in many database systems

rudy