make this sql query faster or use pl sql?

Question

So the below query on an oracle server takes around an hour to execute. Is it a way to make it faster?

SELECT * 
FROM ACCOUNT_CYCLE_ACTIVITY aca1 
WHERE aca1.ACTIVITY_TYPE_CODE='021' 
  AND aca1.ACTIVITY_GROUP_CODE='R12' 
  AND aca1.CYCLE_ACTIVITY_COUNT='999'   
  AND 
  EXISTS   
  (
      SELECT 'a' 
      FROM ACCOUNT_CYCLE_ACTIVITY aca2     
      WHERE  aca1.account_id = aca2.account_id 
        AND aca2.ACTIVITY_TYPE_CODE='021'  
        AND aca2.ACTIVITY_GROUP_CODE='R12' 
        AND aca2.CYCLE_ACTIVITY_COUNT ='1' 
        AND aca2.cycle_activity_amount > 25
        AND  (aca2.cycle_ctr > aca1.cycle_ctr) 
        AND aca2.cycle_ctr =  
        (
            SELECT MIN(cycle_ctr) 
            FROM ACCOUNT_CYCLE_ACTIVITY aca3 
            WHERE aca3.account_id = aca1.account_id 
              AND aca3.ACTIVITY_TYPE_CODE='021'  
              AND aca3.ACTIVITY_GROUP_CODE='R12' 
              AND aca3.CYCLE_ACTIVITY_COUNT ='1' 
       )
  );

So basically this is what it is trying to do. Find a row with a R12, 021 and 999 value, for all those rows we have to make sure another row exist with the same account id, but with R12, 021 and count = 1. If it does we have to make sure that the amount of that row is > 25 and the cycle_ctr counter of that row is the smallest.

As you can see we are doing repetition while doing a select on MIN(CYCLE_CTR).

EDIT: There is one index define on ACCOUNT_CYCLE_ACTIVITY table's column ACCOUNT_ID.

Our table is ACCOUNT_CYCLE_ACTIVITY. If there is a row with ACTIVITY_TYPE_CODE = '021' and ACTIVITY_GROUP_CODE = 'R12' and CYCLE_ACTIVITY_COUNT = '999', that represents the identity row.

If an account with an identity row like that has other 021 R12 rows, query for the row with the lowest CYCLE_CTR value that is greater than the CYCLE_CTR from the identity row. If a row is found, and the CYCLE_ACTIVITY_AMOUNT of the row found is > 25 and CYCLE_ACTIVITY_COUNT = 1, report the account.

Note that identity row is just for identification and will not be reported.

For example, this a SELECT on a account_id which should be reported.

  Account_ID    Group_Code  Type_code   Cycle_ctr   Activity_Amount Activity_count
 53116267       R12          021       14          0               999
 53116267       R12          021       25          35              1
 53116267       R12          021       22          35              1
 53116267       R12          021       20          35              1

There are several other Activity_count apart from 999 and 1, so a WHERE clause for that is necessary.

Similarly if the above example was like following

 Account_ID     Group_Code  Type_code   Cycle_ctr   Activity_Amount Activity_count
     53116267       R12          021       14          0               999
     53116267       R12          021       25          35              1
     53116267       R12          021       22          35              1
     53116267       R12          021       20         **20**           1

It wouldnt be reported because the activity_amount of the row with the lowest cycle_ctr greater than the cycle_ctr of the identity row is 20, which is less than 25.

Explain plan after

      explain plan for select * from account_activity;
      select * from table(dbms_xplan.display);

Plan hash value: 1692077632

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                  |   470M|    12G|   798K  (1)| 02:39:38 |       |       |
|   1 |  PARTITION HASH ALL        |                  |   470M|    12G|   798K  (1)| 02:39:38 |     1 |    64 |
|   2 |   TABLE ACCESS STORAGE FULL| ACCOUNT_ACTIVITY |   470M|    12G|   798K  (1)| 02:39:38 |     1 |    64 |
---------------------------------------------------------------------------------------------------------------

