Skip to content

Correlated Subquery to Analytics

At work I came across a query that was using correlated sub select and I thought it is worth sharing. I have seen many queries where correlated sub select is used which can easily be written using analytics. In this particular query correlated was used to select rows with maximum effective date for every contrived key and category. This query was taking anywhere between 2-4 seconds to complete. I was able to re-write this query using analytics and brought the execution time to less than 500 milliseconds.

I’ll demonstrate by re-generating the similar situation.

Let’s create a members table:


SQL> drop table members;

Table dropped.

SQL> 
SQL> create table members (
  2    Member_ID varchar2(10),
  3    member_ck varchar2(10),
  4    Category char(1),
  5    eff_date date
  6    ) ;

Table created.

Now load some data into members table:

SQL> insert into members values ('A','1','Z',sysdate) ;

1 row created.

SQL> 
SQL> insert into members values ('A','1','Z',sysdate-5) ;

1 row created.

SQL> 
SQL> insert into members values ('A','1','X',sysdate) ;

1 row created.

SQL> 
SQL> insert into members values ('A','1','X',sysdate-5) ;

1 row created.

SQL> 
SQL> insert into members values ('A','2','Z',sysdate-90) ;

1 row created.

SQL> 
SQL> insert into members values ('A','2','Z',sysdate-100) ;

1 row created.

SQL> 
SQL> insert into members values ('A','2','X',sysdate-80) ;

1 row created.

SQL> 
SQL> insert into members values ('A','2','X',sysdate-150) ;

1 row created.

SQL> 
SQL> insert into members values ('B','3','Z',sysdate-90) ;

1 row created.

SQL> 
SQL> insert into members values ('B','3','X',sysdate-200) ;

1 row created.

SQL> commit;

Following are rows for member ‘A.’ From this data set we need to select rownum 1,3,5 and 7. These are rows with maximum eff_date for member_ck and category.


SQL> select rownum, member_id, member_ck, category, eff_date
  2  from members a where member_id = 'A'
  3  ;

    ROWNUM MEMBER_ID  MEMBER_CK  C EFF_DATE
---------- ---------- ---------- - ---------
		 1 A	      1 		 Z 24-