but why is the resulting table read only, meaning objects cannot be cursor selected? Probably obvious..
It's not read-only; but as you say, rows can't be selected, because there is no BTREE index. There is a BTREENULL index, on [ADDRESS_DETAIL_PID]. That index was added automatically. Why was it not a BTREE? Because in the source table, there is no BTREE index on the source field [ADDRESS_DETAIL_PID], or any other guarantee that the field would contain no NULL value(s). It had to assume that NULLs were posssible. (If there were, there would be one group with NULL in that field.) It did know in advance that there would be no duplicate values in [ADDRESS_DETAIL_PID], because we grouped by that field. Otherwise it have had to make a BTREEDUPNULL instead. Too much detail probably. Anyway, two easy ways to fix it: If you happen to know that values in some field are unique, you can add a BTREE index on that field. Here, values in [ADDRESS_DETAIL_PID] are unique, so that will do fine. ALTER TABLE [Tims result Table] ( ADD INDEX [ADDRESS_DETAIL_PID_x 2] BTREE ([ADDRESS_DETAIL_PID]) ); (You could get rid of the BTREENULL on the same field if you wanted to. It's no extra use now.) Another way, if you don't know that values in some existing field are unique, is to tell the engine to add a [mfd_id] field, and add a BTREE index on that. Values added to this special field are made (and kept) unique automatically. ALTER TABLE [Tims result Table] ( ADD [mfd_id] int64, ADD INDEX [mfd_id_x] BTREE ([mfd_id]) ); [Added.] A third way. Instead of using SELECT INTO then ALTER TABLE on the result, you can use CREATE TABLE first (adding either mfd_id or a field you know will contain unique values, with a BTREE index on that field), then using INSERT INTO to fill it.
|