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

简介

目前从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;

        

          
        
        
        

          

          AI 代码解读
        
      
      

end;

/
对比PostgreSQL和YashanDB的精确行数

SELECT

    owner,

    table_name,

    p_num_rows,

    y_num_rows

        

          
        
        
        

          

          AI 代码解读
        
      
      

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

        

          
        
        
        

          

          AI 代码解读
        
      
      

ON

            p.owner = y.owner

            AND p.table_name = y.table_name

    ORDER BY

            p.owner,

            p.table_name

        

          
        
        
        

          

          AI 代码解读
        
      
      

)

WHERE

    nvl(p_num_rows, 999999999999999999999999999999) != nvl(y_num_rows, 999999999999999999999999999999)

        

          
        
        
        

          

          AI 代码解读
        
      
      

;