beverly@ai.cs.wisc.edu (Beverly Seavey (-Kung)) (10/02/90)
WE are compliling a database of boring NMR data. THe way we planned to screen for errors in the data is to have every piece of data entered twice, then take the difference. So, for example, I have two tables, mpshifts and lshifts that the 2 student hourlies have entered data into, and I want to find if either contains a row that the other does not. The following SQL query: select * from mpshifts where mpshifts.sh_event = 191 and not exists (select * from lshifts where lshifts.sh_event = 191 and mpshifts.sh_res = lshifts.sh_res and mpshifts.sh_atom = lshifts.sh_atom and mpshifts.sh_shift = lshifts.sh_shift); works ok when there is not much in mpshifts with sh_event = 191, but dies pretty early otherwise. Is there another way to do this query that would be more efficient? I essentially just want to take the set difference between the two tables, but I don't see set "minus" or "difference" in the Ingres SQL manual. Does anyone know how Oracle or Ingres front ends parse this query, and therefore how to optimize it? .