Problems when Converting Index Column Types -Use DECODE to Reduce Processing -Sub Query-Oracle SQL Performance Tuning Tips ATOZ HELP

Problems when Converting Index Column Types  -Oracle SQL Performance Tuning Tips ATOZ HELP

wOracle performs simple column type conversion, or casting, when it compares columns of different type. If a numeric column is compared to an alphabetic column, the character column automatically has its type converted to numeric.

 

  Select *

  from Account

  Where ACCOUNT_ID = 90426001

In fact, because of conversion this statement will actually be processed as:

  Select *

  from Account

  Where to number(ACCOUNT_ID) = 90426001

•But the following statement:

 

  Select *

  From acc_txn

  Where acc_txn_ref_no = ‘119990012890’

•Will be processed as:

 

  Select *

  From acc_txn

  Where acc_txn_ref_no = to number(‘119990012890’ )

Use DECODE to Reduce Processing

 

•You can achieve the same result much more efficiently with DECODE:

 

  SELECT COUNT(DECODE(DEPT_NO,0020, ‘X’, NULL))

   D0020_COUNT,  

   COUNT(DECODE(DEPT_NO,0030,‘X’,NULL))

   D0030_COUNT,

   SUM(DECODE(DEPT_NO,0020, SAL, NULL))

   D0020_SAL,

   SUM(DECODE(DEPT_NO, 0030, SAL, NULL))

   D0030_SAL

     FROM    EMP

      WHERE  ENAME LIKE ‘SMITH%’;

•Similarly, DECODE can be used in GROUP BY or ORDER BY clause effectively.

Sub Query:

Most Efficient :

   SELECT      TAB_NAME

      FROM    TABLES

      WHERE (TAB_NAME,DB_VER)=

          (SELECT    TAB_NAME, DB_VER

          FROM     TAB_COLUMNS

          WHERE   VERSION = 604)

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

•The DECODE statement provides a way to avoid having to scan the same rows repetitively or to join the same table repetitively.

 

• For example:

 

     SELECT COUNT(*), SUM(SAL)

  FROM  EMP

  WHERE DEPT_NO = 0020

  AND ENAME LIKE ‘SMITH%’;

  SELECT COUNT(*), SUM(SAL)

  FROM  EMP

  WHERE DEPT_NO = 0030

  AND  ENAME LIKE ‘SMITH%’;

Tweet