反思DBLINK在获取分区数据过程中的体验与理解
背景:本地环境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’);
以上为这次需求的问题分析思路以及解决方案!