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

Sql Constraints

Constraints allow you to automatically enforce the integrity of data and to filter the data that is placed in a database. In a sense, constraints are rules that define which data values are valid during INSERT, UPDATE, and DELETE operations. When a data modification transaction breaks the rules of a constraint, the transaction is rejected.

Scope

Constraints may be applied at the column-level or the table-level:

Column-level constraints

Declared as part of a column definition and apply only to that column.


Table-level constraints

Declared independently from any column definitions and may apply to one or more columns in the table. A table constraint is required when you wish to define a constraint that applies to more than one column.