Oracle first N rows of a group using RANK running too slow

Question

I have a table with below columns

Table{
load_date DATE
effective_date DATE
Application_ID Varchar2
Datasource_id VARCHAR2
Rule_id Varchar2
Entity_Id varchar2
Rule_status number
batch_execution_ts TIMESTAMP
}

Now I have a query to fetch results as below

select * from (
select rank() over (partition by effective_date, rule_id, entity_id order by batch_execution_ts desc) as rank,
s.*
from TABLE s
where s.load_date between :date1 and :date2
and s.effective_date between :effdate1 and :effdate2
and application_id=:appid
and datasource_id=:dsid) result
where result.rank=1

We have a query requirement to send results in max od 3seconds.. but this query is running for whopping 4 mins and no results returned. TABLE is partitioned on "load_date"and we have LOCAL INDEX created on effective_date, application_id, datasource_id, rule_id, entity_id

Any suggestion on improving performance. FYI.. each load_date partition contains around 2-3 million rows. Running parallel is not helping the performance either.

Added EXPLAIN PLAN below.

Plan hash value: 2095006046
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                        |     8 | 20720 |   425   (1)| 00:00:01 |       |       |        |      |            |
|*  1 |  PX COORDINATOR                             |                        |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                       | :TQ10001               |     8 | 20720 |   425   (1)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    VIEW                                     |                        |     8 | 20720 |   425   (1)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|*  4 |     WINDOW SORT PUSHED RANK                 |                        |     8 | 12776 |   425   (1)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                             |                        |     8 | 12776 |   425   (1)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH                          | :TQ10000               |     8 | 12776 |   425   (1)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |
|*  7 |        WINDOW CHILD PUSHED RANK             |                        |     8 | 12776 |   425   (1)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|*  8 |         FILTER                              |                        |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|   9 |          NESTED LOOPS OUTER                 |                        |     8 | 12776 |   424   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  10 |           PX PARTITION RANGE ITERATOR       |                        |     8 |  2576 |   422   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWC |            |
|  11 |            TABLE ACCESS BY LOCAL INDEX ROWID| DQM_ENTITY_RULE_STATUS |     8 |  2576 |   422   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |
|* 12 |             INDEX RANGE SCAN                | NU_DQM_ERS_IDX_4       |    27 |       |   421   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |
|  13 |           VIEW PUSHED PREDICATE             |                        |     1 |  1275 |     7   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  14 |            NESTED LOOPS OUTER               |                        |     1 |   757 |     7   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  15 |             NESTED LOOPS OUTER              |                        |     1 |   725 |     6   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  16 |              NESTED LOOPS                   |                        |     1 |   711 |     5   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  17 |               NESTED LOOPS                  |                        |     1 |   614 |     4   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  18 |                NESTED LOOPS                 |                        |     1 |   511 |     3   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 19 |                 TABLE ACCESS BY INDEX ROWID | DQM_RULE               |     1 |   479 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 20 |                  INDEX UNIQUE SCAN          | PK_DQM_RULE            |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  21 |                 TABLE ACCESS BY INDEX ROWID | DQM_FW_DQ_DIM          |     1 |    32 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 22 |                  INDEX UNIQUE SCAN          | PK_DQM_FW_DQ_DIM       |     1 |       |     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  23 |                TABLE ACCESS BY INDEX ROWID  | DQM_RULE_FUNCTION      |     1 |   103 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 24 |                 INDEX UNIQUE SCAN           | PK_DQM_RULE_FUNCTION   |     1 |       |     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  25 |               TABLE ACCESS BY INDEX ROWID   | DQM_RULE_GRP           |     1 |    97 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 26 |                INDEX UNIQUE SCAN            | PK_DQM_RULE_GRP        |     1 |       |     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 27 |              INDEX FULL SCAN                | PK_DQM_FW_DQ_DIM_HRCHY |     1 |    14 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  28 |             TABLE ACCESS BY INDEX ROWID     | DQM_FW_DQ_DIM          |     1 |    32 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 29 |              INDEX UNIQUE SCAN              | PK_DQM_FW_DQ_DIM       |     1 |       |     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE(:EFFECTIVEDATETO)>=TO_DATE(:EFFECTIVEDATEFROM) AND TO_DATE(:LOADDATETO)>=TO_DATE(:LOADDATEFROM))
   3 - filter("LATEST_EXCEPTION"=1)
   4 - filter(RANK() OVER ( PARTITION BY "S"."EFFECTIVE_DT","S"."VENDOR_ENTITY_KEY","S"."RULE_ID" ORDER BY INTERNAL_FUNCTION("S"."BATCH_EXECUTION_TS") 
              DESC )<=1)
   7 - filter(RANK() OVER ( PARTITION BY "S"."EFFECTIVE_DT","S"."VENDOR_ENTITY_KEY","S"."RULE_ID" ORDER BY INTERNAL_FUNCTION("S"."BATCH_EXECUTION_TS") 
              DESC )<=1)
   8 - filter(TO_DATE(:EFFECTIVEDATETO)>=TO_DATE(:EFFECTIVEDATEFROM) AND TO_DATE(:LOADDATETO)>=TO_DATE(:LOADDATEFROM))
  12 - access("S"."EFFECTIVE_DT">=:EFFECTIVEDATEFROM AND "S"."LOAD_DT">=:LOADDATEFROM AND "S"."APPLICATION_ID"=SYS_OP_C2C(:SOURCEID) AND 
              "S"."DATA_SOURCE_ID"=SYS_OP_C2C(:VENDORID) AND "S"."EFFECTIVE_DT"<=:EFFECTIVEDATETO AND "S"."LOAD_DT"<=:LOADDATETO)
       filter("S"."LOAD_DT">=:LOADDATEFROM AND "S"."LOAD_DT"<=:LOADDATETO AND "S"."DATA_SOURCE_ID"=SYS_OP_C2C(:VENDORID) AND 
              "S"."APPLICATION_ID"=SYS_OP_C2C(:SOURCEID))
  19 - filter("RULETABLE"."FUNC_ID" IS NOT NULL AND "RULETABLE"."RULE_GRP_ID" IS NOT NULL)
  20 - access("RULETABLE"."RULE_ID"="S"."RULE_ID")
  22 - access("DIM"."DIMENSION_ID"="RULETABLE"."DIMENSION_ID")
  24 - access("RULETABLE"."FUNC_ID"="RULEFUNCTIONTABLE"."FUNC_ID")
  26 - access("RGRP"."RULE_GRP_ID"="RULETABLE"."RULE_GRP_ID")
  27 - access("SUB_DIM"."SUB_DIMENSION_ID"(+)="DIM"."DIMENSION_ID")
       filter("SUB_DIM"."SUB_DIMENSION_ID"(+)="DIM"."DIMENSION_ID")
  29 - access("DIM1"."DIMENSION_ID"(+)="SUB_DIM"."DIMENSION_ID")
 
Note
-----
   - dynamic sampling used for this statement (level=6)
   - Degree of Parallelism is 32 because of hint

How many partitions you have? The query will have to access *all of them*. See [here](https://stackoverflow.com/a/34975420/4808122) hints how you should post the execution plan. — Marmite Bomber, Nov 03 '20 at 14:46
Without seeing the plan used now. You would benefit more from an index that lead on the columns you have equality predicates against, the effective_date column should come last. That said, we know nothing about how effective those filters are, perhaps a full partition scan is the fastest way. Tell us about your data and what the filters should be achieving — Andrew Sayer, Nov 03 '20 at 15:03
TABLE is a view? Plan wants to use parallelism, does it have resources to run with 32 DOP? The nested loop full index scan looks dodgy, consider creating an index which solves that access (you want the used filter to be the first column in that index with the right data type) — Andrew Sayer, Nov 03 '20 at 16:01
First things first. Let's say you just want to pick the max batch_execution_ts from each group ("partition" in the analytic function). You can write that query, with GROUP BY and MAX, and no analytic functions (and no outer queries). It won't give you all the data you need, but it WILL tell you how much you can improve execution time. If THAT query also takes a long time, then the issue is not how your query is written; it may be the amount of data, the indexes, etc. - including the observation that your so-called "table" doesn't seem to be a table, according to the execution plan you posted. — , Nov 03 '20 at 17:18
Main driving entity "DQM_ENTITY_RULE_STATUS" is a table. VIEW PUSHED PREDICATE is a (select.. with JOINS) containing a max of 400-500rows of data in final result set. — Sasi, Nov 04 '20 at 21:33
Tried to get data using GROUP BY & MAX() but that query is also taking longer time. — Sasi, Nov 05 '20 at 11:11

Oracle first N rows of a group using RANK running too slow

0 Answers0