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

本文内容来自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)

;
posted @   YashanDB  阅读(3)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· 【全网最全教程】使用最强DeepSeekR1+联网的火山引擎,没有生成长度限制,DeepSeek本体
点击右上角即可分享
微信分享提示