Oracle Illustrated

Feb 21 2010   9:16AM GMT

Not Exists vs. Analytical functions



Posted by: Lakshmi Venkatesh
Tags:

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: