Home » SQL & PL/SQL » SQL & PL/SQL » Adding a from () to a query made it so slow (11.2.0.1.0)
Adding a from () to a query made it so slow [message #679842] Mon, 06 April 2020 13:39 Go to next message
OraFerro
Messages: 386
Registered: July 2011
Senior Member
Hi All,

I hope everybody is well and safe together with their beloved ones.

I have a query that works fast. For this query I added an addition from (select data, key) that also runs so far as per the below example. although both queries are fast, when i added the second query to the first one as part of the from clause, it became significantly slow (from 0.3 secs to 9+ secs). I wonder why is that:
select a.key, a.d1, a.d2, a.d3, b.d4
from
 a,
 (
   d4, key
 ) b
where a.key = b.key

Because the actual query is difficult to simulate with similar results, I attached the exec plan.

Thanks,
Ferro
  • Attachment: ExecPlan.txt
    (Size: 20.35KB, Downloaded 34 times)
Re: Adding a from () to a query made it so slow [message #679844 is a reply to message #679842] Mon, 06 April 2020 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.

Re: Adding a from () to a query made it so slow [message #679845 is a reply to message #679844] Mon, 06 April 2020 16:14 Go to previous messageGo to next message
OraFerro
Messages: 386
Registered: July 2011
Senior Member
@Michel, thanks.

Dear All,

Thanks, here you are:
1- Slow Query:

Select  cnt.SHORT_NAME_E Country, agr.NUMBER0 Agreement_no, agr.AGREEMENT_SERIAL, SUBSTR(prj.NAME_ENG, 0, 10) Project_name, evt.AUTHORIZATION_DATE, evt.EXPIRY_EXTENSION_DATE,  extract(month from evt.FIRST_INTEREST_DUE_DATE) || ' and ' || Extract (month from evt.SECOND_INTEREST_DUE_DATE) Acq_Due_Months, 
        amt.VALUE Agreement_amount,
        aam.Amount_modifications, 
        last_amount_date,
        twd.Amount total_withdrawal,  
        amt.VALUE - twd.Amount Loan_Balance,      
        Last_withdrawal_date,
        w3y.w3y,  
        round(w3y.w3y/3,2) Average_withd_l3y, 
        round((evt.EXPIRY_EXTENSION_DATE - sysdate)/360,0) withdrawal_window,
        case when (w3y.w3y/3) > 0
              then round((amt.VALUE - twd.Amount)/(w3y.w3y/3),2)
              else -1
        end
        Expect_yrs_to_withdrawal,
        tpa.amount principle_acquisition,
        tpr.amount principle_repayment,
        tpa.amount - tpr.amount unpaid_principle,
        tia.amount Interest_acquisition,
        tir.amount interest_repayment,
        tia.amount - tir.amount unpaid_iterest
  from  N_AGREEMENT agr, V_LAST_AGREEMENT_EVENTS evt, DAG_PROJECT prj, N_COUNTRY cnt,
        N_AGREEMENT_AMOUNT amt, 
        TABLE(F_AGR_TRN_TYPE_INFO(2,sysdate,1,3)) twd, -- total withdrawal
        (select nvl(count(*) -1, 0) Amount_modifications, max( DATE0) last_amount_date, FK_AGREEMENT_SERIAL from N_AGREEMENT_AMOUNT  group by FK_AGREEMENT_SERIAL) aam,
        (select max(trn.VALUE_DATE) Last_withdrawal_date, FK_AGREEMENT_SERIAL from TRN_AGREEMENT_TRANSACTION trn where trn.TYPE_ID = 2 group by trn.FK_AGREEMENT_SERIAL) lwd,
        TABLE(F_AGR_TRN_TYPE_INFO(3,sysdate,1,3)) tpa, 
        TABLE(F_AGR_TRN_TYPE_INFO(6,sysdate,1,3)) tpr, 
        TABLE(F_AGR_TRN_TYPE_INFO(4,sysdate,1,3)) tia, 
        TABLE(F_AGR_TRN_TYPE_INFO(7,sysdate,1,3)) tir, 
        (
          select nvl(w3y,0) w3y, agreement_serial
            from
            (
              select sum(WAT) w3y, fk_AGREEMENT_SERIAL FROM
                (
                  select trn.amount wat, trn.fk_AGREEMENT_SERIAL , trn.VALUE_DATE, trn.TYPE_ID, lwd.Last_withdrawal_date
                    FROM TRN_AGREEMENT_TRANSACTION trn, 
                          (select max(trn.VALUE_DATE) Last_withdrawal_date, FK_AGREEMENT_SERIAL from TRN_AGREEMENT_TRANSACTION trn where trn.TYPE_ID = 2 group by trn.FK_AGREEMENT_SERIAL)  lwd
                    where trn.FK_AGREEMENT_SERIAL = lwd.FK_AGREEMENT_SERIAL (+)
                      and trn.type_id = 2
                ) 
                where value_date between Last_withdrawal_date and sysdate
                group by fk_AGREEMENT_SERIAL
            ) dta,
            N_AGREEMENT agr
            where agr.AGREEMENT_SERIAL = dta.fk_AGREEMENT_SERIAL (+)
              and agr.SECTOR_ID = 1 
              and agr.STATUS_ID = 2 
              and agr.TYPE_ID = 2         
        ) w3y -- Withdrawals 3 years before the last withdrawal
  where agr.AGREEMENT_SERIAL = evt.AGREEMENT_SERIAL
    and agr.FK_PROJECTNUMBER0 = prj.PROJECT_SERIAL
    and agr.FK_COUNTRYCODE = cnt.CODE
    and agr.AGREEMENT_SERIAL = amt.FK_AGREEMENT_SERIAL
    and agr.AGREEMENT_SERIAL = aam.FK_AGREEMENT_SERIAL
    and agr.AGREEMENT_SERIAL = twd.agreement_serial
    and agr.agreement_serial = tpa.agreement_serial
    and agr.agreement_serial = tpr.agreement_serial
    and agr.agreement_serial = tia.agreement_serial
    and agr.agreement_serial = tir.agreement_serial
    and agr.AGREEMENT_SERIAL = lwd.FK_AGREEMENT_SERIAL  (+)
    and agr.SECTOR_ID = 1 
    and agr.STATUS_ID = 2 
    and agr.TYPE_ID = 2   
    and agr.AGREEMENT_SERIAL = w3y.AGREEMENT_SERIAL
    and amt.STATUS = 'CURRENT'
    ;
2- Exec Plan
PLAN_TABLE_OUTPUT
Plan hash value: 2970467700
 
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |   120M|    41G|       | 11296   (8)| 00:02:16 |
|*  1 |  HASH JOIN                               |                           |   120M|    41G|       | 11296   (8)| 00:02:16 |
|   2 |   VIEW                                   |                           |  8329 |   211K|       | 10010   (2)| 00:02:01 |
|   3 |    HASH GROUP BY                         |                           |  8329 |   341K|       | 10010   (2)| 00:02:01 |
|*  4 |     HASH JOIN OUTER                      |                           |  8329 |   341K|       | 10009   (2)| 00:02:01 |
|*  5 |      TABLE ACCESS FULL                   | N_AGREEMENT               |   159 |  3975 |       |    12   (0)| 00:00:01 |
|   6 |      VIEW                                |                           | 99628 |  1653K|       |  9996   (2)| 00:02:00 |
|*  7 |       FILTER                             |                           |       |       |       |            |          |
|   8 |        HASH GROUP BY                     |                           | 99628 |  4670K|   114M|  9996   (2)| 00:02:00 |
|*  9 |         HASH JOIN RIGHT OUTER            |                           |  1992K|    91M|  1112K|   787   (3)| 00:00:10 |
|* 10 |          TABLE ACCESS FULL               | TRN_AGREEMENT_TRANSACTION | 42045 |   615K|       |   315   (1)| 00:00:04 |
|* 11 |          TABLE ACCESS FULL               | TRN_AGREEMENT_TRANSACTION | 42045 |  1354K|       |   316   (1)| 00:00:04 |
|* 12 |   HASH JOIN                              |                           |  2291K|   749M|       |   632   (5)| 00:00:08 |
|  13 |    VIEW                                  |                           | 16249 |   777K|       |    44   (5)| 00:00:01 |
|  14 |     TRANSPOSE                            |                           |       |       |       |            |          |
|  15 |      SORT GROUP BY PIVOT                 |                           | 16249 |   238K|       |    44   (5)| 00:00:01 |
|  16 |       TABLE ACCESS FULL                  | N_EVENT                   | 16249 |   238K|       |    42   (0)| 00:00:01 |
|* 17 |    HASH JOIN                             |                           |   265K|    74M|       |   575   (3)| 00:00:07 |
|  18 |     COLLECTION ITERATOR PICKLER FETCH    | F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|* 19 |     HASH JOIN                            |                           | 62391 |    17M|       |   544   (3)| 00:00:07 |
|  20 |      COLLECTION ITERATOR PICKLER FETCH   | F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|* 21 |      HASH JOIN                           |                           | 14651 |  4149K|       |   514   (3)| 00:00:07 |
|  22 |       COLLECTION ITERATOR PICKLER FETCH  | F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|* 23 |       HASH JOIN                          |                           |  3440 |   967K|       |   484   (3)| 00:00:06 |
|  24 |        COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|* 25 |        HASH JOIN                         |                           |   808 |   225K|       |   454   (2)| 00:00:06 |
|* 26 |         HASH JOIN                        |                           |   190 | 53960 |       |   425   (3)| 00:00:06 |
|* 27 |          HASH JOIN                       |                           |   190 | 22230 |       |   353   (3)| 00:00:05 |
|* 28 |           HASH JOIN                      |                           |   190 | 15580 |       |   343   (2)| 00:00:05 |
|* 29 |            HASH JOIN OUTER               |                           |   159 |  9858 |       |   335   (2)| 00:00:05 |
|* 30 |             HASH JOIN                    |                           |   159 |  6360 |       |    17   (6)| 00:00:01 |
|* 31 |              TABLE ACCESS FULL           | N_AGREEMENT               |   159 |  3975 |       |    12   (0)| 00:00:01 |
|  32 |              TABLE ACCESS FULL           | N_COUNTRY                 |   206 |  3090 |       |     4   (0)| 00:00:01 |
|  33 |             VIEW                         |                           |  1897 | 41734 |       |   318   (2)| 00:00:04 |
|  34 |              HASH GROUP BY               |                           |  1897 | 28455 |       |   318   (2)| 00:00:04 |
|* 35 |               TABLE ACCESS FULL          | TRN_AGREEMENT_TRANSACTION | 42045 |   615K|       |   315   (1)| 00:00:04 |
|* 36 |            TABLE ACCESS FULL             | N_AGREEMENT_AMOUNT        |  1903 | 38060 |       |     8   (0)| 00:00:01 |
|  37 |           VIEW                           |                           |  1903 | 66605 |       |     9  (12)| 00:00:01 |
|  38 |            HASH GROUP BY                 |                           |  1903 | 22836 |       |     9  (12)| 00:00:01 |
|  39 |             TABLE ACCESS FULL            | N_AGREEMENT_AMOUNT        |  2590 | 31080 |       |     8   (0)| 00:00:01 |
|  40 |          TABLE ACCESS FULL               | DAG_PROJECT               |  1907 |   311K|       |    71   (0)| 00:00:01 |
|  41 |         COLLECTION ITERATOR PICKLER FETCH| F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("AGR"."AGREEMENT_SERIAL"="W3Y"."AGREEMENT_SERIAL")
   4 - access("AGR"."AGREEMENT_SERIAL"="FK_AGREEMENT_SERIAL"(+))
   5 - filter("AGR"."STATUS_ID"=2 AND "AGR"."TYPE_ID"=2 AND "AGR"."SECTOR_ID"=1)
   7 - filter("TRN"."VALUE_DATE">=MAX("TRN"."VALUE_DATE"))
   9 - access("TRN"."FK_AGREEMENT_SERIAL"="FK_AGREEMENT_SERIAL"(+))
  10 - filter("TRN"."TYPE_ID"(+)=2)
  11 - filter("TRN"."TYPE_ID"=2 AND "TRN"."VALUE_DATE"<=SYSDATE@!)
  12 - access("AGR"."AGREEMENT_SERIAL"="AGREEMENT_SERIAL")
  17 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
  19 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
  21 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
  23 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
  25 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
  26 - access("AGR"."FK_PROJECTNUMBER0"="PRJ"."PROJECT_SERIAL")
  27 - access("AGR"."AGREEMENT_SERIAL"="AAM"."FK_AGREEMENT_SERIAL")
  28 - access("AGR"."AGREEMENT_SERIAL"="AMT"."FK_AGREEMENT_SERIAL")
  29 - access("AGR"."AGREEMENT_SERIAL"="LWD"."FK_AGREEMENT_SERIAL"(+))
  30 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
  31 - filter("AGR"."STATUS_ID"=2 AND "AGR"."TYPE_ID"=2 AND "AGR"."SECTOR_ID"=1)
  35 - filter("TRN"."TYPE_ID"=2)
  36 - filter("AMT"."STATUS"='CURRENT')
3- DDL: all tables return empty except the one that selects from PLAN_TABLE as it returns invalid table and I failed to invoke (@ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL) as I am using an SQL client (dbForge) and it seems that Oracle home settings is not correct.

4- Could not locate trace_results.txt!

Thanks

[Updated on: Mon, 06 April 2020 18:29]

Report message to a moderator

Re: Adding a from () to a query made it so slow [message #679863 is a reply to message #679845] Wed, 08 April 2020 04:55 Go to previous messageGo to next message
John Watson
Messages: 8265
Registered: January 2010
Location: Global Village
Senior Member
Most of the cost (9996 out of 11296) is coming here,
|   6 |      VIEW                                |                           | 99628 |  1653K|       |  9996   (2)| 00:02:00 |
|*  7 |       FILTER                             |                           |       |       |       |            |          |
|   8 |        HASH GROUP BY                     |                           | 99628 |  4670K|   114M|  9996   (2)| 00:02:00 |
|*  9 |         HASH JOIN RIGHT OUTER            |                           |  1992K|    91M|  1112K|   787   (3)| 00:00:10 |
|* 10 |          TABLE ACCESS FULL               | TRN_AGREEMENT_TRANSACTION | 42045 |   615K|       |   315   (1)| 00:00:04 |
|* 11 |          TABLE ACCESS FULL               | TRN_AGREEMENT_TRANSACTION | 42045 |  1354K|       |   316   (1)| 00:00:04 |
which is this bit of code,
              select sum(WAT) w3y, fk_AGREEMENT_SERIAL FROM
                (
                  select trn.amount wat, trn.fk_AGREEMENT_SERIAL , trn.VALUE_DATE, trn.TYPE_ID, lwd.Last_withdrawal_date
                    FROM TRN_AGREEMENT_TRANSACTION trn, 
                          (select max(trn.VALUE_DATE) Last_withdrawal_date, FK_AGREEMENT_SERIAL from TRN_AGREEMENT_TRANSACTION trn where trn.TYPE_ID = 2 group by trn.FK_AGREEMENT_SERIAL)  lwd
                    where trn.FK_AGREEMENT_SERIAL = lwd.FK_AGREEMENT_SERIAL (+)
                      and trn.type_id = 2
                ) 
                where value_date between Last_withdrawal_date and sysdate
                group by fk_AGREEMENT_SERIAL
Can you look at re-writing this? For example, are you sure that the outer join is needed? Do you not get the same result with an inner join? Also, you have two GROUP BY clauses when one is enough. I see that the optimizer is managing to push the filter on VALUE_DATE into the sub-query and is doing only one GROUP BY, but the simpler you can make the code, the better chance the optimizer has.

Ideally, you would not have the aggregation in that view at all, because it is preventing the optimizer from merging the view. Is there perhaps some way to do the aggregation in the outer query, not the inner?
Re: Adding a from () to a query made it so slow [message #679864 is a reply to message #679863] Wed, 08 April 2020 05:14 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
That sub-query is getting aggregate data and joining it to ever row being aggregated - so avoiding the aggregation. That's what analytics do, something like this:
select sum(WAT) w3y, fk_AGREEMENT_SERIAL 
FROM (select trn.amount wat, trn.fk_AGREEMENT_SERIAL , trn.VALUE_DATE, trn.TYPE_ID, 
             MAX(trn_value_date) OVER (PARTITION BY trn.FK_AGREEMENT_SERIAL) AS Last_withdrawal_date
      FROM TRN_AGREEMENT_TRANSACTION trn, 
      where trn.type_id = 2
     ) 
where value_date between Last_withdrawal_date and sysdate
group by fk_AGREEMENT_SERIAL
Re: Adding a from () to a query made it so slow [message #679865 is a reply to message #679864] Wed, 08 April 2020 05:45 Go to previous messageGo to next message
John Watson
Messages: 8265
Registered: January 2010
Location: Global Village
Senior Member
Like, wow! I thought something could be done, but I could never have written that. I would be a bit worried that the predicate push might no longer work.

OraFerro, does that re-write help? What plan does it give?
Re: Adding a from () to a query made it so slow [message #679867 is a reply to message #679865] Wed, 08 April 2020 08:13 Go to previous message
OraFerro
Messages: 386
Registered: July 2011
Senior Member
@John and @Cookiemonster
Thanks a lot for you feedback, here are my findings/questions:
1- the re-write has improved the performance dramatically (1.9 sec instead of almost 10 secs).
2- I understand the justification behind replacing the sub-query with the use of analytic function but still wonder how did that cause all this difference (according to my limited understanding of how does the optimizer work). To me, if I have several tables in the from clause then as long as each from item runs fast, the overall performance should not be dramatically difference as long as the where clause is correctly linking the from clause items.
So (in my original post) if I have:
select a.key, a.d1, a.d2, a.d3, b.d4
from
 a,
 (
   d4, key
 ) b
where a.key = b.key

What is written inside (b) should not make that huge difference as long as it is already very fast. In fact my query (using sub query, and the one suggested by Cookiemonster (using analytic function) have the same performance).

Quote:
That sub-query is getting aggregate data and joining it to ever row being aggregated - so avoiding the aggregation
Yet both queries are fast already even with the aggregation!

I really need your feedback to correct my understanding and direct me to where I can read more about the way Optimizer works
3- Here is the new plan:
PLAN_TABLE_OUTPUT
Plan hash value: 4142233921
 
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |    50M|    17G|       |  1539  (23)| 00:00:19 |
|*  1 |  HASH JOIN                              |                           |    50M|    17G|       |  1539  (23)| 00:00:19 |
|   2 |   VIEW                                  |                           | 16249 |   777K|       |    44   (5)| 00:00:01 |
|   3 |    TRANSPOSE                            |                           |       |       |       |            |          |
|   4 |     SORT GROUP BY PIVOT                 |                           | 16249 |   238K|       |    44   (5)| 00:00:01 |
|   5 |      TABLE ACCESS FULL                  | N_EVENT                   | 16249 |   238K|       |    42   (0)| 00:00:01 |
|*  6 |   HASH JOIN                             |                           |  5873K|  1792M|       |  1219   (6)| 00:00:15 |
|   7 |    COLLECTION ITERATOR PICKLER FETCH    | F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|*  8 |    HASH JOIN                            |                           |  1379K|   418M|       |  1157   (3)| 00:00:14 |
|   9 |     COLLECTION ITERATOR PICKLER FETCH   | F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|* 10 |     HASH JOIN                           |                           |   323K|    97M|       |  1120   (2)| 00:00:14 |
|  11 |      COLLECTION ITERATOR PICKLER FETCH  | F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|* 12 |      HASH JOIN                          |                           | 76054 |    22M|       |  1089   (2)| 00:00:14 |
|  13 |       COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|* 14 |       HASH JOIN                         |                           | 17859 |  5441K|       |  1059   (2)| 00:00:13 |
|  15 |        COLLECTION ITERATOR PICKLER FETCH| F_AGR_TRN_TYPE_INFO       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|* 16 |        HASH JOIN                        |                           |  4194 |  1269K|       |  1029   (2)| 00:00:13 |
|* 17 |         HASH JOIN                       |                           |   190 | 53960 |       |   425   (3)| 00:00:06 |
|* 18 |          HASH JOIN                      |                           |   190 | 22230 |       |   353   (3)| 00:00:05 |
|* 19 |           HASH JOIN                     |                           |   190 | 15580 |       |   343   (2)| 00:00:05 |
|* 20 |            HASH JOIN OUTER              |                           |   159 |  9858 |       |   335   (2)| 00:00:05 |
|* 21 |             HASH JOIN                   |                           |   159 |  6360 |       |    17   (6)| 00:00:01 |
|* 22 |              TABLE ACCESS FULL          | N_AGREEMENT               |   159 |  3975 |       |    12   (0)| 00:00:01 |
|  23 |              TABLE ACCESS FULL          | N_COUNTRY                 |   206 |  3090 |       |     4   (0)| 00:00:01 |
|  24 |             VIEW                        |                           |  1897 | 41734 |       |   318   (2)| 00:00:04 |
|  25 |              HASH GROUP BY              |                           |  1897 | 28455 |       |   318   (2)| 00:00:04 |
|* 26 |               TABLE ACCESS FULL         | TRN_AGREEMENT_TRANSACTION | 42045 |   615K|       |   315   (1)| 00:00:04 |
|* 27 |            TABLE ACCESS FULL            | N_AGREEMENT_AMOUNT        |  1903 | 38060 |       |     8   (0)| 00:00:01 |
|  28 |           VIEW                          |                           |  1903 | 66605 |       |     9  (12)| 00:00:01 |
|  29 |            HASH GROUP BY                |                           |  1903 | 22836 |       |     9  (12)| 00:00:01 |
|  30 |             TABLE ACCESS FULL           | N_AGREEMENT_AMOUNT        |  2590 | 31080 |       |     8   (0)| 00:00:01 |
|  31 |          TABLE ACCESS FULL              | DAG_PROJECT               |  1907 |   311K|       |    71   (0)| 00:00:01 |
|  32 |         VIEW                            |                           |  3515 | 91390 |       |   604   (2)| 00:00:08 |
|  33 |          HASH GROUP BY                  |                           |  3515 |   205K|       |   604   (2)| 00:00:08 |
|* 34 |           HASH JOIN OUTER               |                           |  3515 |   205K|       |   603   (1)| 00:00:08 |
|* 35 |            TABLE ACCESS FULL            | N_AGREEMENT               |   159 |  3975 |       |    12   (0)| 00:00:01 |
|* 36 |            VIEW                         |                           | 42045 |  1437K|       |   590   (1)| 00:00:08 |
|  37 |             WINDOW SORT                 |                           | 42045 |   862K|  1504K|   590   (1)| 00:00:08 |
|* 38 |              TABLE ACCESS FULL          | TRN_AGREEMENT_TRANSACTION | 42045 |   862K|       |   315   (1)| 00:00:04 |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("AGR"."AGREEMENT_SERIAL"="AGREEMENT_SERIAL")
   6 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
   8 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
  10 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
  12 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
  14 - access("AGR"."AGREEMENT_SERIAL"=SYS_OP_ATG(VALUE(KOKBF$),4,5,2))
  16 - access("AGR"."AGREEMENT_SERIAL"="W3Y"."AGREEMENT_SERIAL")
  17 - access("AGR"."FK_PROJECTNUMBER0"="PRJ"."PROJECT_SERIAL")
  18 - access("AGR"."AGREEMENT_SERIAL"="AAM"."FK_AGREEMENT_SERIAL")
  19 - access("AGR"."AGREEMENT_SERIAL"="AMT"."FK_AGREEMENT_SERIAL")
  20 - access("AGR"."AGREEMENT_SERIAL"="LWD"."FK_AGREEMENT_SERIAL"(+))
  21 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
  22 - filter("AGR"."STATUS_ID"=2 AND "AGR"."TYPE_ID"=2 AND "AGR"."SECTOR_ID"=1)
  26 - filter("TRN"."TYPE_ID"=2)
  27 - filter("AMT"."STATUS"='CURRENT')
  34 - access("AGR"."AGREEMENT_SERIAL"="FK_AGREEMENT_SERIAL"(+))
  35 - filter("AGR"."STATUS_ID"=2 AND "AGR"."TYPE_ID"=2 AND "AGR"."SECTOR_ID"=1)
  36 - filter("VALUE_DATE"(+)>="LAST_WITHDRAWAL_DATE"(+) AND "VALUE_DATE"(+)<=SYSDATE@!)
  38 - filter("TRN"."TYPE_ID"=2)

Thanks again

[Updated on: Wed, 08 April 2020 08:16]

Report message to a moderator

Previous Topic: getting finacial week number
Next Topic: Subquery returns more than one row - where clause -decode
Goto Forum:
  


Current Time: Sun May 24 18:50:14 CDT 2020