Tim Romano wrote:
> The partial index is one very messy thing, fraught with ambiguities,
> something to avoid.
I want an index that only can be used to find rows with a particular
value or set of values. In what way is that ambiguous? Other databases
(e.g. postgres) seem to support this kind of thing.
> I can imagine other business rules being really
> bollixed up by the sudden reappearance of zombie rows.
This isn't a 'business rule', this is an optimization. No high
level logic will change. Just like when we use other sql indices.
> Under the partial index method, how would
> you ever find a row again once it has become invisible, unless you were
> perhaps to change or suspend the partial index rule, and cause the missing
> rows to reappear?
"Become invisible", meaning it no longer contains data that I care
about? I don't need to find it quickly because it no longer contains
data that I care about.
So, I'm not sure I understand your concerns.
Since SQLite doesn't support partial indices directly, I'm
thinking about making my own index as a separate table and
populating/depopulating it using triggers on the main table. I only
need it for fast lookups during deletion of the relevant rows, so I'll
hijack the app logic that wants to delete those rows and instead use
the secondary table to get the row ids, and delete those directly.
Something like DELETE FROM records WHERE __recno IN (SELECT __recno
FROM idxTable), where __recno is the INTEGER PRIMARY KEY on records.
Am I missing something?
Eric
--
Eric A. Smith
Furbling, v.:
Having to wander through a maze of ropes at an airport or
bank even when you are the only person in line.
-- Rich Hall, "Sniglets"
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
