Oracle怎么查询Interval partition分区表内数据

本篇内容介绍了“Oracle怎么查询Interval partition分区表内数据”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1.查看分区信息

select table_name,partition_name,high_value,partition_position,num_rows from dba_tab_partitions where table_owner='CAMS_CORE' and table_name='BP_VOUCHER_HISTORY';

注：dba_tab_partitions | all_tab_partitions | user_tab_partitions表都可以

2.查看分区内数据（以SYS_P82分区为例）

（1）如果知道分区的名字，可以直接查询对应的分区名

SYS@cams> select count(*) from cams_core.bp_voucher_history partition(SYS_P82);

  COUNT(*)

----------

   2844459

（2）如果不知道分区的名字，但是知道分区主键的字段值范围，可以基于分区范围进行查询

SYS@cams> select count(*) from cams_core.bp_voucher_history partition where ac_dte>=to_date('2017-01-01','yyyy-mm-dd') and ac_dte<to_date('2017-02-01','yyyy-mm-dd');

  COUNT(*)

----------

   2844459

（3）如果不知道分区的名字，也不知道分区主键的字段值范围，可以使用PARTITION FOR子句进行查询，比如现在只知道2017-01-15是这个分区的数据

SYS@cams> select count(*) from cams_core.bp_voucher_history partition for(to_date('2017-01-15','yyyy-mm-dd'));

  COUNT(*)

----------

   2844459

1. 注：PARTITION FOR子句可以用于指定分区，而不使用分区的名字。

3.根据分区内的分区字段值，查询Interval Partition分区的名字

因为Oracle并没有提供直接的方法用于指定某个日期属于哪个分区，所以这里要借助于dba_tab_partitions的high_value。但是这里又有一个问题，high_value是Long类型的，不能使用to_date或者to_char函数直接进行转化。

所以，要解决根据分区字段值查询分区的问题，本文的解决方案是把Oracle数据库的Long类型转化为varchar2类型或者date类型，然后进行比对，查找出分区的名字。

set serveroutput on;
--/
declare
  my_var date;
begin
  for x in (select * from dba_tab_partitions where table_owner='CAMS_CORE' and table_name='BP_VOUCHER_HISTORY') loop
  execute immediate 'select '|| x.high_value || 'from dual' into my_var;
  if (my_var = to_date('2017-02-01','yyyy-mm-dd')) then
    dbms_output.put_line(x.partition_name);
  end if;
  end loop;
end;
/

同理，对于使用数字进行自动分区的情况，也可以通过类似的方法进行处理。

从MOS上找到的用于将high_value转化为varvhar2类型的方法，这里进行分享（已经对部分参数进行修改）：

select subname,
       TO_CHAR(y1*100+y2, '9999') || '/' ||
       TO_CHAR(m,  'FM09')        || '/' ||
       TO_CHAR(d,  'FM09')        || ' ' ||
       TO_CHAR(hh, 'FM09')        || ':' ||
       TO_CHAR(mi, 'FM09')        || ':' ||
       TO_CHAR(ss, 'FM09')
from (
SELECT
  o.subname, tp.part#,
  TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))),  3, 2),
'XX')-100 y1,
  TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))),  5, 2),
'XX')-100 y2,
  TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))),  7, 2),
'XX')     m,
  TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))),  9, 2),
'XX')     d,
  TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 11, 2),
'XX')-1   hh,
  TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 13, 2),
'XX')-1   mi,
  TO_NUMBER(SUBSTR(RAWTOHEX(CAST(bhiboundval as raw(8))), 15, 2),
'XX')-1   ss
  from sys.tabpart$ tp, sys.obj$ o, sys.user$ u
  where tp.obj# = o.obj# and o.owner# = u.user#
   and o.name = 'BP_VOUCHER_HISTORY' and u.name = 'CAMS_CORE')
order by part#;

with xml as (
select dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from dba_tab_partitions where table_name = ''BP_VOUCHER_HISTORY'' and table_owner=''CAMS_CORE''') as x
from dual
)
select extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition,
extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
from xml x,
table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws ORDER BY extractValue(rws.object_value, '/ROW/TABLE_NAME');

“Oracle怎么查询Interval partition分区表内数据”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！