仍然不行的。
NO_COST_BASED_QUERY_TRANSFORMATION.log (125.1 KB)
话说，老师，这个问题可以用 hint 解决吗？
这个是咨询sql侧同学给的建议
加上NO_COST_BASED_QUERY_TRANSFORMATION
再加上no_rewrite 应该可以了，你试试
老师，好像还是不行。
是不是我写得不对呢？我将我全部执行过程已经放在文本。
SELECT /*+ NO_COST_BASED_QUERY_TRANSFORMATION NO_REWRITE */
kh.id_ AS id,
kh.is_dzh_ AS isDzh,
kh.zzsnslx_ AS zzsnslx,
kh.level_ AS level,
kh.fw_status_ AS fwStatus,
@currentMonth:=DATE_FORMAT(NOW(), '%Y%m') currKjQj,
@fwgdLrDate:=(SELECT
... ...
no_rewrite& no_cost.log (117.9 KB)
把子查询里面都加上no_rewrite ， in的这个谓词推到子查询里面了
老师，我在每个SELECT 都加上了 no rewrite 也不行啊。
还是写错了吗？
SELECT /*+ NO_COST_BASED_QUERY_TRANSFORMATION NO_REWRITE */
kh.id_ AS id,
kh.is_dzh_ AS isDzh,
kh.zzsnslx_ AS zzsnslx,
kh.level_ AS level,
kh.fw_status_ AS fwStatus,
@currentMonth:=DATE_FORMAT(NOW(), '%Y%m') currKjQj,
@fwgdLrDate:=(SELECT /*+ NO_REWRITE */
MIN(tr.create_date_)
FROM
csp_ht_fwgd fwgd
INNER JOIN
csp_ht_fwgd_transfer tr ON fwgd.id_ = tr.new_ht_fwgd_id_
WHERE
fwgd.kh_khxx_id_ = kh.id_
AND tr.new_zj_zjxx_id_ = 'h0000000000000498723430515097600') AS fwgdLrDate,
@fwqxZ:=MAX(IF(ht.status_ != '4', '', ht.fwqx_z_)) AS fwqxZ,
@fwqxQ:=IFNULL((SELECT /*+ NO_REWRITE */
htxx.fwqx_q_
FROM
ftsp_ht_htxx htxx
WHERE
htxx.id_ = hf.ht_htxx_id_),
MIN(ht.fwqx_q_)) AS fwqxQ,
@shDate:=MIN(ht.sh_date_) AS shDate,
@tkwcRq:=(SELECT /*+ NO_REWRITE */
MAX(tk.tkwc_rq_)
FROM
ftsp_ht_htxx ht1
LEFT JOIN
ftsp_ht_tkxx tk ON ht1.id_ = tk.ht_id_ AND tk.tkzt_ = 3
WHERE
ht1.kh_khxx_id_ = kh.id_) tkwcRq,
@wqWcrq:=(SELECT /*+ NO_REWRITE */
IF(MAX(mx.sycs_) <= 0
AND GROUP_CONCAT(DISTINCT taskfwsx.status_) = 8,
MAX(taskfwsx.confirm_time_),
IF(MAX(mx.sycs_) <= 0
AND GROUP_CONCAT(DISTINCT taskfwsx.status_) IS NULL,
mx.finish_date_,
NULL))
FROM
ftsp_ht_fwsxmx mx
INNER JOIN
ftsp_wq_fwsx wqfwsx ON wqfwsx.id_ = mx.wq_fwsx_id_
AND wqfwsx.wqlx_ != 0
LEFT JOIN
ftsp_wq_task_fwsx taskfwsx ON taskfwsx.ht_fwsxmx_id_ = mx.id_
LEFT JOIN
ftsp_ht_tkxx_fwsx tf ON tf.ht_fwsxmx_id_ = mx.id_
AND tf.tkzt_ = 3
WHERE
mx.ht_htxx_id_ = ht.id_
AND mx.zf_zt_ = 0
AND tf.id_ IS NULL) wqWcRq,
@initialStatus:=IF((init.initial_status_ = '1')
AND EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_khfw khfw
WHERE
kh.id_ = khfw.kh_khxx_id_ AND yjjz_ = 1)
AND EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_htxx ht
WHERE
ht.kh_khxx_id_ = kh.id_
AND ht.status_ = 4
AND ht.htlx_ IN ('20' , '30', '40')
AND kh.is_dzh_ != 0),
1,
IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_khfw khfw
WHERE
kh.id_ = khfw.kh_khxx_id_ AND yjjz_ = 1)
AND EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_htxx ht
WHERE
ht.kh_khxx_id_ = kh.id_
AND ht.status_ = 4
AND ht.htlx_ IN ('20' , '30', '40')
AND kh.is_dzh_ != 0),
0,
NULL)) AS initialStatus,
IF(ht.id_ IS NOT NULL,
(SELECT /*+ NO_REWRITE */
pj.fxkh_
FROM
ftsp_kh_fwda_khpj pj
WHERE
pj.kh_khxx_id_ = kh.id_),
NULL) AS fxStatus,
MAX(IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_htxx ht1
LEFT JOIN
ftsp_ht_tkxx tk ON tk.ht_id_ = ht1.id_
LEFT JOIN
ftsp_ht_tkxx_tkyy tkyy ON tk.id_ = tkyy.ht_tkxx_id_
LEFT JOIN
ftsp_ht_xfgt xf ON ht1.id_ = xf.ht_htxx_id_
LEFT JOIN
ftsp_kh_tyxx ty ON ty.kh_khxx_id_ = ht1.kh_khxx_id_
WHERE
ht1.id_ = ht.id_
AND (tk.lczt_ = '5'
AND tkyy.tkyy_code_value_ IN ('0409' , '0410', '0411', '0412')
OR (xf.xfyx_ = '0' AND xf.bxfzt_ = '3'
AND ty.ty_yy_label_ IN ('0409' , '0410', '0411', '0412')))),
1,
0)) AS gwStatus,
@zhStatus:=IF(hf.wxjz_ = '1' OR hf.yjjz_ = '1',
1,
0) AS zhStatus,
@yjjz:=IF(hf.wxjz_ = '1',
2,
IF(hf.yjjz_ = '1', 1, 0)) AS yjjz,
zh.dfp_zh_ AS dfpZh,
zh.dfp_jz_ AS dfpJz,
zh.dfp_status_ AS dfpStatus,
IF(zt.id_ IS NOT NULL, 1, 0) AS defaultZt,
IF(@yjjz != 0,
IF(DATE_FORMAT(hf.yjjz_date_, '%Y%m') = @currentMonth,
@yjjz,
NULL),
NULL) AS dyYzhFs,
IF(@zhStatus = 0,
IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_khfw_error er
WHERE
er.kh_khxx_id_ = kh.id_),
1,
0),
NULL) AS whWzhyyStatus,
TIMESTAMPDIFF(DAY,
@shDate,
IFNULL(hf.yjjz_date_, NOW())) AS zhZq,
@scgt:=IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_khfw hf1
WHERE
hf1.kh_khxx_id_ = kh.id_
AND hf1.scgt_ = 1),
1,
0),
IF(@zhStatus = 0,
IF(zh.hs_qylxr_type_ IN ('1' , '2', '3')
AND (EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_hsxx hsxx
WHERE
hsxx.kh_khxx_id_ = kh.id_)
OR NOT EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_htxx ht
INNER JOIN
ftsp_ht_fwsxmx mx ON ht.id_ = mx.ht_htxx_id_
LEFT JOIN
ftsp_ht_tkxx_fwsx htf ON mx.id_ = htf.ht_fwsxmx_id_
AND htf.tkzt_ = 3
LEFT JOIN
ftsp_ht_hsxx hs ON hs.kh_khxx_id_ = ht.kh_khxx_id_
AND ht.id_ = hs.ht_htxx_id_
WHERE
mx.kh_khxx_id_ = kh.id_
AND ht.status_ IN (4)
AND htf.id_ IS NULL
AND hs.hs_type_ IS NULL)),
2,
IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_khfw_error er
JOIN
ftsp_infra_code_value value ON er.code_ = value.value1_
AND value.infra_code_type_id_ = 'h0000000000000033091146981793793'
WHERE
er.kh_khxx_id_ = kh.id_
AND value.status_ = '1'
AND value.value1_ IN ('E031' , 'E100', 'E014', 'E036', 'E103')
AND er.deal_progress_ NOT IN ('3' , '6', '5')
AND er.is_jfyc_ = '1'),
0,
1)),
0) AS dzhScgtStatus,
zh.hs_qylxr_date_ dzhScgtDate,
(SELECT /*+ NO_REWRITE */
MIN(hsxx.create_date_)
FROM
ftsp_ht_hsxx hsxx
WHERE
hsxx.kh_khxx_id_ = kh.id_) AS hsHtxxDate,
IF(@yjjz = 1,
IF(init.cwscgt_ = 1, 2, 1),
0) AS dgwScgtStatus,
init.cwscgt_date_ AS csScgtDate,
hf.yjjz_date_ AS zhDate,
IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_kh_bq
WHERE
infra_khbq_id_ = 'h0000000000000417138514390507521'
AND kh_khxx_id_ = kh.id_),
1,
0) AS tyd,
CASE
WHEN hf.wxjz_ = '1' THEN 4
WHEN
@yjjz = '1'
THEN
CASE
WHEN
zt.id_ IS NOT NULL
AND @initialStatus = '1'
THEN
7
WHEN
zt.id_ IS NOT NULL
AND IFNULL(@initialStatus, '0') != '1'
THEN
6
WHEN zt.id_ IS NULL THEN 5
END
WHEN
kh.is_dzh_ = '1'
AND IFNULL(hf.yjjz_, '0') = '0'
THEN
1
WHEN
IFNULL(kh.is_dzh_, '0') = '0'
AND IFNULL(hf.wxjz_, '0') = '0'
THEN
2
END AS zhjd,
IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_htxx ht1
WHERE
ht1.kh_khxx_id_ = kh.id_
AND ht1.status_ = 4
AND ((ht1.qd_sc_ IS NOT NULL
AND ht1.qd_sc_ > 0)
OR (ht1.cxsc_ IS NOT NULL AND ht1.cxsc_ > 0)
OR (ht1.zssc_ IS NOT NULL AND ht1.zssc_ > 0)
OR (ht1.lzsc_ IS NOT NULL AND ht1.lzsc_ > 0))),
1,
0) AS dzlzSc,
IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_fwsxmx mx
INNER JOIN
ftsp_wq_fwsx wf ON wf.id_ = mx.wq_fwsx_id_
AND wf.kind_ IN ('04' , '02', '05')
LEFT JOIN
ftsp_ht_tkxx_fwsx tk ON mx.id_ = tk.ht_fwsxmx_id_
AND tk.tkzt_ = 3
WHERE
tk.id_ IS NULL
AND mx.kh_khxx_id_ = kh.id_
AND (mx.finish_status_ != 1
AND mx.zf_zt_ != 1
AND (mx.ht_htxx_id_ = '00000000000000000000000000000000'
OR (EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_htxx h
WHERE
h.id_ = mx.ht_htxx_id_
AND h.status_ = '4'))))),
1,
0) AS fwsxType,
kh.ht_fw_zt_ htFwZt,
IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_htxx ht1
WHERE
ht1.kh_khxx_id_ = kh.id_
AND ht1.status_ IN (4 , 8)),
1,
0) AS khzhStatus,
EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_kh_pg pg
INNER JOIN
ftsp_infra_user u ON u.id_ = pg.infra_user_id_
WHERE
u.is_delete_ = '0'
AND u.role_code_ LIKE CONCAT('%', 'PORTAL_ZBKJ_GWS', '%')
AND pg.kh_khxx_id_ = kh.id_) AS zzkj,
EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_kh_pg pg
INNER JOIN
ftsp_infra_user u ON u.id_ = pg.infra_user_id_
WHERE
u.is_delete_ = '0'
AND u.role_code_ LIKE CONCAT('%', 'PORTAL_CSGW_GWS', '%')
AND pg.kh_khxx_id_ = kh.id_) AS csgw,
(SELECT /*+ NO_REWRITE */
jcxx.sfzxh_
FROM
ftsp_kh_jcxx jcxx
WHERE
jcxx.kh_khxx_id_ = kh.id_) AS zxh,
hf.fw_lx_ fwLx,
DATE_FORMAT(ty_date_, '%Y%m') tyDate,
zh.jfyc_status_ jfycStatus,
IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_khfw_error er
JOIN
ftsp_infra_code_value value ON er.code_ = value.value1_
AND value.infra_code_type_id_ = 'h0000000000000033091146981793793'
WHERE
er.kh_khxx_id_ = kh.id_
AND value.status_ = '1'
AND value.value1_ IN ('E037' , 'E031', 'E100', 'E014', 'E036')
AND er.deal_progress_ NOT IN ('3' , '6', '5')
AND er.is_jfyc_ = '1'),
1,
0) tcJfycStatus,
IF(EXISTS( SELECT /*+ NO_REWRITE */
1
FROM
ftsp_ht_khfw_error er
WHERE
er.kh_khxx_id_ = kh.id_
AND er.deal_progress_ = '5'),
1,
0) jfycDhs
FROM
ftsp_kh_khxx kh
LEFT JOIN
ftsp_ht_htxx ht ON ht.kh_khxx_id_ = kh.id_
AND ht.status_ = 4
LEFT JOIN
ftsp_kh_zh_work_bench zh ON zh.kh_khxx_id_ = kh.id_
LEFT JOIN
ftsp_ht_khfw hf ON hf.kh_khxx_id_ = kh.id_
AND hf.fw_lx_ = '1'
LEFT JOIN
ftsp_zt_ztxx zt ON zt.kh_khxx_id_ = kh.id_
AND zt.is_default_ = '1'
LEFT JOIN
ftsp_kh_initial_work_bench init ON init.kh_khxx_id_ = kh.id_
WHERE
kh.zj_zjxx_id_ = 'h0000000000000498723430515097600'
AND kh.fw_status_ = '1'
AND kh.id_ IN (
'34236668E4F24A2194078B06A41FB3E9',
'h0000000000000723719839150735360',
'h0000000000000723721895296237568',
'h0000000000000723725015971635200',
'h0000000000000723725944125022208',
'h0000000000000723727762867486720',
'h0000000000000723027926701416448',
'FAE0ADFBC45C4CC49A43812C0D85886D',
'h0000000000000723740577568088064',
'h0000000000000723764224533954560',
'h0000000000000414686761562685610',
'h0000000000000723770814864670720',
'h0000000000000721635630453366784',
'A801EC5C41CC4498AFB9CC7C3B99C6FE',
'h0000000000000270905798948709064',
'h0000000000000723773802352967680',
'h0000000000000723777364676009984',
'h0000000000000723724900702601216',
'h0000000000000723792559037177856',
'h0000000000000723793193538904064',
'h0000000000000718746453054595072',
'h0000000000000723795291613667328',
'h0000000000000723809000746622976',
'A6A60FD955234A96A18EEE6BECC28340',
'h0000000000000723813892685021184',
'h0000000000000723819395545972736',
'h0000000000000723828924156182528',
'28F140F099A54CE7820E26C72D0766FB',
'D4E8A86271B2444CAEEA782D1BD57662',
'457A44E783FE424FBDB3A05881842E4A',
'h0000000000000302974729383772160',
'h0000000000000304426901648629760',
'h0000000000000723864891199176704',
'h0000000000000304444614374809600',
'h0000000000000304445703337730048',
'3C426DB4C422412487B4B0F257D511A2',
'9AB2904F320B44F78D380A0EDE8FB777',
'9424C1F306024F09A43B3D55C5F01998',
'h0000000000000304478707036643328',
'h0000000000000254492324970897536',
'13fb3f5c610545d58da169ef29c38b01',
'h0000000000000292995586075181067',
'6F18F2BE57284C6A99D9FA9AFED0761B',
'h0000000000000254249655644234017',
'h0000000000000304516426437713920',
'h0000000000000297169967694233600',
'h0000000000000279888233080111591',
'h0000000000000297681196213616719',
'h0000000000000257686961026138473',
'h0000000000000267985696891675845',
'8B3E36C081254D8CA9591E2558CCE090',
'h0000000000000304565098089226240',
'h0000000000000304568441587048448',
'h0000000000000125535160930034047',
'0534221B7D4541CCACE6090964500285',
'h0000000000000724061446504931328',
'h0000000000000034450037090623488',
'B22F868B0B764A09A1428AFF5A2CBCBC',
'ACAD193F8F5A411595D98F8503E77F99',
'0CE656BD22AF4C2EB9176EF4BDC458DD',
'h0000000000000724154007051550720',
'h0000000000000724162090797785088',
'h0000000000000542736819524067800',
'h0000000000000107032479624822807',
'h0000000000000483171988757233664',
'1C5E142BF6B04498AF1E423B78298F59',
'h0000000000000724210513726070784',
'h0000000000000304773993869008896',
'h0000000000000302634834664579072',
'h0000000000000302614239776268288',
'h0000000000000142822591766438017',
'6ECAE3DE7B194C5990D382AFD072F2C8',
'h0000000000000170729408827809792',
'14869805049584908244 ',
'E6C0D181AA8947A0AB9982D99E05D8D2',
'h0000000000000304843433277054976',
'h0000000000000303417233084768256',
'h0000000000000269991955040755816',
'h0000000000000181081137609793652',
'14767748520687034455 ',
'h0000000000000304864450133286912',
'h0000000000000300424046572700153',
'h0000000000000304552293726380032',
'251558c660904d35b1ceb4d4a5d15dea',
'833E37CADC87456B9A97F8C7D5502091',
'h0000000000000304899210738106368',
'h0000000000000234191610981548582',
'h0000000000000274397987003998301',
'h0000000000000230347561999212544',
'h0000000000000054110362505454764',
'DDCD480FD80845BFA4A6132FB853B161',
'h0000000000000304796305745969152',
'h0000000000000304931530857717760',
'h0000000000000304911545158541312',
'29A0A978077C4A5F8DDD69E3B294C90B',
'h0000000000000294597953715781643',
'h0000000000000724440982204850176',
'2B3FFFD5BFA34E05811FB5F029FF9440',
'h0000000000000082753401467142149',
'4B15260A35644CB68530BF4582A6CD81',
'h0000000000000724499642741374976',
'h0000000000000724499149424074752',
'h0000000000000724507527596654592',
'h0000000000000724507481748725760',
'h0000000000000724518956118761472',
'h0000000000000724520333633077248',
'h0000000000000724522237779943424',
'E820CD5DBC6D4402A654C755B7BE0DCA',
'h0000000000000723507205084266496',
'h0000000000000724533114579107840',
'h0000000000000724518650917027840',
'h0000000000000724536037884149760',
'h0000000000000724542432900759552',
'h0000000000000722985982956773377',
'36AD690B50D64E0B98C38CD8ACEEFCDD',
'h0000000000000305141955264200704',
'h0000000000000275807199836087798',
'h0000000000000305157188418486272',
'h0000000000000243369829867806815',
'8F92145936F24BF4B228A73107205F50',
'h0000000000000259950636164071424',
'h0000000000000305156806963306496',
'h0000000000000305164730276208640',
'h0000000000000724452729526632448',
'922f740ff7a24dd58bb97f5718f50fda',
'5CA449B71CBB4B1D8B41339A1831B78B',
'h0000000000000265768311404708555',
'h0000000000000305205333475155968',
'ABA9023D43F44E15B3F34B15690ABEC9',
'h0000000000000304844025307267072',
'h0000000000000247329166550860289',
'AD76E7089DD7467197C778332D4A9763',
'h0000000000000053711454633566664',
'h0000000000000241550432005587078',
'B89C82C1C91B4F72B4DB99A22D29C22A',
'2ab24d0af4014775984f30793d9716b2',
'h0000000000000305163873877737472',
'C869F44C9BA045ACB1C9A627F8E7453E',
'h0000000000000305243916105867264',
'h0000000000000257420134761022212',
'h0000000000000305243144223940608',
'h0000000000000305258433409916928',
'h0000000000000305211971179347968',
'CEEBA0364DCE486FAE36F2796F9223F7',
'h0000000000000305274520881037312',
'9022E50CCFA64F6C859FA7FEEB3CA0DB',
'9AE506969B2D4C33A0108B245870FDF4',
'03107BA2FE554DACA9EA2F4066ACE557',
'h0000000000000724840477185974272',
'4734548F09444FA89EF5A316FEF06316',
'E390DFF439CC4FE0BD699B43FB0CECA7',
'h0000000000000247329166538276896',
'69995747147646c3a18ef5172c3f9d26',
'h0000000000000259217775795143043',
'4F0E20713D234FF5A27A143B4960C518',
'h0000000000000724946731174641664',
'h0000000000000304877872127164416',
'h0000000000000305535603109724160',
'h0000000000000137103803694621229',
'h0000000000000305536638385586176',
'h0000000000000305547060790624256',
'F6E9B1495F1C4B3A8D56F8FF93C46DD9',
'h0000000000000289564115418497028',
'h0000000000000200775292788171057',
'h0000000000000258186906250707028',
'h0000000000000305212086354911232',
'h0000000000000305574162118172672',
'h0000000000000305562146032787456',
'h0000000000000302037093272027350',
'h0000000000000299805761412096000',
'h0000000000000305587154700435456',
'h0000000000000304582043475853312',
'h0000000000000305564225468047360',
'h0000000000000081332275838935040',
'h0000000000000305612560690274304',
'h0000000000000247326428848955546',
'h0000000000000257653388575440966',
'h0000000000000297535879455450011',
'h0000000000000283193772351971771',
'h0000000000000305635742063214592',
'FA588DC9A7024174B11D6FABC5DBF555',
'h0000000000000125871690378739945',
'h0000000000000275772998730514850',
'h0000000000000175190294598508906',
'C7005C33EF584747A2473AD794B7D33E',
'BCCFB1B24C9149E3BC9ECCB07D5795E7',
'C38E8719202544B5B88BC8C4E838D3C9',
'77A7BA60CFAE446D861ACB13255055F2',
'F3C0F26450644D6BAFEE2726F9732644',
'h0000000000000300197292015042560',
'h0000000000000305876303152119808',
'h0000000000000305875145008300032',
'h0000000000000305875232283377664',
'h0000000000000275773011120431136',
'h0000000000000260562279752376536',
'h0000000000000305892253305888768',
'h0000000000000263668641769185803',
'DEE56745A6D84C518761DAE721351C68',
'h0000000000000305892335417745408',
'h0000000000000299333779395903771',
'h0000000000000268920993645601632',
'h0000000000000305929428810530816',
'h0000000000000305892222356094976',
'h0000000000000302970516343832576')
GROUP BY kh.id_;
另外，老师，请问，为啥4.2.4也有这个问题？
目前，我们发现用的4.2.3是没有这个问题的。
sql涉及的表结构可以发一下么
你那里积分够么，建议提个悬赏帖方便拉个群处理该问题
应该够的。因为我们也是持续关注oceanbase的，所以，肯定是希望后续的高版本不会出现这个问题~
但是我现在找不到悬赏按键，是需要重新发贴子吗？
已知的缺陷导致的DAS与非DAS计划交叉枚举的情况。该内存不足问题在425版本得到缓解，预计在425bp1解决