SQL Query Optimization
Hi ,i need help below query to execute more time .am new to sql server query. subquery getting more to execute .how to use inner join.
SELECT
sysdate currentdate,
sv.smart_num,
sv.version_num,
sv.smart_type_code,
s.part_sakey,
sv.replenish_qty,
rr.requested_dts,
rr.last_updated_dts,
rr.status_code,
rr.request_num,
p.prefix_code,
p.base_code,
p.suffix_code,
p.control_code,
p.part_name,
d.uid_addr,
sv.call_trigger_qty,
srt.reason_desc as color
FROM
sm_smart_version sv,
SM_PROCG_DEVICE pd,
sm_device d,
sm_smart s,
sm_bl_part p ,
(select * from
( select
request_num,
smart_num,
version_num,
status_code,
request_cont_cnt,
requested_dts,
last_updated_dts
from
sm_curr_request c
union
select
request_num,
smart_num,
version_num,
status_code,
request_cont_cnt,
requested_dts,
null last_updated_dts
from
sm_hist_request h
)
where
request_num in (
select
max(request_num)
from
(
select
request_num,
smart_num,
version_num,
status_code,
request_cont_cnt
from
sm_curr_request c
union
select
request_num,
smart_num,
version_num,
status_code,
request_cont_cnt
from
sm_hist_request h
)
group by
smart_num,
version_num
)
)
rr,
sm_reason_type srt
WHERE
trunc(sysdate) between trunc(sv.eff_from_date)
and trunc(sv.eff_to_date)
and rr.smart_num (+) = sv.smart_num
and rr.version_num (+) = sv.version_num
and s.smart_num = sv.smart_num
and s.part_sakey = p.part_sakey
and sv.smart_num = pd.smart_num (+)
and sv.version_num = pd.version_num (+)
and pd.device_sakey = d.device_sakey (+)
and (
s.color_code = srt.reason_code (+)
and srt.reason_type_code (+) = 'VCBC'
)
and sv.smart_num in (4155,
13667,
4929,
3155);