摘要：本文提供一种GaussDB DWS迁移CONNECT BY语法方案。
本文分享自华为云社区《GaussDB(DWS)迁移 - oracle兼容 -- CONNECT BY迁移》，作者： 譡里个檔 。
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字句中的非关联条件计算的优先级低于CONNECT BY，即CONNECT BY执行之后才会进行这些条件的过滤，如上id=2的FILTER条件
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
点击关注，第一时间了解华为云新鲜技术~
