Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to query the data in the Interval partition partition table by Oracle". In the operation of the actual case, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. View partition information

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'

Note: dba_tab_partitions | all_tab_partitions | user_tab_ partitions table is fine

two。 View data within a partition (take SYS_P82 partition as an example)

(1) if you know the name of the partition, you can query the corresponding partition name directly

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

COUNT (*)

-

2844459

(2) if you do not know the name of the partition, but know the field value range of the partition primary key, you can query based on the partition range.

SYS@cams > select count (*) from cams_core.bp_voucher_history partition where ac_dte > = to_date ('2017-01-01-01)) and ac_dte select count (*) from cams_core.bp_voucher_history partition for (to_date (' 2017-01-15))

COUNT (*)

-

2844459

Note: the PARTITION FOR clause can be used to specify a partition without using the name of the partition.

3. Query the name of the Interval Partition partition based on the partition field value within the partition

Because Oracle does not provide a direct way to specify which partition a date belongs to, you can use dba_tab_partitions 's high_value here. But there is another problem here. High_value is of type Long and cannot be converted directly using to_date or to_char functions.

Therefore, in order to solve the problem of querying partitions according to the value of partition fields, the solution of this paper is to convert the Long type of Oracle database to varchar2 type or date type, and then compare it to find out the name of the partition.

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)) then dbms_output.put_line (x.partition_name); end if; end loop;end;/

Similarly, the use of numbers for automatic partitioning can be handled in a similar way.

The method found on MOS to convert high_value to varvhar2 is shared here (some parameters have been modified):

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')-100y1, TO_NUMBER (SUBSTR (CAST (bhiboundval as raw (8), 5,2), 'XX')-100y2, TO_NUMBER (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 (RAWTOHEX (CAST (bhiboundval as raw (8), 11,2),' XX')-1 hh, TO_NUMBER (SUBSTR (RAWTOHEX (bhiboundval as raw (8)), 13,2), 'XX')-1 mi, TO_NUMBER (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_valuefrom dba_tab_partitions where table_name =' 'BP_VOUCHER_HISTORY'' and table_owner=''CAMS_CORE''') as xfrom 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_valuefrom xml x Table (xmlsequence (extract (x.x,'/ ROWSET/ROW')) rws ORDER BY extractValue (rws.object_value,'/ ROW/TABLE_NAME')

This is the end of the content of "how to query the data in the Interval partition partition table by Oracle". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.