Igor Tandetnik,
The explain query plan for select FieldName, min(rowid) from
BlobLastNameTest group by FieldName shows a full index scan, even after I run
ANALYZE TABLE AND INDEX ON FIELDNAME, which could be problematic when the
number of rows in BlobLastNameTest increases by a factor 10 to 30 million rows.
But since Sqlite does not have a DISTINCT ON clause as Florian Weimer as
pointed out two days ago,we will have to investigate this issue in another
possible thread.
Here is how I might do the update:
1. CREATE TABLE FOO(FIELDNAME CHAR(25), IDROW INT);
2 INSERT INTO FOO select FieldName, min(rowid) from BlobLastNameTest
group by FieldName
3. INSERT OR REPLACE INTO BLOBLASTNAMETEST SELECT t1.FIELDNAME,
UDF(t1.ROWID,t1.FIELDNAME,this,'BLOBLASTNAMETEST') FROM FOO WHERE
BLOBLASTNAMETEST.FIELDNAME = FOO.FIELDNAME AND BLOBLASTNAMETEST.ROWID =
FOO.IDROW.
WHERE THE UDF looks like this:
void cIntersectingGroupCache::UDFFunc(sqlite3_context *context, int argc,
sqlite3_value **argv){
char* TableName;
int size;
int* ip2;
long long int iVal;
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column.clear();
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
iVal = sqlite3_value_int64(argv[0]);
iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
sqlite3_result_int64(context, iVal);
break;
}
case SQLITE_NULL: {
size = sqlite3_value_bytes(argv[3]);
TableName = new char[size + 1];
memcpy(TableName, sqlite3_value_text(argv[3]),size);
TableName[size] = '\x0';
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->RowCountMap[TableName]
+= 1;
delete [] TableName;
break;
}
case SQLITE_BLOB: {
size = sqlite3_value_bytes(argv[0]);
ip2 = (int *)sqlite3_value_blob(argv[0]);
for (int i = 0; i < size/sizeof(int); i++){
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column.push_back(ip2[i]);
}
break;
}
default: {
break;
}
}
switch( sqlite3_value_type(argv[1]) ){
case SQLITE_INTEGER: {
int iVal = sqlite3_value_int(argv[1]);
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column.push_back(iVal);
char* blob =
reinterpret_cast<char*>(&(((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column[0]));
sqlite3_result_blob(context,blob,((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column.size()*sizeof(int),NULL);
break;
}
default: {
break;
}
}
}
Thank you for your help.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
