Home » RDBMS Server » Performance Tuning » SQL Query with aggregate function taking long time to execute
| SQL Query with aggregate function taking long time to execute [message #158103] |
Wed, 08 February 2006 08:56 |
najnarin
Messages: 1
Registered: February 2006
Location: UK
|
Junior Member |
|
|
Hi
Need help! SQL query with aggregate functions (SUM) is taking long time to execute. Can any one please tell me how can I optimize the SQL so that it takes less time to execute?
The below SQL has sub queries as well, it has many aggregate (SUM) functions.
If I just relace all these aggregate functions coloured in Red with "Count(*)" it executes in seconds.
Below is the SQL,
select
sit_sit_code
,CARD_CAGE
,LAND_MAX C7_MAX
,AIR_SIDE
,sp_id
,sum(alloc_done * is_unknown) IMP_UNK
,sum(alloc_done * is_bsc) IMP_AIR
,sum(alloc_done * is_c7) IMP_C7
,sum(alloc_done * is_unknown) + sum(alloc_done * is_bsc) + sum(alloc_done * is_c7) TOT_IMP
,LAND_MAX + AIR_SIDE CAPACITY
,sum(IN_ALLOC_QUEUE * is_bsc ) - sum( in_rec_queue * is_bsc ) Q_AIR
,sum(IN_ALLOC_QUEUE * is_c7 ) - sum( in_rec_queue * is_c7 ) Q_C7
,sum(IN_ALLOC_QUEUE * is_unknown ) - sum( in_rec_queue * is_unknown ) Q_UNK
,AIR_SIDE - sum(alloc_done * is_bsc) AIR_FREE
,LAND_MAX - sum(alloc_done * is_c7) C7_FREEfrom
(
select
decode(conn_to,'',1,0) IS_UNKNOWN
,decode(instr(decode(conn_to,'',' ',conn_to),'BSC'),0,0,1) IS_BSC
,decode(instr(decode(conn_to,'',' ',conn_to),'BSC'),0,DECODE( conn_to,'',0,1 ),0) IS_C7
,decode(alloc_status
,2,1
,5,decode(tsc_actual_completion_dt,null,0,1)
,0) ALLOC_DONE
,decode(alloc_status
,5,decode(tsc_actual_completion_dt,null,1,0)
,0
) IN_ALLOC_QUEUE
,decode(rec_status
,5,decode(tsc_cancellation_dt,null,1,0)
,0
) IN_REC_QUEUE
, ENT.*
from
(
select *
from
(
SELECT
rat.rat_status_ind alloc_status
,rec_rat.rat_status_ind rec_status
,tsc.tsc_actual_completion_dt
,tsc.tsc_cancellation_dt
,NEQP_SEQ_NUM TS2_NEQP_NEQP_SEQ_NUM
,tsa.TS2_TS2_TIME_SLOT_SEQ_NUM
,tsa.TSA_SUBJ_TO_RECOV_FLAG
,tsa.tsa_id
,mp_inet.GET_INET_ASSOC_FOR_TSA ( tsa_id) CONN_TO
,ENT.*
FROM
(
SELECT
sud.sit_sit_code
,sud.neqd_id neqd_id
,sud.port NEQP_SEQ_NUM
,substr(mp_ports.EQPT_STORAGE_POSITION(sud.probe_sp_id)|| ' ' ||sud.cage,1,255) CARD_CAGE
,sud.cagesp_id sp_id
,mtre_max.table2keyvalue1 LAND_MAX
,TO_NUMBER(mtre_max.table2keyvalue2 ) AIR_SIDE
FROM
MAG_TABLE_RELATION_ENTRY MTRE_MAX
,mp_inet_port_view sud
WHERE
sud.inetcardtype = '2G'
AND MTRE_MAX.MTRS_MTRS_ID (+)= -20
AND MTRE_MAX.table1keyvalue1 (+)= sud.cagesp_id
ORDER BY sud.sit_sit_code
) ENT
,mp_tasks rec_rat
,mp_tasks rat
,mp_slot_connections tsc
,MP_TIME_SLOT_ALLOCATIONS tsa
WHERE
tsa.TS2_NEQP_NEQD_NEQD_ID (+)= neqd_id
AND tsa.TS2_NEQP_NEQP_SEQ_NUM (+)= neqp_seq_num
AND tsa.TSA_SUBJ_TO_RECOV_FLAG (+)< 2
AND tsa.TSA_NOT_DELETED_FLAG (+)= 1
and tsc.tsc_id (+) = tsa.tsc_tsc_id
and tsc.tsc_not_deleted_flag (+)= 1
and rat.rat_id (+)= tsa.rat_rat_id
and rec_rat.rat_id (+)= tsa.rat_rat_id_may_be_the_subject_
)where tsa_id is not null and tsc_cancellation_dt is null
)ENT
)ENT2
GROUP BY
sit_sit_code
,CARD_CAGE
,LAND_MAX
,AIR_SIDE
,sp_id
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 14:21:07 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
