>I have some very large tables from which I have to get records NOT IN other very large tables. Code like this:
>
>select * from table1;
>where keyfield NOT IN;
>(select keyfield from table2)
>
>is not optimizable, and is extremely slow (about 15 minutes or more) for each query. I have tried a SCAN/SEEK, but it is at least as slow or worse, since you're looking for unique keys that don't exist, and therefore must hit every record.
>
>Any ideas for something faster?

Hi Bruce,

I haven't replied cause I wasn't really sure what could be done about this in a conventional manner. After reading the other posts it seems "conventional" might not be the way to go.

In light of that I'm gonna suggest something to prick your thinking that might lead you to a reasonable soultion. It may be a stupid idea and kinda off the top of my head and I don't know the structure of your tables/relations, but if this is something that really needs to be speeded up, would it be possible to create and maintain a separate table containing the id's not in the main table which could be indexed. Then the select might be something like:

select * from table1;
where keyfield IN;
(select keyfield from MyNotInTable)

Again, the tables relations, ability to determine records' in/not in status, maintainance nightmares, etc. might be prohibitive, but just a thought to provoke your thoughts.

Bill