Kjell,

> Now I have about 150 million items, so I absolutely want to avoid unindexed
> scans on this table.
>
> I need to delete all items for a subset of 231 owners. How can I do that
> without causing a full scan of the "Item" table and without first creating an
> explicit list of "Owner"."Id" values and paste that into the SQL?
>
> This (simplified) SQL will cause a full scan on "Item":
>
> delete from "Item" I
> where I."Owner" in (
> select O."Id"
> from "Owner" O
> where O."IndexedIntCol" = 12345
> )
>
> It won't accept an explicit plan telling it to use the index on "Item"."Owner".

Try this

EXECUTE BLOCK AS
DECLARE VARIABLES TOwnerID INTEGER;
BEGIN
FOR
SELECT
"Id"
FROM "Owner"
WHERE
"IndexedIntCol" = 12345
INTO
:TOwnerID
DO BEGIN
DELETE FROM "Item" WHERE "Owner" = :TOwnerID;
END
END


Sean