Hi,

I have a little problem. FB 2.1. Please advise if this can be solved
better in 2.5 - I may consider an upgrade.

Two tables (simplified):

create table "Owner" (
"Id" int not null,
constraint "OwnerPK" primary key ("Id"),
// Other omitted columns
);

create table "Item" (
"Id" int not null,
"Owner" int not null,
constraint "ItemPK" primary key ("Id")
);
create index "ItemOwner" on "Item" ("Owner"); // Stat = 0.000000

(Side note: The index should really be a foreign key, but the OO
framework I use doesn't generate it like a foreign key, probably to
avoid problems with circular references when adding and removing records.)

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".

For a select I'd do a join, but that's not possible on a delete, is it?

Regards,
Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64