Oracle跨库不同数据类型表合并的性能优化诉求

环境与业务场景 #

• 涉及两个Oracle数据库：DB1的wh1用户下有lotxlocxid表；DB2包含wmwhse1至wmwhse5共5个用户，每个用户都有独立的lotxlocxid表
• 遗留前端需要聚合展示两个数据库中所有lotxlocxid表的内容

核心问题 #

DB2表的多数字段为nvarchar2类型，DB1对应字段为varchar2类型，直接关联会触发ORA-12704字符集不匹配错误，必须通过cast()/to_char()转换为varchar2类型。但字段转换会导致索引无法被利用，查询触发全表扫描，性能严重下降。

当前视图实现（核心查询） #

create or replace view wh1.v_dmt_lotxlocxid as
select 'DMT' as datasource, cast(wh.facility as varchar2(5)) as facility, to_char(lli.lot) as lot, cast(l.loc as varchar2(10)) as loc, cast(lli.id as varchar2(50)) as id, cast(lli.storerkey as varchar2(15)) as storerkey, CAST(lli.sku AS varchar2(50)) as sku,
       lli.qty, lli.qtyallocated, lli.qtypicked, lli.qtyexpected, cast(lli.status as varchar2(10)) as status,
       to_char(l.locationtype) as locationtype, to_char(l.putawayzone) putawayzone,
       lli.editdate, lli.adddate, cast(lli.editwho as varchar2(50)) as editwho, cast(lli.addwho as varchar2(50)) as addwho, wh.externalwh
       from wmwhse1.lotxlocxid@sce lli, wmwhse1.loc@sce l, dmt.warehouse wh
       where lli.loc = l.loc
         and l.whseid = wh.whseid
union
select 'DMT' as datasource, cast(wh.facility as varchar2(5)) as facility, to_char(lli.lot) as lot, cast(l.loc as varchar2(10)) as loc, cast(lli.id as varchar2(50)) as id, cast(lli.storerkey as varchar2(15)) as storerkey, CAST(lli.sku AS varchar2(50)) as sku,
       lli.qty, lli.qtyallocated, lli.qtypicked, lli.qtyexpected, cast(lli.status as varchar2(10)) as status,
       to_char(l.locationtype) as locationtype, to_char(l.putawayzone) putawayzone,
       lli.editdate,lli.adddate, cast(lli.editwho as varchar2(50)) as editwho,  cast(lli.addwho as varchar2(50)) as addwho, wh.externalwh
       from wmwhse2.lotxlocxid@sce lli, wmwhse2.loc@sce l, dmt.warehouse wh
       where lli.loc = l.loc
         and l.whseid = wh.whseid
union
select 'DMT' as datasource, cast(wh.facility as varchar2(5)) as facility, to_char(lli.lot) as lot, cast(l.loc as varchar2(10)) as loc, cast(lli.id as varchar2(50)) as id, cast(lli.storerkey as varchar2(15)) as storerkey, CAST(lli.sku AS varchar2(50)) as sku,
       lli.qty, lli.qtyallocated, lli.qtypicked, lli.qtyexpected, cast(lli.status as varchar2(10)) as status,
       to_char(l.locationtype) as locationtype, to_char(l.putawayzone) putawayzone,
       lli.editdate,lli.adddate, cast(lli.editwho as varchar2(50)) as editwho,  cast(lli.addwho as varchar2(50)) as addwho, wh.externalwh
       from wmwhse3.lotxlocxid@sce lli, wmwhse3.loc@sce l, dmt.warehouse wh
       where lli.loc = l.loc
         and l.whseid = wh.whseid
union
select 'DMT' as datasource, cast(wh.facility as varchar2(5)) as facility, to_char(lli.lot) as lot, cast(l.loc as varchar2(10)) as loc, cast(lli.id as varchar2(50)) as id, cast(lli.storerkey as varchar2(15)) as storerkey, CAST(lli.sku AS varchar2(50)) as sku,
       lli.qty, lli.qtyallocated, lli.qtypicked, lli.qtyexpected, cast(lli.status as varchar2(10)) as status,
       to_char(l.locationtype) as locationtype, to_char(l.putawayzone) putawayzone,
       lli.editdate,lli.adddate, cast(lli.editwho as varchar2(50)) as editwho,  cast(lli.addwho as varchar2(50)) as addwho, wh.externalwh
       from wmwhse4.lotxlocxid@sce lli, wmwhse4.loc@sce l, dmt.warehouse wh
       where lli.loc = l.loc
         and l.whseid = wh.whseid
union
select 'DMT' as datasource, cast(wh.facility as varchar2(5)) as facility, to_char(lli.lot) as lot, cast(l.loc as varchar2(10)) as loc, cast(lli.id as varchar2(50)) as id, cast(lli.storerkey as varchar2(15)) as storerkey, CAST(lli.sku AS varchar2(50)) as sku,
       lli.qty, lli.qtyallocated, lli.qtypicked, lli.qtyexpected, cast(lli.status as varchar2(10)) as status,
       to_char(l.locationtype) as locationtype, to_char(l.putawayzone) putawayzone,
       lli.editdate,lli.adddate, cast(lli.editwho as varchar2(50)) as editwho,  cast(lli.addwho as varchar2(50)) as addwho, wh.externalwh
       from wmwhse5.lotxlocxid@sce lli, wmwhse5.loc@sce l, dmt.warehouse wh
       where lli.loc = l.loc
         and l.whseid = wh.whseid
union
select 'LEGACY' as datasource, l.facility, lli.lot, l.loc, lli.id, lli.storerkey, lli.sku, lli.qty,
       lli.qtyallocated, lli.qtypicked, lli.qtyexpected, lli.status,
       l.locationtype, l.putawayzone,
       lli.editdate,lli.adddate, lli.editwho, lli.addwho,
       (select wh.externalwh from dmt.warehouse wh where cast(wh.facility as varchar2(5)) = l.facility) as externalwh
       from wh1.lotxlocxid lli, wh1.loc l
       where lli.loc = l.loc
;

已尝试的优化措施 #

• 通过PL/SQL Explain Plan确认查询存在全表扫描
• 尝试为转换后的字段创建新索引，未解决性能问题
• 调研物化视图方案，但无法在DB1通过dblink创建基于DB2数据的物化视图；且业务要求数据实时访问，需要DB2数据持续同步至DB1

诉求 #

寻求一种可避免性能问题的跨库表关联解决方案

内容的提问来源于stack exchange，提问作者VySe