oracle存储过程isas,adsas数据库去O记

最新推荐文章于 2024-05-21 09:58:09 发布

安静的森林

最新推荐文章于 2024-05-21 09:58:09 发布

阅读量233

点赞数

文章标签： oracle存储过程isas

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

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_TYPE0PG_INTEGER_TYPE1DEFAULT_NUMERICfloatSKIP 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" forhelp.

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 nvarchar22. 函数 concat, nvl, nvl2, lnnvl, decode, bitand, nanvl, sinh, cosh, tanh and oracle.substr3. 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 INTOTBL_AD_CLICK_LOGSELECT *

FROMTMP_AD_CLICK_LOGWHERE (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 INTOTBL_AD_CLICK_LOGSELECT *

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 FROMTBL_AD_CLICK_LOGWHERE ctid NOT IN (SELECT MIN(ctid)FROMTBL_AD_CLICK_LOGGROUP BY ES_APP_ID, IDFA_SUM);

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

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

delete fromtbl_ad_click_logwhere ctid = any(array(selectcitdfrom (select "row_number"() over(partition by es_app_id, idfa_sum order by id) asrn,ctidfrom tbl_ad_click_log) asadwhere ad.rn > 1));

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