SQL Design Patterns

Same table refered twice in a multi level subquery.

SELECT calc_scheme.scheme_id
FROM calc_scheme
WHERE NOT EXISTS
  (SELECT 'PRODUCT'
   FROM calc_rule_product
   WHERE calc_rule_product.scheme_id = calc_scheme.scheme_id)
OR EXISTS
  (SELECT 'PRODUCT'
   FROM calc_rule_product
   WHERE calc_rule_product.scheme_id = calc_scheme.scheme_id
   AND 'FLT' = calc_rule_product.code
   AND 'FLT' IS NOT NULL)

Becomes

SELECT calc_scheme.scheme_id
FROM calc_scheme,
    (SELECT scheme_id,
     code
   FROM calc_rule_product)
x
WHERE(calc_scheme.scheme_id = x.scheme_id(+)
 AND x.rowid IS NULL) OR(x.scheme_id = calc_scheme.scheme_id
 AND 'FLT' = x.code
 AND 'FLT' IS NOT NULL) 

For WHERE EXISTS single table sub-queries, try a Semi-Join. If this doesn't work, try rewriting the query as a join

SELECT *
FROM   table_a
WHERE  EXISTS (
       SELECT 1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
       AND    b.key1 = a.key1);

becomes

SELECT a.*
FROM   table_a a
, (    SELECT DISTINCT b.key1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
  ) d
WHERE  a.key1 = d.key1

For WHERE NOT EXISTS sub-queries, try re-writing the SQL as an outer join with a b.ROWID IS NULL clause. eg.

SELECT a.*
FROM   table_a a
WHERE  NOT EXISTS (
       SELECT 1
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a , table_b b
WHERE  a.col1 = b.col1 (+)
AND    b.rowid IS NULL

For IN and NOT IN sub-queries, eliminate correlated sub-queries, they must use a Nested Loops join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 IN (
       SELECT col2
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a
WHERE  (col1, col2) IN (
       SELECT col2
       FROM   table_b)

For IN sub-queries that are not correlated, try an ORDERED hint and a USE_HASH or USE_MERGE hint. eg:

SELECT /*+ ORDERED USE_HASH(a)*/ *
FROM table_a a
WHERE col1 IN (
      SELECT col1
      FROM   table_b b)

In this way, Oracle can run the outer query and the sub-query independently and perform a hash join.


For correlated col = (sub-query) sub-queries, Oracle must use a Filter. Try to re-write as a table join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 = (
       SELECT max(col2)
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a,
(      SELECT col1, max(col2)
       FROM   table_b
       GROUP  BY col1
) b
WHERE  a.col1 = b.col1