Use Analytical functions to optimize the sql code - Example-1

 It is common practice to use corelated subquery to get the max record for a given value.

SELECT   B.COLA,
                 B.COLB
FROM   BIG_TABLE B
WHERE B.COLA IN (select VALUE from SMALL_TABLE d where d.colx = 'VALUE')
AND B.COLC = ( select max(COLC) 
                                 from BIG_TABLE C
                                where B.COLC = C.COLC
                              )  ;

  

Problem with Above query is Oracle needs to query the BIG_TABLE twice.

We can rewrite the query as follows using analytical function to avoid scanning table twice.

SELECT COLA,
                COLB
  FROM ( SELECT B.COLA,
                                 B.COLB
                                 B.COLC,
                                 MAX(B.COLC) OVER ( PARTITION BY B.COLA ) MAX_COLC
                   FROM BIG_TABLE B
                   WHERE B.COLA IN ( select VALUE from SMALL_TABLE d where d.colx = 'VALUE' )
                 )
WHERE COLC = MAX_COLC ;