Answer 1

My rewrite of the query is this:

Select  Aca1.* 
From    Account_Cycle_Activity Aca1
Join    Account_Cycle_Activity Aca2
    On  Aca2.Account_Id = Aca1.Account_Id
    And Aca2.Group_Code = Aca1.Group_Code
    And Aca2.Type_Code  = Aca1.Type_Code
    And Aca2.Activity_Amount > 25
    And Aca2.Activity_Count = 1
    And Aca2.Cycle_Ctr > Aca1.Cycle_Ctr
    And Aca2.Cycle_Ctr =(
            Select  Min( Cycle_Ctr ) 
            From    Account_Cycle_Activity Aca3 
            Where   Aca3.Account_Id = Aca1.Account_Id 
              And   Aca3.Type_Code  = Aca1.Type_Code 
              And   Aca3.Group_Code = Aca1.Group_Code
              And   Aca3.Activity_Count =1 
       )
Where Aca1.Type_Code    = 21
  And Aca1.Group_Code   = 'R12' 
  And Aca1.Activity_Count = 999;

But the execution plan wasn't all that different and, more significantly, the cost, 14, was the same. However, then I added the two indexes and the cost fell from 14 to 2. I tried to create a Fiddle but, as usual, the Oracle section was inoperative. So here it is:

Create Table Account_Cycle_Activity(
    Account_Id      Int Not Null,
    Group_Code      Char( 3 ) Not Null,
    Type_Code       Int Not Null,
    Cycle_Ctr       Int Not Null,
    Activity_Amount Int Not Null,
    Activity_Count  Int Not Null
);

insert into Account_Cycle_Activity
    select  53116267, 'R12', 21, 14, 0, 999 from dual union all
    select  53116267, 'R12', 21, 25, 35,  1 from dual union all
    Select  53116267, 'R12', 21, 22, 35,  1 From Dual Union All
    select  53116267, 'R12', 21, 20, 35,  1 from dual;

-- Execute the query before creating these indexes and again after.
Create Index Ix_Account_Cycle_Activity1
    On Account_Cycle_Activity( Account_Id, Group_Code, Type_Code, Activity_Amount, Activity_Count );
Create Index Ix_Account_Cycle_Activity2
    On Account_Cycle_Activity( Cycle_Ctr );

Answer 2

Rewrite the query using explicit joins, and not with EXISTS.

Basically these two lines

WHERE  aca1.account_id = aca2.account_id 
AND  (aca2.cycle_ctr > aca1.cycle_ctr) 

are the join condition for joining the first and second select, and this one joins the first and the third.

WHERE aca3.account_id = aca1.account_id

The query should look like this

select distinct aca1.*
FROM ACCOUNT_CYCLE_ACTIVITY aca1, ACCOUNT_CYCLE_ACTIVITY aca2, ACCOUNT_CYCLE_ACTIVITY aca3
WHERE
 join conditions and other selection conditions

Answer 3

I would probably start with use of the WITH statement to hopefully reduce the number of times that the data is selected, and make it more readable. The other thing i would recommend is replacing the exists by some sort of join.

with base as 
(
    select *
    from account_cycle_activity
    where activity_type_code = '021'
      and activity_group_code = 'R12'
)
SELECT * 
FROM base aca1 
WHERE aca1.CYCLE_ACTIVITY_COUNT='999'   
  AND 
  EXISTS   
  (
      SELECT 'a' 
      FROM base aca2     
      WHERE  aca1.account_id = aca2.account_id 
        AND aca2.CYCLE_ACTIVITY_COUNT ='1' 
        AND aca2.cycle_activity_amount > 25
        AND  (aca2.cycle_ctr > aca1.cycle_ctr) 
        AND aca2.cycle_ctr =  
        (
            SELECT MIN(cycle_ctr) 
            FROM base aca3 
            WHERE aca3.account_id = aca1.account_id 
              AND aca3.CYCLE_ACTIVITY_COUNT ='1' 
       )
  );