• adsas数据库去O记


      adsas 数据库是用于广告买量数据分析;在17年由 Oracle 迁移到 PostgreSQL。现把之前的迁移笔记整理下。本次迁移表91个;存储过程21个;数据库大小2G。 

    1. 准备PostgreSQL数据库

      安装PostgreSQL数据库 参考 PostgreSQL简单安装手册 ;

           在安装PostgreSQL数据库之前;首先要对数据库硬件要做基准评测;会影响是否能取代Oracle服务器。安装完成之后;需要创建同名 数据库/用户/schema。

    2. 配置Ora2Pg迁移工具

      Ora2Pg是一个免费的工具,用于将Oracle数据库迁移到PostgreSQL兼容的模式。它连接您的Oracle数据库,自动扫描并提取它的结构或数据,然后生成可以装载到PostgreSQL数据库的SQL脚本。Ora2Pg可以从逆向工程Oracle数据库到大型企业数据库迁移,或者简单地将一些Oracle数据复制到PostgreSQL数据库中。它非常容易使用,并且不需要任何Oracle数据库知识,而不需要提供连接到Oracle数据库所需的参数。

      安装配置文档

    3. 利用Ora2Pg导出数据并导入PostgreSQL

      在上一篇成功安装 Ora2Pg 工具;接下来我们利用Ora2Pg导出数据。

    3.1 配置Oracle客户端连接配置文件

    [postgres@oracle166 config]$ tnsping ora165
    
    TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 12-OCT-2018 16:45:22
    
    Copyright (c) 1997, 2013, Oracle.  All rights reserved.
    
    Used parameter files:
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ******)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
    OK (10 msec)

      当然该步骤也可以省略。在接下来配置ora2pg.conf文件有讲解

    3.2 配置ora2pg.conf

    ORACLE_DSN  dbi:Oracle:ora165  
    ORACLE_USER adsas
    ORACLE_PWD  easouadsas166
    LOGFILE /home/postgres/log/Ora2Pg.log
    SCHEMA  adsas   
    TYPE TABLE SEQUENCE COPY
    PG_NUMERIC_TYPE    0
    PG_INTEGER_TYPE    1
    DEFAULT_NUMERIC float
    SKIP    fkeys pkeys ukeys indexes checks
    NLS_LANG    AMERICAN_AMERICA.UTF8
    OUTPUT     adsas.sql

      只导出 TABLE,SEQUENCE。存储过程,函数,DBLINK,视图等需要改写;

    参数配置查考

    3.3 导出数据

    [postgres@oracle166 config]$ ora2pg -c ora2pg.conf

    3.4 导入PostgreSQL

    [postgres@oracle166 ]$ psql adsas adsas
    psql (9.6.0)
    Type "help" for help.
    
    adsas=> i /home/postgres/config/adsas.sql

      表数据跟序列已成功导入;接下来的开始改写存储过程和函数。

    4. PostgreSQL创建oracle兼容函数(Orafce)

      在改写存储过程/函数之前;先安装Oracle兼容函数(Orafce);它会让你觉得PostgreSQL跟Oracle写法没什么区别PostgreSQL是和Oracle最接近的企业数据库,包括数据类型,功能,架构和语法等几个方面。甚至大多数的日常应用的性能也不会输给Oracle。但是Oracle有些函数或者包,默认PostgreSQL是没有的,需要安装orafce包来实现这些兼容性。

      安装文档

      现在orafce已经包含了如下内容。

    1. 类型 date, varchar2 and nvarchar2
    2. 函数 concat, nvl, nvl2, lnnvl, decode, bitand, nanvl, sinh, cosh, tanh and oracle.substr
    3. dual 表
    4. package : 
        dbms_output
        utl_file
        dbms_pipe
        dbms_alert
        PLVdate
        PLVstr and PLVchr
        PLVsubst
        DBMS_utility
        PLVlex
        DBMS_ASSERT
        PLUnit
        DBMS_random

    5. 安装PostgreSQL中调试工具plpgsql存储过程

      这个我感觉没多大用;可以忽略!

      安装文档

    6. 存储过程/函数改写过程

      A/G

    7. Oracle迁移PG代码优化

      经存储过程/函数改写完成;代码要跟Oracle等同;执行时长跟Oracle上面的时长是否差不多;发现在Oracle上面执行 12s;而改写的代码需要 256s;相差21倍之多。这差距不是一般的大。同样的代码可能在Oracle执行效果要好;这跟数据库底层代码有关。

    7.1 insert ... on conflict 妙用

      经核查;其中有条SQL花了将近240s的时间。

     INSERT INTO TBL_AD_CLICK_LOG  
        SELECT *
          FROM TMP_AD_CLICK_LOG
         WHERE (ES_APP_ID, IDFA_SUM) NOT IN
               (SELECT ES_APP_ID, IDFA_SUM FROM TBL_AD_CLICK_LOG);

      表TBL_AD_CLICK_LOG: 存放所有已排重的点击数;数据量有40万条。
      表TMP_AD_CLICK_LOG: 存放当天已排重点击数;
    该SQL目的就是避免重复的 idfa_sum 在插入表 TBL_AD_CLICK_LOG;以达到去重的效果。该SQL在Oracle上运行时长1s左右。然而在PG上运行时长将近240s。效率差。与这SQL等价且高效的SQL。

    INSERT INTO TBL_AD_CLICK_LOG  
     SELECT *
    FROM TMP_AD_CLICK_LOG on conflict (ES_APP_ID, IDFA_SUM) do nothing;

      使用该SQL之前需创建唯一索引;

    CREATE UNIQUE INDEX IND_AD_CLICK_IDFA ON TBL_AD_CLICK_LOG(ES_APP_ID, IDFA_SUM);

      优化后;在PostgreSQL执行一次调度不到5s;相比Oracle的执行一次调度需要12s还短。

    7.2 高效删除重复数据

      在前例中;需要创建唯一索引;但是表TBL_AD_CLICK_LOG存在重复记录;由于与媒体对接测试的数据为清理。删除表中重复数据;想都不用想用rowid;在PG中;ctid跟rowid对应。所以SQL自然而然就出来了。放到PostgreSQL上执行;执行了 396s 还在执行中。我终止SQL执行。

    DELETE FROM TBL_AD_CLICK_LOG
      WHERE ctid NOT IN (SELECT MIN(ctid)
                            FROM TBL_AD_CLICK_LOG
                           GROUP BY ES_APP_ID, IDFA_SUM);

    优化的目的:用最小的时间达到相同效果。

      所以先在Oracle去执行;在用ora2pg工具迁移到PostgreSQL;我没意见;比较相比前面花的时间要少。在PostgreSQL有没有更高效的SQL呢?答案是有的

     delete from tbl_ad_click_log
      where ctid = any(array(select citd
               from (select "row_number"() over(partition by es_app_id, idfa_sum order by id) as rn,ctid
                       from tbl_ad_click_log) as ad
              where ad.rn > 1));

      先别吐槽SQL更复杂;但效率真的很棒;执行只花8s就搞定

  • 相关阅读:
    Java基础
    Java 基础
    Java基础
    Java基础
    web 学习随记(1)
    jdk8-》List去重
    JVM-调优方案
    JUC_02 AQS工作原理
    Synchronized-可重入锁原理
    JUC_01 线程阻塞、唤醒三种方式
  • 原文地址:https://www.cnblogs.com/lottu/p/9809089.html
Copyright © 2020-2023  润新知