本文分享自華爲雲社區《》，作者： 譡裡個檔 。
1. CONNECT BY NOCYCLE
--1) 預置對象定義
DROP SCHEMA IF EXISTS "sdifin" CASCADE ; CREATE SCHEMA "sdifin" ; DROP TABLE IF EXISTS "sdifin" . "ogg_ets_bas_instrument_t_2670" ; SET search_path = sdifin; CREATE TABLE ogg_ets_bas_instrument_t_2670 ( bas_instrument_id numeric NOT NULL , instrument_name character varying ( 600 ), instrument_parent_id numeric , status numeric ) WITH (enable_hstore= on , orientation= column , compression=yes, enable_delta= false , colversion= 2.0 , max_batchrow= 60000 ) DISTRIBUTE BY HASH (bas_instrument_id); DROP TABLE IF EXISTS "sdifin" . "ogg_sectype_2360" ; SET search_path = sdifin; CREATE TABLE ogg_sectype_2360 ( thekey character varying ( 18 ) NOT NULL , name character varying ( 150 )) WITH (enable_hstore= on , orientation= column , compression=yes, enable_delta= false , colversion= 2.0 , max_batchrow= 60000 ) DISTRIBUTE BY REPLICATION ;
-- 2) oracle原始語句
SELECT to_char(i.BAS_INSTRUMENT_ID) AS INSTRUMENT_ID, to_char(i.INSTRUMENT_PARENT_ID) AS parent_thekey, TRIM (i.INSTRUMENT_NAME) AS INSTRUMENT_NAME, SYS_CONNECT_BY_PATH(i.INSTRUMENT_NAME, ; ) AS INSTRUMENT_PATH, SYS_CONNECT_BY_PATH(i.BAS_INSTRUMENT_ID, - ) AS BAS_ID_PATH, LEVEL AS INSTRUMENT_LEVEL, CONNECT_BY_ISCYCLE AS CYCLE_ERROR, 2670 AS ss_id FROM OGG_ETS_BAS_INSTRUMENT_T_2670 i, OGG_SECTYPE_2360 s2 WHERE upper (i.instrument_name) = upper ( TRIM (s2.name(+))) AND s2.thekey IS NULL AND I.STATUS = 1 CONNECT BY NOCYCLE PRIOR i.BAS_INSTRUMENT_ID = i.INSTRUMENT_PARENT_ID START WITH i.instrument_parent_id IS NULL
oracle中的執行計劃
關鍵點：
1）語句特征：SQL語句中WHERE字句包含非關聯條件
2）執行特征：WHERE字句中的非關聯條件計算的優先級低於 BY，即 BY執行之後才會進行這些條件的過濾，如上id=2的條件
--3) DWS等價改寫邏輯
WITH RECURSIVE TMP_ETC AS ( SELECT to_char(i.bas_instrument_id) AS instrument_id, to_char(i.instrument_parent_id) AS parent_thekey, trim (i.instrument_name) AS instrument_name, ; ||i.instrument_name AS instrument_path, - ||i.bas_instrument_id AS bas_id_path, 1 AS instrument_level, 0 AS cycle_error, 2670 AS ss_id, s2.thekey, i.status, i.bas_instrument_id AS start_val FROM sdifin.ogg_ets_bas_instrument_t_2670 i LEFT JOIN sdifin.ogg_sectype_2360 s2 ON upper ( trim (i.instrument_name)) = upper ( trim (s2.name)) WHERE i.instrument_parent_id IS NULL UNION ALL SELECT to_char(i.bas_instrument_id) AS instrument_id, to_char(i.instrument_parent_id) AS parent_thekey, trim (i.instrument_name) AS instrument_name, (b.instrument_path || ; ||i.instrument_name) AS instrument_path, (b.bas_id_path || - ||i.bas_instrument_id) AS bas_id_path, b.instrument_level+ 1 AS instrument_level, decode ( trim (i.bas_instrument_id)=b.start_val, false , 0 , 1 ) AS cycle_error, 2670 AS ss_id, s2.thekey, i.status, b.start_val AS start_val FROM sdifin.ogg_ets_bas_instrument_t_2670 i LEFT JOIN sdifin.ogg_sectype_2360 s2 ON upper (i.instrument_name) = upper ( trim (s2.name)) INNER JOIN tmp_etc b ON b.instrument_id = i.instrument_parent_id WHERE b.cycle_error 1 ) SELECT instrument_id, parent_thekey, instrument_name, instrument_path, bas_id_path, instrument_level, cycle_error, ss_id FROM tmp_etc WHERE thekey IS NULL AND status = 1
關注
