SQL:
select ssem.ssem_bl_no,
ssem.ssem_transport_mode_code,
ssem.ssem_transfer_code,
spci.spci_cnt_no,
spci.spci_cnt_size,
spci.spci_cnt_type,
spci.spci_cnt_operator_code,
spci.spci_cnt_status,
spci.spci_seal_no,
(case when td.rc >0 then 'Y' else 'N' end) spgi_is_danger_cargo_flag,
ssem.ssem_fcl_lcl_flag,
ssem.ssem_main_bl_no,
ssem.ssem_carrier_id
FROM (select count(*) rc
from SCargoInfo
where SPGI_IS_DANGER_CARGO_FLAG='Y') td,
scontainerinfo spci,
sexportmanifest ssem
where ssem.ssem_exp_bl_id = spci.spci_exp_bl_id
and ssem.ssem_document_type = 0
and ssem_org_id='139'
and (ssem.ssem_sc_before_after_flag = 0 or ssem.ssem_sc_before_after_flag =2 or ssem.ssem_sc_before_after_flag = 4)
and ssem.ssem_voyage_id = '2492'
and ssem.ssem_carrier_id = 'EDDD00000013'SCargoInfo表有300W数据,该表的SPGI_IS_DANGER_CARGO_FLAG字段只有2个值,这时建立索引是没有意义的,我只想判断该字段是不是有Y的值,怎么写才快,这样肯定是快不了的,用了20S。
select ssem.ssem_bl_no,
ssem.ssem_transport_mode_code,
ssem.ssem_transfer_code,
spci.spci_cnt_no,
spci.spci_cnt_size,
spci.spci_cnt_type,
spci.spci_cnt_operator_code,
spci.spci_cnt_status,
spci.spci_seal_no,
(case when td.rc >0 then 'Y' else 'N' end) spgi_is_danger_cargo_flag,
ssem.ssem_fcl_lcl_flag,
ssem.ssem_main_bl_no,
ssem.ssem_carrier_id
FROM (select count(*) rc
from SCargoInfo
where SPGI_IS_DANGER_CARGO_FLAG='Y') td,
scontainerinfo spci,
sexportmanifest ssem
where ssem.ssem_exp_bl_id = spci.spci_exp_bl_id
and ssem.ssem_document_type = 0
and ssem_org_id='139'
and (ssem.ssem_sc_before_after_flag = 0 or ssem.ssem_sc_before_after_flag =2 or ssem.ssem_sc_before_after_flag = 4)
and ssem.ssem_voyage_id = '2492'
and ssem.ssem_carrier_id = 'EDDD00000013'SCargoInfo表有300W数据,该表的SPGI_IS_DANGER_CARGO_FLAG字段只有2个值,这时建立索引是没有意义的,我只想判断该字段是不是有Y的值,怎么写才快,这样肯定是快不了的,用了20S。
select count(*) rc
from SCargoInfo
where SPGI_IS_DANGER_CARGO_FLAG='Y' and rownum=1