Monday, January 10, 2011

Using analytical function rank() - an example of improving query performance

Today, I have encountered a sql in a production database, which looks like


select distinct rs, bid, 
     [some_other_cols ...  ]
  from
     prod_table n
   where ee_date in (
       select max(ee_date) from
                prod_table n1
           where n1.bid = n.bid
             and n1.cc = n.cc
             and n1.rs=n.rs)



---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |   571 | 51390 |   197   (4)| 00:00:03 |
|   1 |  HASH UNIQUE          |               |   571 | 51390 |   197   (4)| 00:00:03 |
|*  2 |   FILTER              |               |       |       |            |          |
|   3 |    HASH GROUP BY      |               |   571 | 51390 |   197   (4)| 00:00:03 |
|*  4 |     HASH JOIN         |               | 22250 |  1955K|   194   (3)| 00:00:03 |
|   5 |      TABLE ACCESS FULL| PROD_TABLE    | 22250 |   738K|    96   (2)| 00:00:02 |
|   6 |      TABLE ACCESS FULL| PROD_TABLE    | 22250 |  1216K|    97   (3)| 00:00:02 |
---------------------------------------------------------------------------------------


It contains a correlated sub-query. I rewrote it by using the analytical function rank(). I found that the consistent gets reduced by half



select distinct rs,bid, 
     [some_other_cols ...  ]
    from
    (
    select  n1.*,
           rank() over ( partition by bid, cc, rs order by ee_date desc ) rank
    from  prod_table n1
    )
    where rank =1;


-------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               | 22250 |   956K|       |   602   (2)| 00:00:08
|   1 |  HASH UNIQUE              |               | 22250 |   956K|  2984K|   602   (2)| 00:00:08
|*  2 |   VIEW                    |               | 22250 |   956K|       |   350   (2)| 00:00:05
|*  3 |    WINDOW SORT PUSHED RANK|               | 22250 |   956K|  2984K|   350   (2)| 00:00:05
|   4 |     TABLE ACCESS FULL     | PROD_TABLE    | 22250 |   956K|       |    98   (4)| 00:00:02
-------------------------------------------------------------------------------------------------