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

一种,dws,迁移,oracle,connect,by,语法,方案 · 浏览次数 : 18

小编点评

**GaussDB DWS迁移CONNECT BY语法方案** **简介：** 本文提供一种用于执行GaussDB DWS迁移CONNECT BY语法方案。该方案使用预置对象定义和CREATE TABLE语句来对目标数据库中的表进行迁移。 **语法：** ```sql CONNECT BY NOCYCLE1) ``` **关键点：** 1. **WHERE条件：**语句首先使用WHERE子句过滤源数据库中的记录，并按照顺序执行条件。 2. **CONNECT BY子句：**该子句用于定义连接策略。 - NOCYCLE1：表示连接不依赖任何顺序。 - i.BAS_INSTRUMENT_ID = i.INSTRUMENT_PARENT_IDSTART：连接基于源表中的父级 instrument_id。 - i.instrument_parent_id IS NULL：连接仅基于父级 instrument_id。 3. **结果：**最终结果将包含连接后的目标数据库中的所有记录。 **示例：** 假设源数据库中存在以下表： | 编号 | 父级 ID | 姓名 | |---|---|---| | 1 | NULL | 张三 | | 2 | 1 | 王四 | | 3 | 2 | 李五 | 使用以下查询语句连接并迁移该表： ```sql CONNECT BY NOCYCLE1) 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, 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 = 1CONNECT BY NOCYCLE PRIOR i.BAS_INSTRUMENT_ID = i.INSTRUMENT_PARENT_IDSTART WITH i.instrument_parent_id IS NULL ``` **结果：** 该查询将创建一个名为 `ogg_ets_bas_instrument_t_2670` 的表，其中包含所有源数据库中的记录，并按 instrument_id、parent_thekey、instrument_name、instrument_path、bas_id_path、instrument_level 和 cycle_error 等字段进行排序。

正文

摘要：本文提供一种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

点击关注，第一时间了解华为云新鲜技术~