一种 DWS 迁移 Oracle 的 CONNECT BY 语法的方案

2023-05-18
广东
本文字数：1779 字
阅读完需：约 6 分钟

本文分享自华为云社区《GaussDB(DWS)迁移 - oracle兼容 -- CONNECT BY迁移》，作者：譡里个檔。

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 s2WHERE upper(i.instrument_name) = upper(TRIM(s2.name(+)))AND s2.thekey IS NULL AND I.STATUS = 1CONNECT BY NOCYCLE PRIOR i.BAS_INSTRUMENT_ID = i.INSTRUMENT_PARENT_IDSTART 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_idFROM tmp_etcWHERE thekey IS NULLAND status = 1