现在是10秒,希望2秒左右select
rtct.distance_from as distance_from,
rtct.distance_to as distance_to,
supp.scac_id as scac_id,
rtct.scac_suffix as scac_suffix,
supp.supplier_name as supplier_name,
rt.transport_mode as transport_mode,
rt.rate as rate,
rt.origin as origin,
rt.destination as destination,
rtct.weight_to as weight_to,
rtct.weight_from as weight_from,
rt.time_mode as time_mode,
upsz5.ups_zone_num as ups_zone_num,
rt.transit_tme_from as transit_tme_from,
rt.transit_time_to as transit_time_to,
rtct.arrival_time as arrival_time,
rt.structure_id as structure_id,
rt.discount_percent as discount_percent,
cntr.service_file_ind as service_file_ind,
cntr.am_arrival_time as am_arrival_time,
cntr.pm_arrival_time as pm_arrival_time,
cntr.def_arrival_time as def_arrival_time,
rtct.rate_tt_cat_desc as rate_tt_cat_desc,
cntr.contract_id as contract_id,
cntr.contract_type as contract_type,
cntr.mileage_calc_cde as mileage_calc_cde,
rtst.distance_uom as distance_uom,
rtst.round_rule_code as round_rule_code,
stm.cost_multiplier as cost_multiplier,
rtct.minimum_ind as minimum_ind,
rtct.maximum_ind as maximum_ind,
rtct.rate_uom as rate_uom,
rt.delivery_type as delivery_type,
rt.two_way_ind as two_way_ind,
cntr.contract_desc as contract_desc,
rtst.distance_p_time as distance_p_time,
rtst.time_uom as time_uom,
supp.supp_cntct_phone as supp_cntct_phone,
stm.dim_weight_factr as dim_weight_factor,
stm.dim_max_weight as dim_max_weight,
stm.dim_max_dim as dim_max_singleDim,
stm.dim_max_comb_dim as dim_max_combineDim,
ttd.tt_arrival_time as tt_arrival_time,
ttd.tt_days as tt_days,
ttd.tt_uom as tt_uom
FROM
wwddb.cdbtcntr cntr
LEFT JOIN wwddb.cdbtsupp supp on supp.scac_id = cntr.scac_id
LEFT JOIN wwddb.cdbtcige ocige on ocige.contract_id = cntr.contract_id
LEFT JOIN wwddb.cdbtrt rt on rt.origin = ocige.geography_id
and rt.contract_id = ocige.contract_id
LEFT JOIN wwddb.cdbtupsz5 upsz5 on rt.contract_id = upsz5.contract_id
and rt.structure_id = upsz5.structure_id
LEFT JOIN wwddb.cdbtrtst rtst on rtst.contract_id = cntr.contract_id
LEFT JOIN wwddb.cdbtstm stm on stm.contract_id = rtst.contract_id
and stm.structure_id = rtst.structure_id
LEFT JOIN wwddb.cdbtrtct rtct on rtct.contract_id = rtst.contract_id
and rtct.structure_id = rtst.structure_id
and rt.contract_id = rtct.contract_id
and rt.structure_id = rtct.structure_id
and rt.rate_tt_category = rtct.rate_tt_category
LEFT JOIN wwddb.cdbtttd ttd on rt.contract_id = ttd.contract_id
and rt.structure_id = ttd.structure_id
WHERE
cntr.contr_eff_date <= current date
and cntr.contr_exp_date >= current date
and (rt.expiration_date >= current date or rt.expiration_date is null)
and rt.effective_date <= current date
and cntr.mileage_calc_cde = 'U'
and ((cntr.contract_type = ? and ? ^= ' ') or (? = ' '))
and ((supp.scac_id = ? and ? ^= ' ') or (? = ' '))
and ((rtct.scac_suffix = ? and ? ^= ' ') or (? = ' '))
and ((rt.transport_mode = ? and ? ^= ' ') or (? = ' '))
and int(ttd.destination_zip_lo) <= ?
and int(ttd.destination_zip_hi) >= ?
and ((int(ocige.from_postal_code) <= ? and int(ocige.to_postal_code) >= ?) or (int(ocige.from_postal_code) = ? and ocige.to_postal_code = ' '))
and int(upsz5.origin_zip_lo) <= ?
and int(upsz5.origin_zip_hi) >= ?
and int(upsz5.destination_zip_lo) <= ?
and int(upsz5.destination_zip_hi) >= ?
and ((stm.minimum_weight <= ? or stm.minimum_weight = 0)
and (stm.maximum_weight >= ? or stm.maximum_weight = 0)
and (stm.maximum_length >= ? or stm.maximum_length = 0)
and (stm.maximum_width>= ? or stm.maximum_width = 0)
and (stm.maximum_height>= ? or stm.maximum_height = 0)
and (stm.max_girth >= ? or stm.max_girth = 0)
and (stm.maximum_dimensn >= ? or stm.maximum_dimensn = 0)
and (stm.max_combined_dim >= ? or stm.max_combined_dim = 0))
and rtct.weight_from <= ? and rtct.weight_to >= ?
and ((rt.destination = 'ZONE' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE ' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE 10' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE10' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE 20' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE20' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE 1' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE1' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE 2' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE2' || char(upsz5.ups_zone_num)))
rtct.distance_from as distance_from,
rtct.distance_to as distance_to,
supp.scac_id as scac_id,
rtct.scac_suffix as scac_suffix,
supp.supplier_name as supplier_name,
rt.transport_mode as transport_mode,
rt.rate as rate,
rt.origin as origin,
rt.destination as destination,
rtct.weight_to as weight_to,
rtct.weight_from as weight_from,
rt.time_mode as time_mode,
upsz5.ups_zone_num as ups_zone_num,
rt.transit_tme_from as transit_tme_from,
rt.transit_time_to as transit_time_to,
rtct.arrival_time as arrival_time,
rt.structure_id as structure_id,
rt.discount_percent as discount_percent,
cntr.service_file_ind as service_file_ind,
cntr.am_arrival_time as am_arrival_time,
cntr.pm_arrival_time as pm_arrival_time,
cntr.def_arrival_time as def_arrival_time,
rtct.rate_tt_cat_desc as rate_tt_cat_desc,
cntr.contract_id as contract_id,
cntr.contract_type as contract_type,
cntr.mileage_calc_cde as mileage_calc_cde,
rtst.distance_uom as distance_uom,
rtst.round_rule_code as round_rule_code,
stm.cost_multiplier as cost_multiplier,
rtct.minimum_ind as minimum_ind,
rtct.maximum_ind as maximum_ind,
rtct.rate_uom as rate_uom,
rt.delivery_type as delivery_type,
rt.two_way_ind as two_way_ind,
cntr.contract_desc as contract_desc,
rtst.distance_p_time as distance_p_time,
rtst.time_uom as time_uom,
supp.supp_cntct_phone as supp_cntct_phone,
stm.dim_weight_factr as dim_weight_factor,
stm.dim_max_weight as dim_max_weight,
stm.dim_max_dim as dim_max_singleDim,
stm.dim_max_comb_dim as dim_max_combineDim,
ttd.tt_arrival_time as tt_arrival_time,
ttd.tt_days as tt_days,
ttd.tt_uom as tt_uom
FROM
wwddb.cdbtcntr cntr
LEFT JOIN wwddb.cdbtsupp supp on supp.scac_id = cntr.scac_id
LEFT JOIN wwddb.cdbtcige ocige on ocige.contract_id = cntr.contract_id
LEFT JOIN wwddb.cdbtrt rt on rt.origin = ocige.geography_id
and rt.contract_id = ocige.contract_id
LEFT JOIN wwddb.cdbtupsz5 upsz5 on rt.contract_id = upsz5.contract_id
and rt.structure_id = upsz5.structure_id
LEFT JOIN wwddb.cdbtrtst rtst on rtst.contract_id = cntr.contract_id
LEFT JOIN wwddb.cdbtstm stm on stm.contract_id = rtst.contract_id
and stm.structure_id = rtst.structure_id
LEFT JOIN wwddb.cdbtrtct rtct on rtct.contract_id = rtst.contract_id
and rtct.structure_id = rtst.structure_id
and rt.contract_id = rtct.contract_id
and rt.structure_id = rtct.structure_id
and rt.rate_tt_category = rtct.rate_tt_category
LEFT JOIN wwddb.cdbtttd ttd on rt.contract_id = ttd.contract_id
and rt.structure_id = ttd.structure_id
WHERE
cntr.contr_eff_date <= current date
and cntr.contr_exp_date >= current date
and (rt.expiration_date >= current date or rt.expiration_date is null)
and rt.effective_date <= current date
and cntr.mileage_calc_cde = 'U'
and ((cntr.contract_type = ? and ? ^= ' ') or (? = ' '))
and ((supp.scac_id = ? and ? ^= ' ') or (? = ' '))
and ((rtct.scac_suffix = ? and ? ^= ' ') or (? = ' '))
and ((rt.transport_mode = ? and ? ^= ' ') or (? = ' '))
and int(ttd.destination_zip_lo) <= ?
and int(ttd.destination_zip_hi) >= ?
and ((int(ocige.from_postal_code) <= ? and int(ocige.to_postal_code) >= ?) or (int(ocige.from_postal_code) = ? and ocige.to_postal_code = ' '))
and int(upsz5.origin_zip_lo) <= ?
and int(upsz5.origin_zip_hi) >= ?
and int(upsz5.destination_zip_lo) <= ?
and int(upsz5.destination_zip_hi) >= ?
and ((stm.minimum_weight <= ? or stm.minimum_weight = 0)
and (stm.maximum_weight >= ? or stm.maximum_weight = 0)
and (stm.maximum_length >= ? or stm.maximum_length = 0)
and (stm.maximum_width>= ? or stm.maximum_width = 0)
and (stm.maximum_height>= ? or stm.maximum_height = 0)
and (stm.max_girth >= ? or stm.max_girth = 0)
and (stm.maximum_dimensn >= ? or stm.maximum_dimensn = 0)
and (stm.max_combined_dim >= ? or stm.max_combined_dim = 0))
and rtct.weight_from <= ? and rtct.weight_to >= ?
and ((rt.destination = 'ZONE' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE ' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE 10' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE10' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE 20' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE20' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE 1' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE1' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE 2' || char(upsz5.ups_zone_num))
or (rt.destination = 'ZONE2' || char(upsz5.ups_zone_num)))
有同感,这么复杂的SQL语句,光理解他的意思就要花上一段时间,况且又没有相关环境,表结构说明,统计信息,执行计划,语句含义和目的,从10S到2S也相对来说不是那么大的提升