Hi,
I have a question regarding the performance of DELETE FROM (or maybe
better: correlated subqueries).
I have a table "main" and a table "staging". In "staging" I have a
subset of "main". I want to delete all rows from "main" which are also
in "staging".
CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) );
CREATE TABLE staging ( ATT1 INT, ATT2 INT );
Then I execute
DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE main.att1
= staging.att1 AND main.att2 = staging.att2)
which takes a very long time. As far as I understand the query plan
SQLite scans the full staging table for each row in "main":
sqlite> EXPLAIN QUERY PLAN DELETE FROM main WHERE EXISTS (SELECT 1
FROM staging WHERE main.att1 = staging.att1 AND main.att2 = staging.att2)
...> ;
QUERY PLAN
|--SCAN TABLE main
`--CORRELATED SCALAR SUBQUERY
`--SCAN TABLE staging
How do I speed this up? The idea is that the database should scan
"staging" and lookup "main" (because an appropriate primary index exists).
But I'm open to any alternative approach. I just have the situation that
I have a very large "main" table and a "staging" table which contains
also a large number of tuples which should be deleted from "main".
Any ideas?
Thanks,
Jürgen
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
