Rusty Conover wrote:
Using a temporary table seems to be the best way when you have lots
of keys. Of course there is a tradeoff point, such as for 1 or 2
rows a temporary table is overkill. Also it depends on if you're
temporary table is being stored in memory or on the disk, check the
relevant PRAGMA documentation to specify how you want them stored.
CREATE TEMP TABLE search_keys(oid INTEGER NOT NULL PRIMARY KEY);
Next, insert all of the records you're searching for into search_keys
table.
You might want to do an "ANALYZE" here on the temporary table, so the
planner can pick the most efficient way to do the upcoming join
depending on the number of rows you're searching for, and the number
of rows you're searching in.
Do the actual select:
SELECT ... from search_keys, target where search_keys.oid = bar.oid
I believe the ordering of the tables in the from clause may be
important as the planner may use it in determining which table to
process first, but if there are statistics about all of the tables it
may reorder them. drh, would know actual behavior for sure.
Finally don't forget to:
DROP TABLE search_keys
Cheers,
Rusty
--
Rusty Conover
InfoGears Inc.
Web: http://www.infogears.com
Hello Rusty,
Exactly what I was looking for
I've seen the PRAGMA TEMP_STORE to create a memory table.
Do you think I can join a memory table and a disk based one ?
Anyway, thank a lot
Best wishes
--
Noël Frankinet
Gistek Software SA
http://www.gistek.net
