Home > SQL Tuning > Upper Function suppresses index

Upper Function suppresses index

Founding:
 
– The upper function is used doubtfully on the p.fsa since the p.fsa are numeric. 
 After removing the upper function, the SQL gets hundred times faster than before.
– The detailed comparsion is shown as below
 

Before Tuning After Tuning
Responding time=2 sec. Responding Time=0.01sec
SQL Statistics
———————————————————-
          0  recursive calls
          0  db block gets
      35689  consistent gets
          0  physical reads
          0  redo size
       6346  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL Statistics
———————————————————-
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       6346  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

  
Tuned SQL
 
SELECT *
FROM (SELECT row_number() over (PARTITION BY fsa ORDER BY stb_num) AS rn, stb_id_act AS stb_id_1,
      LEAD(stb_id_act) OVER (PARTITION BY fsa ORDER BY stb_num) AS stb_id_2, res.*
FROM
(SELECT pa.stb_num, pa.stb_id AS stb_id_act, p.*
                FROM
                VI_PROFILE_T p,
                VI_PROFILE_ACTIVATION_T pa
                WHERE
                p.fsa = pa.fsa(+)
                AND p.fsa IN (select p.fsa
                                from
                                VI_PROFILE_T p,
                                VI_PROFILE_ACTIVATION_T pa
                                where
                                p.fsa = pa.fsa(+)
                                — AND  (UPPER(p.fsa) LIKE UPPER(‘10000599’) ESCAPE ‘\’) <– the upper function suppress the index and it is doubtful because all data of fsa are numeric
                                  AND  (p.fsa LIKE upper(‘10000599’) ESCAPE ‘\’)  <– remove the upper function like that
                                AND  (UPPER(status) = UPPER(‘CLOSED’) )
                                )
ORDER BY pa.stb_num ) res
)
WHERE rn = 2
/
Advertisements
Categories: SQL Tuning
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: