Wednesday, 29 May 2019

Oracle | How to avoid same sub-query in multiple select in oracle?

This is three different selects using the same subquery. Repetition of the same subquery will increase the cost of execution cost of the main Query. See below.


SELECT *
FROM Address
WHERE address_key IN
  ( SELECT address_key FROM person_address WHERE peson_key IN (person_list));
SELECT *
FROM Phone
WHERE phone_key IN
  ( SELECT address_key FROM person_address WHERE peson_key IN (person_list));
SELECT *
FROM Email
WHERE address_key IN
  ( SELECT address_key FROM person_address WHERE peson_key IN (person_list));
 
-- person_list := '1,2,3,4'
 
Oracle's WITH clause is the solution of the above mentioned use case. To avoid the repetition of the same subquery, we can use the WITH clause and can use the result for instead of the repetitive subquery.

Oracle WITH Clause

Important bullet points of With Clause are:
Ø  With The clause in Oracle is released in Oracle 9i release 2 to improve the performance of complex SQL queries.
Ø  The clause works like global temporary tables of Oracle which is used to improve the query speed of complex SQL queries.
Ø  This technique is also called as sub-query factoring as it is used to De-factor the subqueries.
Ø  With clause in Oracle is not supported by all Oracle versions, the Oracle version 9i and beyond versions.
Ø  When sub-query needs to be executed multiple times at that time WITH clause is used.
Ø  The name which is assigned to the sub-query is treated as though it was an inline view or table.
Ø  The With Clause is useful in Recursive queries as well. 

WITH address_keys AS
  ( SELECT address_key FROM person_address WHERE peson_key IN (person_list))
SELECT * FROM table1, table2, address_keys
WHERE table1.address_key = address_keys.address_key
AND table2.address_key   = address_keys.address_key


No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...