业务描述: 外围系统需要访问ERP系统中的数据(两个数据库),可用dblink将两个数据库联接,访问方式有两种:
1. 在目的数据库建立访问源数据库的synonym
优点: 及时性好,只要erp中数据更改后,在目的数据库立即就可以反映出来
缺点: 通过dblink直接访问速度较慢,有时无法使用ebs数据库中索引或是一些奇怪的问题
2. 在目的数据库建立访问源数据库的物化视图
优点: 访问速度快,可以对物化视图加索引,以加快查询速度
缺点: 数据及时性不好,如果服务器配置较高此缺点可以忽略
物化视图建立:
1. 语法: CREATE MATERIALIZED VIEW viewname
[refresh [fast/complete/force]
[on demand/commit]
[start with date]
[next date]
[with {primary key/rowid}]] AS subquery
2. 语法说明:
refresh fast: 增量更新,需要在源数据表中建立日志表
refersh complete: 全表更新,如果数据天中数据量较少可以采用此方式建立物化视图
refersh force: 如果增量更新可能则进行增量更新,不可用则进行全表更新.
注: 如果不指定 refersh 则默认为 refersh force
on demand: 按照指定的时间频率进行更新,较常用的一种方法
on commit: 源数据表中更新数据commit后就刷新物化视图,此方法及时性较好,但对服务器配置、性能要求相对较高,很少采用
start with date: 此物化视图在何时开始第一次刷新
next date: 指定物化视图刷新的频率
with primary key: 如果源数据表中有单个栏位的 primary key,可以用此方式建立物化视图
with rowid: 如果源数据表中没有单个栏位的primary key,则需使用此方式建立物化视图
3. 创建源数据表的日志表:
CREATE MATERIALIZED VIEW LOG ON owner.tablename;
注: 在建立此log时,一定要使用和数据表相同的owner,建立好后可在 all_mview_logs view 中找到创建的相关信息
4. 实例:
a.创建物料信息的物化视图(增量更新)
因为物料信息表中的数据较多,且更新频率相对较低,故我们采取增量更新方式的物化视图
(1). CREATE MATERIALIZED VIEW LOG ON INV.MTL_SYSTEM_ITEMS_B
(2). 以INV用户登录,将 MLOG$_MTL_SYSTEM_ITEMS_B(执行第1步后会生成此表)授权给apps
(3). 以apps用户登录,创建同义词 MLOG$_MTL_SYSTEM_ITEMS_TL
(4). 登录目的数据库
CREATE MATERIALIZED VIEW mtl_system_items_b -- ENABLE QUERY REWRITE
REFRESH FAST ON DEMAND WITH ROWID
START WITH SYSDATE
NEXT SYSDATE+1/2
as
SELECT a.* FROM mtl_system_items_b@dblink a;
create unique index mtl_system_items_b_U1 on mtl_system_items_b (ORGANIZATION_ID,INVENTORY_ITEM_ID);
create index mtl_system_items_b_N1 on mtl_system_items_b (ORGANIZATION_ID,Segment1 );
注: 以上步骤即完成创建增量更新的物化视图