-
Neeraj Bisht authored
WITH COMPOSITE KEY COLUMNS Problem:- While running a SELECT query with several AGGR(DISTINCT) function and these are referring to different field of same composite key, Returned incorrect value. Analysis:- In a table, where we have composite key like (a,b,c) and when we give a query like select COUNT(DISTINCT b), SUM(DISTINCT a) from .... here, we first make a list of items in Aggr(distinct) function (which is a, b), where order of item doesn't matter. and then we see, whether we have a composite key where the prefix of index columns matches the items of the aggregation function. (in this case we have a,b,c). if yes, so we can use loose index scan and we need not perform duplicate removal to distinct in our aggregate function. In our table, we traverse column marked with <-- and get the result as (a,b,c) count(distinct b) sum(distinct a) treated as count b treated as sum(a) (1,1,2)<-- 1 1 (1,2,2)<-- 1++=2 1+1=2 (1,2,3) (2,1,2)<-- 2++=3 1+1+2=4 (2,2,2)<-- 3++=4 1+1+2+2=6 (2,2,3) result will be 4,6, but it should be (2,3) As in this case, our assumption is incorrect. If we have query like select count(distinct a,b), sum(distinct a,b)from .. then we can use loose index scan Solution:- In our query, when we have more then one aggr(distinct) function then they should refer to same fields like select count(distinct a,b), sum(distinct a,b) from .. -->we can use loose scan index as both aggr(distinct) refer to same fields a,b. If they are referring to different field like select count(distinct a), sum(distinct b) from .. -->will not use loose scan index as both aggr(distinct) refer to different fields.
35a3f9d7