Not Exists vs. Analytical functions

Analytical Functions - Introduced in Oracle 8i. There are different families of functions available and with each release new functions are introduced to the family tree. The below is a quick demonstration of using Analytical functions in place of NOT EXISTS.

Note: Similar logic can be applied to EXISTS also.

This is particularly useful for huge sets of data joined across multiple tables.
Following is a simple demonstration.

Case 1 - NOT EXIST vs USE OF ANALYTICAL FUNCTION

Requirement - Pick only those objects which are referenced in - PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE.
and not part of the rest.


ie., say,

TABLE_NAME OBJECT_TYPE
TAB1 PACKAGE
TAB1 PACKAGE BODY
TAB1 [ TRIGGER ]
TAB1 INDEX

Say, in the above example TRIGGER is not part of the expected in-list [ PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE ]. So, this should not be taken for consideration.

TABLE_NAME OBJECT_TYPE
TAB2 PACKAGE
TAB2 PACKAGE BODY
TAB2 INDEX

Above is part of the in-list, it should be included to the select.

Step 1 - Create table


Drop table KEY_TABLE

CREATE TABLE KEY_TABLE (table_name varchar2(50), referenced_in varchar2(50), object_id number)

Step 2 - Populate data

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB1', object_type
from all_objects) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB2', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB3', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'PACKAGE BODY''PROCEDURE')
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB4', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'PACKAGE BODY''PROCEDURE', 'TRIGGER')
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB5', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE BODY', 'PACKAGE', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
) a


INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct
'TAB'||rownum * 20
, object_type
from all_objects) a

Step 3 - QUERY

-- USING NOT EXISTS

SELECT /*+ ALL_ROWS */ * from key_table ktab
WHERE ktab.REFERENCEd_IN in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
AND NOT EXISTS
(SELECT 1 FROM key_Table elim
WHERE elim.object_id = ktab.object_id
AND elim.REFERENCEd_IN not in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE'))


-- USING ANALYTICAL FUNCTIONS

SELECT /*+ ALL_ROWS */ ktab.*,
COUNT(1) OVER (PARTITION BY referenced_in, table_name order by referenced_in, table_name) COUNT_TOTAL,
(CASE WHEN ktab.referenced_in in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE') THEN
1
else
0
END) COUNT_ELIM
FROM key_table_1 ktab