Position of Joins in the WHERE Clause-Oracle SQL Performance Tuning Tips atoz help

Position of Joins in the WHERE Clause-Oracle SQL Performance Tuning Tips atoz help

•Table joins should be written first before any condition of WHERE clause. And the conditions which filter out the maximum records should be placed at the end after the joins as the parsing is done from BOTTOM to TOP.

•Least Efficient :

  SELECT  . . . .                

  FROM  EMP E

  WHERE  SAL > 50000

  AND  JOB = ‘CLERK’

  AND  25 < (SELECT  COUNT(*)

  FROM   EMP  WHERE   MGR = E.EMPNO);

w Most Efficient :

  SELECT  . . . .               

  FROM  EMP E

  WHERE 25 < (SELECT  COUNT(*)

  FROM  EMP

  WHERE   MGR = E.EMPNO )

  AND  SAL > 50000 AND JOB = ‘CLERK’;

 •ORACLE parser always processes table names from right to left, so the table name you specify last (driving table) is actually the first table processed.

•If you specify more than one table in a FROM clause of a SELECT statement, you must choose the table containing the lowest number of rows as the driving table.

• When ORACLE processes multiple tables, it uses an internal sort/merge procedure to join those tables.

• First, it scans and sorts the first table (the one specified last in the FROM clause).

•Next, it scans the second table (the one prior to the last in the FROM clause) and merges all of the rows retrieved from the second table with those retrieved from the first table.

•For example:

  Table TABA has 16,384 rows.

  Table TABB has 1 row.

                                     SELECT COUNT(*) FROM TABA, TABB 0.96 seconds elapsed

                                     SELECT COUNT(*) FROM TABB, TABA 26.09 seconds elapsed

•If three tables are being joined, select the intersection table as the driving table.

•The intersection table is the table that has many tables dependent on it.

•E.g.. The EMP table represents the intersection between the LOCATION table and the CATEGORY table.

  SELECT . . .

  FROM  LOCATION L, CATEGORY C,  EMP E

  WHERE     E.EMP_NO BETWEEN 1000 AND 2000

  AND E.CAT_NO = C.CAT_NO

  AND  E.LOCN = L.LOCN

      is more efficient than this next example:

  SELECT . . .

  FROM       EMP E,

  LOCATION  L, CATEGORY C           

  WHERE     E.CAT_NO = C.CAT_NO

  AND         E.LOCN = L.LOCN

  AND         E.EMP_NO BETWEEN 1000 AND 2000

Note : Provide your comments by clicking below options! Thanks ! :)

Tweet