On 8/2/11 8:34 AM, Eugene Prokopiev wrote:
Hi,
I have table with 81 887 010 rows. Select count from this table works fine, but I can't wait for the results of select count(distinct ...) from this table. Why can it be? Distinct requires the table to be sorted to find them.
sql>explain select count(src) from transfer_ip; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal
| +==================================================================================================================================================================================+ | function user.s1_1{autoCommit=true}():void;
| | barrier _55 := language.dataflow();
| | _2 := sql.mvc();
| | _39:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,0,2);
| | _43 := algebra.selectNotNil(_39);
| | _35:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,0,2);
| | _45 := algebra.kdifference(_43,_35);
| | _41 := algebra.selectNotNil(_35);
| | _47 := algebra.kunion(_45,_41);
| | _6:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","transfer_ip",1);
| | _9 := bat.reverse(_6);
| | _49 := algebra.kdifference(_47,_9);
| | _51 := aggr.count(_49);
| | _40:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,1,2);
| | _44 := algebra.selectNotNil(_40);
| | _38:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,1,2);
| | _46 := algebra.kdifference(_44,_38);
| | _42 := algebra.selectNotNil(_38);
| | _48 := algebra.kunion(_46,_42);
| | _52 := aggr.count(_48);
| | _10:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",1);
| | _11 := algebra.selectNotNil(_10);
| | _53 := aggr.count(_11);
| | _50 := mat.pack(_51,_52,_53);
| | _54 := algebra.selectNotNil(_50);
| | _14 := aggr.sum(_54);
| | exit _55;
| | sql.exportValue(1,"sys.transfer_ip","L4","wrd",64,0,6,_14,"");
| | end s1_1;
| +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 29 tuples (12.925ms) sql>explain select count(distinct src) from transfer_ip; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal
| +==================================================================================================================================================================================+ | function user.s2_1{autoCommit=true}():void;
| | barrier _56 := language.dataflow();
| | _2 := sql.mvc();
| | _42:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,0,2);
| | _46 := algebra.selectNotNil(_42);
| | _38:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,0,2);
| | _48 := algebra.kdifference(_46,_38);
| | _44 := algebra.selectNotNil(_38);
| | _50 := algebra.kunion(_48,_44);
| | _6:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","transfer_ip",1);
| | _9 := bat.reverse(_6);
| | _52 := algebra.kdifference(_50,_9);
| | _53 := bat.reverse(_52);
| | _43:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,1,2);
| | _47 := algebra.selectNotNil(_43);
| | _41:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,1,2);
| | _49 := algebra.kdifference(_47,_41);
| | _45 := algebra.selectNotNil(_41);
| | _51 := algebra.kunion(_49,_45);
| | _54 := bat.reverse(_51);
| | _10:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",1);
| | _11 := algebra.selectNotNil(_10);
| | _55 := bat.reverse(_11);
| | _14 := mat.pack(_53,_54,_55);
| | _15 := algebra.kunique(_14);
| | _16 := bat.reverse(_15);
| | _17 := aggr.count(_16);
| | exit _56;
| | sql.exportValue(1,"sys.transfer_ip","L5","wrd",64,0,6,_17,"");
| | end s2_1;
| +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 30 tuples (1.238ms)