Reduce the result set as early as possible | IN versus Exists

Avoid using sub-selects and redundant expressions

Let us look at the following real example on subselects:.

SELECT distinct(CATENTRY_ID)
from CATENTRY 
where MARKFORDELETE=0 
AND 
1)  (CATENTRY_ID
     in (SELECT CATENTRY_ID 
          FROM CATGPENREL 
         WHERE CATGROUP_ID = 10000000)
         or CATENTRY_ID       
2)      in (SELECT CATENTRY_ID_CHILD 
           from CATENTREL, CATGPENREL
          where CATENTRY_ID_PARENT=CATENTRY_ID 
           and CATGROUP_ID = 10000000))

The above SQL has two levels of subselect. This makes the SQL statement very expensive. Optimization is required for such statements. If there are common tables in the From clause between the sub-selects, then it is a good hint that the sub-selects can be converted easily to joins.

A better and more efficient version of the SQL is:

SELECT distinct (c.CATENTRY_ID)
From CATENTRY c , catgpenrel b ,catentrel d
Where 
  MARKFORDELETE=0
  AND
  b.CATGROUP_ID = 10000000
  and
  (
1)  (c.catentry_id=b.catentry_id)
        or
2)  (c.CATENTRY_ID=d.CATENTRY_ID_PARENT
     and b.catentry_id=d.CATENTRY_ID_CHILD))

Note that:

• The underlined clause (CATGROUP_ID = 100000000) was repeated and evaluated multiple times in the original statement. In the new tuned SQL, the clause was taken to be outside the scope of the original sub-selects.

• The original subselect #1 was rewritten by moving the table "CATGPENREL" in the "FROM" section to the main SQL. The new form of the subselect is shown as # 1 above. It is a simple join expression.

• For sub-select #2 in the original SQL, the table "CATENTREL" was moved to the main. The other table "CATGPENREL" was moved to the main part of tuning sub-select #1.