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 |
|
OraFerro
Messages: 433 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 1631 times)
|
|
|
|
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 |
|
OraFerro
Messages: 433 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 |
John Watson
Messages: 8937 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 |
cookiemonster
Messages: 13925 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 #679867 is a reply to message #679865] |
Wed, 08 April 2020 08:13 |
|
OraFerro
Messages: 433 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
|
|
|
Goto Forum:
Current Time: Mon Jun 17 00:51:51 CDT 2024
|