Oracle Illustrated

Feb 21 2010   9:16AM GMT

Not Exists vs. Analytical functions

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: