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

 This is another example of using ANALYTICAL function to optimize the SQL Access. 

Consider the following SQL

-- Table Columns:

TABLE MY_TABLE
(   COLA,

     COLB,

     TXT_1,

     TXT_2,

     TXT_3,

     SRCH_TXT_1,

     SRCH_TXT_2,

     SRCH_TXT3

) ;

SELECT   a.*,
                  a.TXT_1 as FULL_TXT,

                  a.SRCH_TXT_1  as SRCH_TXT
  FROM     MY_TABLE a

UNION

SELECT   a.*,
                  a.TXT_2 as FULL_TXT,

                  a.SRCH_TXT_2  as SRCH_TXT
  FROM     MY_TABLE a

UNION

SELECT   a.*,
                  a.TXT_3 as FULL_TXT,

                  a.SRCH_TXT_3  as SRCH_TXT
  FROM     MY_TABLE a ;

Above query is going to get all the rows of the table 3 times with extra columns FULL_TXT and SRCH_TXT. It is basically conversion of rows into columns. Oracle provides UNPIOVT to achieve this.

SELECT DISTINCT COLA,

                                   COLB,

                                   U_TXT_1 as TXT_1,

                                   U_TXT_2 as TXT_2,

                                   U_TXT_3 as TXT_3,

                                   U_SRCH_TXT_1 as SRCH_TXT_1,

                                   U_SRCH_TXT_2 as SRCH_TXT_2,

                                   U_SRCH_TXT_3 as SRCH_TXT_3,

                                   FULL_TXT,

                                   FULL_SRCH_TXT

(

SELECT    *

   FROM    

(  SELECT a.*,

                                     a.TXT_1 as U_TXT_1,

                                     a.TXT_2 as U_TXT_2,

                                     a.TXT_3 as U_TXT_3,

                                     a.SRCH_TXT_1 as U_SRCH_TXT_1,

                                     a.SRCH_TXT_2 as U_SRCH_TXT_2,

                                     a.SRCH_TXT_3 as U_SRCH_TXT_3

                       FROM   MY_TABLE a

)

UNPIVOT (    ( FULL_TXT, FULL_SRCH_TXT) for node in (  (TXT_1, SRCH_TXT_1)  as  '1',

                                                                                                        (TXT_2, SRCH_TXT_2)  as  '2',

                                                                                                        (TXT_3, SRCH_TXT_3)  as  '3',

                                                                                                     )

) 

) ;

                              

                   

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 ;