Ever need to look for missing data? For instance, the table TLD_HEADER is the header for TLD_SALES_DATA, TLD_MGR_OVERRIDE, TLD_REASON, and TLD_TENDER_DATA. Originally there was no foreign key on these child tables to TLD_HEADER so after years of collecting data, there were some cases where TLD_HEADER records did NOT EXIST for the other TLD_ records.
Once we made it a true parent of the other TLD_ tables, we had a problem and had to create a script to create the TLD_HEADER records in order for the delta that creates the FK to run successfully. (Create_TLD_Header.sql)
This query was used to identify missing TLD_HEADER records from TLD_SALES_DATA:
select distinct site_no, tran_dt, reg_no, tran_no
from tld_sales_data tsd
where NOT EXISTS (select *
from TLD_HEADER
where site_no = tsd.site_no
and tran_dt = tsd.tran_dt
and reg_no = tsd.reg_no
and tran_no = tsd.tran_no);
However in a customer's environment it was taking 7 hours and 53 minutes.
A sql guru looked at the script and suggested this query instead:
SELECT /*+ index_ffs(tsd TLD_SALES_DATA_PK) index_ffs(th TLD_HEADER_PK) */
distinct tsd.site_no, tsd.tran_dt, tsd.reg_no, tsd.tran_no
FROM tld_sales_data tsd, tld_header th
WHERE th.site_no(+) = tsd.site_no
AND th.tran_dt(+) = tsd.tran_dt
AND th.reg_no(+) = tsd.reg_no
AND th.tran_no(+) = tsd.tran_no
AND th.rowid is null;
Very interesting. Do an outer join, but where the th.rowid is null? That's kind of weird. But believe it or not this query picked up the same number of records, but did so in 26 minutes. That's 18 times faster! How embarrasing. So if you need to locate records that DON'T exist, use this method where an outer join is used instead of an inner join with a NOT EXISTS!
