Home » RDBMS Server » Performance Tuning » Help for tuning the sql Query (11.2.0.1)
| Help for tuning the sql Query [message #639742] |
Wed, 15 July 2015 03:04  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi,
Sql query
SELECT TEMP.NAME, TEMP.CODE, TEMP.ID, TEMP.C_ORDBKD, TEMP.C_NS, TEMP.C_COS, TEMP.UM_PCT, nvl(TEMP.Y_C_ORDBKD,0) Y_C_ORDBKD, nvl(TEMP.Y_C_NS,0) Y_C_NS,
nvl(TEMP.Y_C_COS,0) Y_C_COS, Case nvl(TEMP.Y_C_NS, 0) when 0 Then 0 Else ((TEMP.Y_C_NS - nvl(TEMP.Y_C_COS, 0))/TEMP.Y_C_NS)*100 End Y_UM_PCT
,nvl(TEMP.Q_C_ORDBKD,0) Q_C_ORDBKD, nvl(TEMP.Q_C_NS,0) Q_C_NS, nvl(TEMP.Q_C_COS,0) Q_C_COS
,Case nvl(TEMP.Q_C_NS, 0) when 0 Then 0 Else ((TEMP.Q_C_NS - nvl(TEMP.Q_C_COS, 0))/TEMP.Q_C_NS)*100 End Q_UM_PCT
,nvl(TEMP.M_C_ORDBKD,0) M_C_ORDBKD, nvl(TEMP.M_C_NS,0) M_C_NS, nvl(TEMP.M_C_COS,0) M_C_COS,
Case nvl(TEMP.M_C_NS, 0) when 0 Then 0 Else ((TEMP.M_C_NS - nvl(TEMP.M_C_COS, 0))/TEMP.M_C_NS)*100 End M_UM_PCT
,PTD_C_ORDBKD, PTD_C_NS, PTD_C_COS, PTD_UM_PCT, PTD_Y_C_ORDBKD, PTD_Y_C_NS, PTD_Y_C_COS, PTD_Y_UM_PCT, PTD_Q_C_ORDBKD, PTD_Q_C_NS
,PTD_Q_C_COS, PTD_Q_UM_PCT, PTD_M_C_ORDBKD, PTD_M_C_NS, PTD_M_C_COS, PTD_M_UM_PCT, EOM_PDATE
FROM
( with rd AS
( SELECT TRUNC(sysdate, 'MM') slice_end_date, TRUNC(sysdate, 'YYYY') year_start, TRUNC(sysdate, 'Q') qtr_start, TRUNC(sysdate, 'MM') month_start
FROM dual)
SELECT INV.NAME, INV.CODE, INV.ID, (P_ORDBKD) C_ORDBKD, (P_NS) C_NS, (P_COS) C_COS, ROUND(UM_PCT,2)UM_PCT
-- EOM_PDATE
,(select last_day(add_months(sysdate, -1)) from dual) EOM_PDATE
--Year to Date
,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_ORDBKD'
and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month') AND rd.year_start < s.finish_date
)Y_C_ORDBKD
,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_NS'
and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month') AND rd.year_start < s.finish_date )Y_C_NS
,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_COS'
and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month') AND rd.year_start < s.finish_date )Y_C_COS
--Quarter to Date
,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_ORDBKD'
and investment_code=inv.code and START_DATE BETWEEN trunc(trunc(sysdate,'q'),'Q') AND trunc(sysdate,'mm')-1 AND rd.year_start < s.finish_date) Q_C_ORDBKD
,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_NS'
and investment_code=inv.code and START_DATE BETWEEN trunc(trunc(sysdate,'q'),'Q') AND trunc(sysdate,'mm')-1 AND rd.year_start < s.finish_date )Q_C_NS
,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_COS'
and investment_code=inv.code and START_DATE BETWEEN trunc(trunc(sysdate,'q'),'Q') AND trunc(sysdate,'mm')-1 AND rd.year_start < s.finish_date) Q_C_COS
--Month to Date
,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_ORDBKD'
and investment_code=inv.code and START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1 AND rd.year_start < s.finish_date ) M_C_ORDBKD
,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_NS'
and investment_code=inv.code and START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1 AND rd.year_start < s.finish_date )M_C_NS
,(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_COS'
and investment_code=inv.code and START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1 AND rd.year_start < s.finish_date )M_C_COS
--PTD data todate
,(P_ORDBKD)PTD_C_ORDBKD, (P_NS )PTD_C_NS, (P_COS )PTD_C_COS, round(UM_PCT,2 )PTD_UM_PCT
--PTD Year to Date
,ROUND(FIN.p_ordbk_ytd,2) PTD_Y_C_ORDBKD, ROUND(FIN.p_ns_ytd,2) PTD_Y_C_NS, ROUND(FIN.p_cos_ytd,2) PTD_Y_C_COS, ROUND(FIN.um_pct_ytd,2) PTD_Y_UM_PCT
--PTD Quarter to Date
,FIN.p_ordbk_qtd PTD_Q_C_ORDBKD, FIN.p_ns_qtd PTD_Q_C_NS, FIN.p_cos_qtd PTD_Q_C_COS, FIN.um_pct_qtd PTD_Q_UM_PCT
--PTD Month to Date
,FIN.p_ordbk_mtd PTD_M_C_ORDBKD, FIN.p_ns_mtd PTD_M_C_NS, FIN.p_cos_mtd PTD_M_C_COS, FIN.um_pct_mtd PTD_M_UM_PCT
FROM rd
join INV_INVESTMENTS INV on 1=1
JOIN ODF_CA_PROJECT ODFP
ON INV.ID=ODFP.ID
JOIN ODF_CA_PROJFINPROPERTIES FIN
ON ODFP.ID=FIN.ID
WHERE INV.ID = &p_project_id ) TEMP;
Explain plan:
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2746247480
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 135 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 44 | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | ERI_SLICES | 1 | 44 | 7 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | ERI_SLICE_TABLE_U01 | 1 | | 6 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 44 | | |
|* 6 | FILTER | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | ERI_SLICES | 1 | 44 | 7 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | ERI_SLICE_TABLE_U01 | 1 | | 6 (0)| 00:00:01 |
| 9 | SORT AGGREGATE | | 1 | 44 | | |
|* 10 | FILTER | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | ERI_SLICES | 1 | 44 | 7 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | ERI_SLICE_TABLE_U01 | 1 | | 6 (0)| 00:00:01 |
| 13 | SORT AGGREGATE | | 1 | 44 | | |
|* 14 | FILTER | | | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | ERI_SLICES | 1 | 44 | 7 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | ERI_SLICE_TABLE_U01 | 1 | | 6 (0)| 00:00:01 |
| 17 | SORT AGGREGATE | | 1 | 44 | | |
|* 18 | FILTER | | | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | ERI_SLICES | 1 | 44 | 7 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | ERI_SLICE_TABLE_U01 | 1 | | 6 (0)| 00:00:01 |
| 21 | SORT AGGREGATE | | 1 | 44 | | |
|* 22 | FILTER | | | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | ERI_SLICES | 1 | 44 | 7 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | ERI_SLICE_TABLE_U01 | 1 | | 6 (0)| 00:00:01 |
| 25 | SORT AGGREGATE | | 1 | 44 | | |
|* 26 | FILTER | | | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | ERI_SLICES | 1 | 44 | 7 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | ERI_SLICE_TABLE_U01 | 1 | | 6 (0)| 00:00:01 |
| 29 | SORT AGGREGATE | | 1 | 44 | | |
|* 30 | FILTER | | | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | ERI_SLICES | 1 | 44 | 7 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | ERI_SLICE_TABLE_U01 | 1 | | 6 (0)| 00:00:01 |
| 33 | SORT AGGREGATE | | 1 | 44 | | |
|* 34 | FILTER | | | | | |
| 35 | TABLE ACCESS BY INDEX ROWID | ERI_SLICES | 1 | 44 | 7 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | ERI_SLICE_TABLE_U01 | 1 | | 6 (0)| 00:00:01 |
| 37 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 38 | NESTED LOOPS | | 1 | 135 | 5 (0)| 00:00:01 |
| 39 | NESTED LOOPS | | 1 | 135 | 3 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | 1 | 83 | 2 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID| ODF_CA_PROJFINPROPERTIES | 1 | 77 | 2 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | ODF_CA_PROJFINPROPERTIES_PK | 1 | | 1 (0)| 00:00:01 |
|* 43 | INDEX UNIQUE SCAN | ODF_CA_PROJECT_PK | 1 | 6 | 0 (0)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID | INV_INVESTMENTS | 1 | 52 | 1 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | INV_INVESTMENTS_PK | 1 | | 0 (0)| 00:00:01 |
| 46 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!,'fmmonth')>TRUNC(SYSDATE@!,'fmyyyy') AND
TRUNC(SYSDATE@!,'fmy')<=TRUNC(SYSDATE@!,'fmmonth'))
4 - access("INVESTMENT_CODE"=:B1 AND "S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND
"S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
filter(UPPER("SLICE_NAME")='P_ORDBKD' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND
"S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND "S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
6 - filter(TRUNC(SYSDATE@!,'fmmonth')>TRUNC(SYSDATE@!,'fmyyyy') AND
TRUNC(SYSDATE@!,'fmy')<=TRUNC(SYSDATE@!,'fmmonth'))
8 - access("INVESTMENT_CODE"=:B1 AND "S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND
"S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
filter(UPPER("SLICE_NAME")='P_NS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND
"S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND "S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
10 - filter(TRUNC(SYSDATE@!,'fmmonth')>TRUNC(SYSDATE@!,'fmyyyy') AND
TRUNC(SYSDATE@!,'fmy')<=TRUNC(SYSDATE@!,'fmmonth'))
12 - access("INVESTMENT_CODE"=:B1 AND "S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND
"S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
filter(UPPER("SLICE_NAME")='P_COS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND
"S"."FINISH_DATE">=TRUNC(SYSDATE@!,'fmy') AND "S"."FINISH_DATE"<=TRUNC(SYSDATE@!,'fmmonth'))
14 - filter(TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq')<=TRUNC(SYSDATE@!,'fmmm')-1)
16 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND
"S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
filter(UPPER("SLICE_NAME")='P_ORDBKD' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND
"START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
18 - filter(TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq')<=TRUNC(SYSDATE@!,'fmmm')-1)
20 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND
"S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
filter(UPPER("SLICE_NAME")='P_NS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND
"START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
22 - filter(TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq')<=TRUNC(SYSDATE@!,'fmmm')-1)
24 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND
"S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
filter(UPPER("SLICE_NAME")='P_COS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND
"START_DATE">=TRUNC(TRUNC(SYSDATE@!,'fmq'),'fmq') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
26 - filter(TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth')<=TRUNC(SYSDATE@!,'fmmonth')-1)
28 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND
"S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
filter(UPPER("SLICE_NAME")='P_ORDBKD' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND
"START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND
"START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
30 - filter(TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth')<=TRUNC(SYSDATE@!,'fmmonth')-1)
32 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND
"S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
filter(UPPER("SLICE_NAME")='P_NS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND
"START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND
"START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
34 - filter(TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth')<=TRUNC(SYSDATE@!,'fmmonth')-1)
36 - access("INVESTMENT_CODE"=:B1 AND "START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND
"S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND "START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
filter(UPPER("SLICE_NAME")='P_COS' AND "S"."FINISH_DATE">TRUNC(SYSDATE@!,'fmyyyy') AND
"START_DATE">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmonth') AND
"START_DATE"<=TRUNC(SYSDATE@!,'fmmonth')-1)
42 - access("FIN"."ID"=TO_NUMBER(:P_PROJECT_ID))
43 - access("ODFP"."ID"=TO_NUMBER(:P_PROJECT_ID))
45 - access("INV"."ID"=TO_NUMBER(:P_PROJECT_ID))
|
|
|
|
|
|
|
|
|
|
| Re: Help for tuning the sql Query [message #639748 is a reply to message #639745] |
Wed, 15 July 2015 04:02  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
select slice_name, count(*) from eri_slices group by slice_name;
slice_name count(*)
------------------------
p_ns_cs 125312
p_ns_hw 125312
c_ordbkd_sw 125312
c_ns_cs 125312
p_act_cst_cs 125312
p_ordbkd_cs 125312
c_act_cst_cs 125312
p_act_cst 125312
c_ns_hw 125312
c_ns 125312
p_ns 125312
p_act_cst_sw 125312
c_cos 125312
p_cos_cs 125312
c_act_cst 125312
c_act_cst_hw 125312
c_act_cst_sw 125312
p_act_cst_hw 125312
c_cos_cs 125312
c_cos_hw 125312
c_cos_sw 125312
p_ns_sw 125312
p_ordbkd_hw 125312
p_cos_hw 125312
c_ordbkd_hw 125312
p_cos_sw 125312
p_ordbkd_sw 125312
c_ordbkd_cs 125312
c_ns_sw 125312
p_ordbkd 125312
c_ordbkd 125312
p_cos 125312
32 rows selected
--Year to Date
select COUNT(*) from eri_slices s, inv_investments inv
where upper(slice_name) in ('P_ORDBKD', 'P_NS', 'P_COS')
and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month') AND TRUNC(sysdate, 'YYYY') < s.finish_date;
COUNT(*)
------------
89802
select count(*) from eri_slices
COUNT(*)
-------
4009984
|
|
|
|
| Re: Help for tuning the sql Query [message #639750 is a reply to message #639748] |
Wed, 15 July 2015 04:37 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'd try joining the main query to eri_slices restricting it to the three slice_names and the maximum date range.
Then get rid of all sub-selects and do a sum(case) where the case does all the restrictions the sub-select where clause was doing.
e.g.
SELECT .........
,sum(Case WHEN slice_name ='p_cos'
AND START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1
AND rd.year_start < s.finish_date
then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End)
FROM rd
join INV_INVESTMENTS INV on 1=1
JOIN ODF_CA_PROJECT ODFP
ON INV.ID=ODFP.ID
JOIN ODF_CA_PROJFINPROPERTIES FIN
ON ODFP.ID=FIN.ID
JOIN eri_slices e ON e.investment_code = inv.code
WHERE INV.ID = &p_project_id
AND e.slice_name IN ('p_ordbkd', 'p_ns', 'p_cos')
AND START_date > LEAST(trunc(add_months(sysdate,-1),'month'), TRUNC(SYSDATE,'y'))
GROUP BY ......
) TEMP;
You can fill in the rest
|
|
|
|
| Re: Help for tuning the sql Query [message #639753 is a reply to message #639750] |
Wed, 15 July 2015 05:21 |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi,
I didnot understand the conditions like
in sum clause
AND START_DATE BETWEEN trunc(add_months(sysdate,-1),'month') AND trunc(sysdate,'month')-1
in where clause
AND START_date > LEAST(trunc(add_months(sysdate,-1),'month'), TRUNC(SYSDATE,'y'))
But in previous query:
(select sum((Case WHEN rd.year_start < s.finish_date then s.slice * (LEAST(s.finish_date, rd.slice_end_date) - GREATEST(s.start_date, rd.year_start)) End))
from eri_slices s where upper(slice_name)='P_COS'
and investment_code=inv.code and s.FINISH_DATE BETWEEN TRUNC(SYSDATE,'y') AND TRUNC(SYSDATE,'month') AND rd.year_start < s.finish_date )
Please clarify.
|
|
|
|
| Re: Help for tuning the sql Query [message #639759 is a reply to message #639753] |
Wed, 15 July 2015 05:38 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You've got three date ranges.
If you join to the table once then you need to ensure that all the records that can fall into the three date ranges are picked up by the join.
That's what the least clause I wrote does (I assume there are no eri_slices records that have future dates) - it specifies the oldest possible from date that any of the three dates ranges can use.
Having done that you need to write CASE statements that only sum the eri_slice records that fall into the appropriate date range. The example I used was from the Month to Date set.
The CASE conditions for the other two sets will be different.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 13:11:06 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
