【YashanDB 知识库】如何从内存中获取 SQL 语句的执行计划

2025-02-12
广东
本文字数：1527 字
阅读完需：约 5 分钟

本文内容来自 YashanDB 官网，原文内容请见https://www.yashandb.com/newsinfo/7664896.html?templateId=1718516

简介

目前从 PostgreSQL 迁移到 YashanDB 后，需要进行数据校验。下面给出 user1 模式从 PostgreSQL 迁移到 YashanDB 进行数据行数比对的示例。

详情

获取 PostgreSQL 精确行数

创建 table_count，用于存储行数（建议：使用现有的迁移模式 user1，并把 table_count 创建在 user1 用户下）

create table user1.table_count (owner varchar(200),table_name varchar(200),num_rows int);

复制代码

获取 user1 模式下的所有表的行数

DO $$
DECLARE
one_row record;
stmt varchar(200);
num_rows int;
BEGIN
 
EXECUTE 'truncate table table_count';
FOR one_row IN (select schemaname, tablename from pg_tables where upper(schemaname)=upper('user1') and upper(tablename)!=upper('table_count')) loop
 
stmt := 'select count(*) from ' || one_row.schemaname || '.' || one_row.tablename || '';
raise notice '%', stmt;
EXECUTE stmt into num_rows;
raise notice '%', num_rows;
insert into table_count values(upper(one_row.schemaname), upper(one_row.tablename), num_rows);
END loop;
commit;
END $$;

复制代码

获取 YashanDB 精确行数

从 PostgreSQL 迁移到 YashanDB 迁移 user1 模式，迁移完成后可以创建 table_count ，用于存储行数（建议：创建新的用户 db_yashan，并把 table_count 创建在 db_yashan 用户下）

create table db_yashan.table_count (owner varchar(200),table_name varchar(200),num_rows int);

复制代码

获取 user1 用户下的所有表的行数

declare
v_owner VARCHAR2(100);
v_tabname VARCHAR2(100);
stmt VARCHAR2(200);
num_rows number;
begin
EXECUTE IMMEDIATE 'truncate table db_yashan.table_count';
for rec in (select owner,table_name from dba_tables where owner=upper('user1') and tablename!=upper('table_count') order by 1, 2)
            loop
            select rec.owner,rec.table_name into v_owner,v_tabname from dual;
            stmt := 'select count(*) from "' || v_owner || '"."' || v_tabname || '"';
            EXECUTE IMMEDIATE stmt INTO num_rows;
            insert into db_yashan.table_count values(v_owner,v_tabname,num_rows);
           end loop;
end;
/

复制代码

对比 PostgreSQL 和 YashanDB 的精确行数

SELECT
        owner,
        table_name,
        p_num_rows,
        y_num_rows
FROM
        (
        SELECT
                nvl(p.owner, y.owner) owner,
                nvl(p.table_name, y.table_name) table_name,
                p.num_rows p_num_rows,
                y.num_rows y_num_rows
        FROM
                (
                SELECT
                        owner,
                        table_name,
                        num_rows
                FROM
                        user1.table_count) p
        FULL OUTER JOIN
        (
                SELECT
                        owner,
                        table_name,
                        num_rows
                FROM
                        db_yashan.table_count) y
ON
                p.owner = y.owner
                AND p.table_name = y.table_name
        ORDER BY
                p.owner,
                p.table_name
)
WHERE
        nvl(p_num_rows, 999999999999999999999999999999) != nvl(y_num_rows, 999999999999999999999999999999)
;