DBLINK获取PARTITION数据的反思

背景：本地环境A库，远端环境B库。

      远端环境B库中的表aim_table

           本地dblink连接名称odsb

      B库aim_table按照字段date_id做range分区

      其中date_id为date类型，按天分区

      A库中的ETL程序需要根据按天的业务日期从B库中取指定业务日期的数据，其中程序的业务日期传入参数均为8位字符串，如：20141026

 

问题思路分析：

      假设需要取2014年10月26日的数据集

1、  A库中做如下查询

Select * from aim_table@odsb where to_char(date_id,’yyyymmdd’)=’20141026’;

这里可以发现查询效率很低。

2、  尝试使用本地创建表的方式获取B库的数据

Create table test_table

Select * from aim_table@odsb where to_char(date_id,’yyyymmdd’)=’20141026’;

同样这里的创建时间较长，效率低。

3、  将同样的查询语句在B库中直接执行

Select * from aim_table where to_char(date_id,’yyyymmdd’)=’20141026’;

查询时间长，查询效率低，与1、2中的查询效率无差异

4、  想通过指定分区的方式进行数据查询

Select * from aim_table partition(p20141026);

查询时间缩短，效率提升明显。

5、  由于4查询效率提升明显，故希望在A库通过dblink方式做指定分区查询

Select * from aim_table@odsb partition(p20141026);

ORA-14100: partition extended table name cannot refer to a remote object

查询报错。故表示dblink方式不能进行指定分区查询的操作。

6、  尝试在A库创建同义词的方式，进行指定分区查询的操作。

Create synonym sy_aim_table for aim_table@odsb;

Select count(1) from sy_aim_table;

Select count(1) from sy_aim_table partition(P20141026);

通过以上两个查询发现，统计记录条数一致，表示同义词方式指定分区查询没有效果，但查询方式没有报错，故数据库可能将 partition(P20141026)当做别名的方式识别。

7、  若能够在远端B库动态创建视图的方式获取指定分区数据，并且在A库创建B库视图的同义词信息，也可以获取数据。

如何在B库根据range分区的方式获取指定的分区名称？故有一下测试程序

declare

  v_sql  varchar2(1000);

  v_date date;

begin

  for i in (select high_value, partition_name

              from all_tab_partitions

             where table_name = 'AIM_TABLE') loop

    v_sql := 'select ' || i.high_value || ' from dual';

    execute immediate v_sql

      into v_date;

    if (to_date(&i_date, 'yyyymmdd') < v_date and

       to_date(&i_date, 'yyyymmdd') + 1 = v_date) then

      dbms_output.put_line(i.partition_name);

    end if;

  end loop;

end;

按照以上程序可以根据入参的值获取指定分区名称，故可以使用动态语句的方式创建需要的视图信息。

可以在A库通过dblink方式调用B库的动态生成视图的程序进行视图更新。

需要注意的是使用dblink方式调用远端库的程序时，结束需要进行绘画关闭的操作

dbms_session.close_database_link(odsb);

（其实完成到这一步的时候作者当时相当富有成就感）

8、  按照以上的分析思路，基本上可以满足此次的需求，但我们重新看下远端表的表结构能够发现，远端表的分区按照日期创建的range分区。

通过重新查看我们之前的查询sql可以发现

Select * from aim_table@odsb where to_char(date_id,’yyyymmdd’)=’20141026’;

这里每次将分区关键字进行了to_char转换的操作在同指定的业务日期字符串进行比较。

通过查阅文档可以知道分区表的分区关键字默认存在索引信息。故这里我们调整下查询思路：

Select * from aim_table@odsb where date_id = to_date(‘20141026’,’yyyymmdd’);

这里查询效率时间短，效率提升明显。

由于是远端的DBLINK方式，故增加hint参数

Select /*+driving_site(a)*/* from aim_table@odsb  a where date_id = to_date(‘20141026’,’yyyymmdd’);

 

以上为这次需求的问题分析思路以及解决方案！

转载于:https://my.oschina.net/OracleJay/blog/338181