Power of Sets : Performance Improvement Tip

its really practical,

recently i had a task of writing a sql statement to fetch records
in the source table that did not exist in the target table for a
particular column filter criteria like ( ex: KPI_ID in (1,2,3) ).

initially i used following approach:


create table test_table as
SELECT decode(kpi_id,1,9,2,9,3,9) from source table
WHERE KPI_ID not in
(
SELECT * from TARGET_TABLE
WHERE KPI_ID IN(1,2,3)
)


The above approach was taking considerable time to execute.
as it involves decode operation as well as not in (can't exactly determine the performance loss

then i remembered set theory

like


if A = B union C

implies B = A minus C


so i used approach


CREATE TABLE test_table as
(
SELECT decode(kpi_id,1,9,2,9,3,9) from source table
minus
SELECT decode(kpi_id,1,9,2,9,3,9) from TARGET_TABLE
WHERE KPI_ID IN(1,2,3)
)

here the performance improved dramatically.
i would call it as wonder of set theory...