【Oracle】sql循环遍历查询结果

轨迹表特点：

一天一张轨迹表，存储武汉市全市渣土车数据，名称以 GA_GPS_HISTORY_开头，大概一天会有几千台车，每天数据量大概几百万条记录至千万条记录，目前存储有5个月的数据。

需求：

想要查询从轨迹表产生以来，有多少车出现过轨迹

利用sql，实现sql语句的产生，因为人工操作的话，表的数量大，手动写sql比较麻烦

因此采用Oracle自带的sql，生成相应的sql语句

• 实例：

declare  s_sql  clob := '' ;--    此处需要定义一个clob字段，若定义一个varchar2类型或者nvarchar2类型的，则会显示字符串长度超出(varchar2最大长度4000）

begin

for  wf  in ( select  tname  from  tab  where  tname  like  'GA_GPS_HISTORY_%' )--    该sql用于查询当前用户下的表格

   loop

    s_sql:=s_sql|| 'select distinct hp from ' ||wf.tname|| ' union all ' ;

   end  loop ;

    dbms_output.put_line(s_sql);

--    select s_sql from dual;

end ;

运行上面的sql语句，最终生成一个如下的sql语句：

select count ( distinct hp) from ( select distinct hp from GA_GPS_HISTORY_2016_11_02 union all select distinct hp from GA_GPS_HISTORY_2016_11_03 union all select distinct hp from GA_GPS_HISTORY_2016_11_04 union all select distinct hp from GA_GPS_HISTORY_2016_11_05 union all select distinct hp from GA_GPS_HISTORY_2016_11_06 union all select distinct hp from GA_GPS_HISTORY_2016_11_07 union all select distinct hp from GA_GPS_HISTORY_2016_11_08 union all select distinct hp from GA_GPS_HISTORY_2016_11_09 union all select distinct hp from GA_GPS_HISTORY_2016_11_10 union all select distinct hp from GA_GPS_HISTORY_2016_11_11 union all select distinct hp from GA_GPS_HISTORY_2016_11_12 union all select distinct hp from GA_GPS_HISTORY_2016_11_13 union all select distinct hp from GA_GPS_HISTORY_2016_11_14 union all select distinct hp from GA_GPS_HISTORY_2016_11_15 union all select distinct hp from GA_GPS_HISTORY_2016_11_16 union all select distinct hp from GA_GPS_HISTORY_2016_11_17 union all select distinct hp from GA_GPS_HISTORY_2016_11_18 union all select distinct hp from GA_GPS_HISTORY_2016_11_19 union all select distinct hp from GA_GPS_HISTORY_2016_11_20 union all select distinct hp from GA_GPS_HISTORY_2016_11_21 union all select distinct hp from GA_GPS_HISTORY_2016_11_22 union all select distinct hp from GA_GPS_HISTORY_2016_11_23 union all select distinct hp from GA_GPS_HISTORY_2016_11_24 union all select distinct hp from GA_GPS_HISTORY_2016_11_25 union all select distinct hp from GA_GPS_HISTORY_2016_11_26 union all select distinct hp from GA_GPS_HISTORY_2016_11_27 union all select distinct hp from GA_GPS_HISTORY_2016_11_28 union all select distinct hp from GA_GPS_HISTORY_2016_11_29 union all select distinct hp from GA_GPS_HISTORY_2016_11_30 union all select distinct hp from GA_GPS_HISTORY_2016_12_01 union all select distinct hp from GA_GPS_HISTORY_2016_12_02 union all select distinct hp from GA_GPS_HISTORY_2016_12_03 union all select distinct hp from GA_GPS_HISTORY_2016_12_04 union all select distinct hp from GA_GPS_HISTORY_2016_12_05 union all select distinct hp from GA_GPS_HISTORY_2016_12_06 union all select distinct hp from GA_GPS_HISTORY_2016_12_07 union all select distinct hp from GA_GPS_HISTORY_2016_12_08 union all select distinct hp from GA_GPS_HISTORY_2016_12_09 union all select distinct hp from GA_GPS_HISTORY_2016_12_10 union all select distinct hp from GA_GPS_HISTORY_2016_12_11 union all select distinct hp from GA_GPS_HISTORY_2016_12_12 union all select distinct hp from GA_GPS_HISTORY_2016_12_13 union all select distinct hp from GA_GPS_HISTORY_2016_12_14 union all select distinct hp from GA_GPS_HISTORY_2016_12_15 union all select distinct hp from GA_GPS_HISTORY_2016_12_16 union all select distinct hp from GA_GPS_HISTORY_2016_12_17 union all select distinct hp from GA_GPS_HISTORY_2016_12_18 union all select distinct hp from GA_GPS_HISTORY_2016_12_19 union all select distinct hp from GA_GPS_HISTORY_2016_12_20 union all select distinct hp from GA_GPS_HISTORY_2016_12_21 union all select distinct hp from GA_GPS_HISTORY_2016_12_22 union all select distinct hp from GA_GPS_HISTORY_2016_12_23 union all select distinct hp from GA_GPS_HISTORY_2016_12_24 union all select distinct hp from GA_GPS_HISTORY_2016_12_25 union all select distinct hp from GA_GPS_HISTORY_2016_12_26 union all select distinct hp from GA_GPS_HISTORY_2016_12_27 union all select distinct hp from GA_GPS_HISTORY_2016_12_28 union all select distinct hp from GA_GPS_HISTORY_2016_12_29 union all select distinct hp from GA_GPS_HISTORY_2016_12_30 union all select distinct hp from GA_GPS_HISTORY_2016_12_31 union all select distinct hp from GA_GPS_HISTORY_2017_01_01 union all select distinct hp from GA_GPS_HISTORY_2017_01_02 union all select distinct hp from GA_GPS_HISTORY_2017_01_03 union all select distinct hp from GA_GPS_HISTORY_2017_01_04 union all select distinct hp from GA_GPS_HISTORY_2017_01_05 union all select distinct hp from GA_GPS_HISTORY_2017_01_06 union all select distinct hp from GA_GPS_HISTORY_2017_01_07 union all select distinct hp from GA_GPS_HISTORY_2017_01_08 union all select distinct hp from GA_GPS_HISTORY_2017_01_09 union all select distinct hp from GA_GPS_HISTORY_2017_01_10 union all select distinct hp from GA_GPS_HISTORY_2017_01_11 union all select distinct hp from GA_GPS_HISTORY_2017_01_12 union all select distinct hp from GA_GPS_HISTORY_2017_01_13 union all select distinct hp from GA_GPS_HISTORY_2017_01_14 union all select distinct hp from GA_GPS_HISTORY_2017_01_15 union all select distinct hp from GA_GPS_HISTORY_2017_01_16 union all select distinct hp from GA_GPS_HISTORY_2017_01_17 union all select distinct hp from GA_GPS_HISTORY_2017_01_18 union all select distinct hp from GA_GPS_HISTORY_2017_01_19 union all select distinct hp from GA_GPS_HISTORY_2017_01_20 union all select distinct hp from GA_GPS_HISTORY_2017_01_21 union all select distinct hp from GA_GPS_HISTORY_2017_01_22 union all select distinct hp from GA_GPS_HISTORY_2017_01_23 union all select distinct hp from GA_GPS_HISTORY_2017_01_24 union all select distinct hp from GA_GPS_HISTORY_2017_01_25 union all select distinct hp from GA_GPS_HISTORY_2017_01_26 union all select distinct hp from GA_GPS_HISTORY_2017_01_27 union all select distinct hp from GA_GPS_HISTORY_2017_01_28 union all select distinct hp from GA_GPS_HISTORY_2017_01_29 union all select distinct hp from GA_GPS_HISTORY_2017_01_30 union all select distinct hp from GA_GPS_HISTORY_2017_01_31 union all select distinct hp from GA_GPS_HISTORY_2017_02_01 union all select distinct hp from GA_GPS_HISTORY_2017_02_02 union all select distinct hp from GA_GPS_HISTORY_2017_02_03 union all select distinct hp from GA_GPS_HISTORY_2017_02_04 union all select distinct hp from GA_GPS_HISTORY_2017_02_05 union all select distinct hp from GA_GPS_HISTORY_2017_02_06 union all select distinct hp from GA_GPS_HISTORY_2017_02_07 union all select distinct hp from GA_GPS_HISTORY_2017_02_08 union all select distinct hp from GA_GPS_HISTORY_2017_02_09 union all select distinct hp from GA_GPS_HISTORY_2017_02_10 union all select distinct hp from GA_GPS_HISTORY_2017_02_11 union all select distinct hp from GA_GPS_HISTORY_2017_02_12 union all select distinct hp from GA_GPS_HISTORY_2017_02_13 union all select distinct hp from GA_GPS_HISTORY_2017_02_14 union all select distinct hp from GA_GPS_HISTORY_2017_02_15 union all select distinct hp from GA_GPS_HISTORY_2017_02_16 union all select distinct hp from GA_GPS_HISTORY_2017_02_17 union all select distinct hp from GA_GPS_HISTORY_2017_02_18 union all select distinct hp from GA_GPS_HISTORY_2017_02_19 union all select distinct hp from GA_GPS_HISTORY_2017_02_20 union all select distinct hp from GA_GPS_HISTORY_2017_02_21 union all select distinct hp from GA_GPS_HISTORY_2017_02_22 union all select distinct hp from GA_GPS_HISTORY_2017_02_23 union all select distinct hp from GA_GPS_HISTORY_2017_02_24 union all select distinct hp from GA_GPS_HISTORY_2017_02_25 union all select distinct hp from GA_GPS_HISTORY_2017_02_26 union all select distinct hp from GA_GPS_HISTORY_2017_02_27 union all select distinct hp from GA_GPS_HISTORY_2017_02_28 union all select distinct hp from GA_GPS_HISTORY_2017_03_01 union all select distinct hp from GA_GPS_HISTORY_2017_03_02 union all select distinct hp from GA_GPS_HISTORY_2017_03_03 union all select distinct hp from GA_GPS_HISTORY_2017_03_04 union all select distinct hp from GA_GPS_HISTORY_2017_03_05 union all select distinct hp from GA_GPS_HISTORY_2017_03_06) where hp like '鄂A%'

本次试验中，因为每张轨迹表的数据量动辄几百万，甚至千万，因此distinct查询很慢，整个过程持续了15分钟左右

待解决问题：

怎么提高查询效率？