在MySQL或PostgreSQL中对partition不是很友好，如分区格式、性能、或索引限制，如pg中的pk索引必须带分区键，但是在oracle中的分区有时设计就不是很科学，就像当初上线时没必要用oracle一样，现在国产数据库上线可能”这个杀鸡焉用宰牛刀”的现象又回重演，如简单的逻辑小型库，非要上线某分布式数据库，恐怕还在沾沾自喜。 oracle partition有的分区表随着业务下线，像最大分区停留在几年前，迁移到其它数据库时，是否可以排除或创建为非分区仅留格式？所以在国产化改造过程并不是简单的迁移，而是一次优化的机会。
single partkey
-- Copyright 2022 Zhang. Weizhao . All rights reserved. More info at http://www.anbob.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
-- List max partition (date style single column range partition)
set serveroutput on size 1000000
SET VERIFY OFF
set linesize 200
set echo off
set lines 2000 pages 1000
col table_owner for a20
col table_name for a30
col column_name for a50
col interval for a10
col HIGH_VALUE for a100
col interval_d for a20
define _SQL_MONITOR = "--"
define _VERSION_11 = "--"
define _VERSION_10 = "--"
col version11 noprint new_value _VERSION_11
col version10 noprint new_value _VERSION_10
select /*+ no_parallel */case
when substr(banner,
instr(banner, 'Release ') + 8,
instr(substr(banner, instr(banner, 'Release ') + 8), ' ')) >=
'10.2' and
substr(banner,
instr(banner, 'Release ') + 8,
instr(substr(banner, instr(banner, 'Release ') + 8), ' ')) < '11.2' then ' ' else '--' end version10, case when substr(banner, instr(banner, 'Release ') + 8, instr(substr(banner, instr(banner, 'Release ') + 8), ' ')) >=
'11.2' then
' '
else
'--'
end version11
from v$version
where banner like 'Oracle Database%';
SELECT table_owner,
table_name,
column_name,
nvl(( TO_DATE (SUBSTR (high_value, INSTR (high_value, '''') + 2, 19),
'yyyy-mm-dd hh24:mi:ss')
- TO_DATE (
SUBSTR (n_high_value, INSTR (n_high_value, '''') + 2, 19),
'yyyy-mm-dd hh24:mi:ss')),-1)
interval_days,
trunc(sysdate-TO_DATE (SUBSTR (high_value, INSTR (high_value, '''') + 2, 19), 'yyyy-mm-dd hh24:mi:ss')) until_days_before,
&_VERSION_11 interval,
high_value
FROM (SELECT table_owner,
table_name,
column_name,
interval,
high_value,
LAG (
high_value)
OVER (PARTITION BY table_owner, table_name
ORDER BY high_value)
n_high_value,
ROW_NUMBER ()
OVER (PARTITION BY table_owner, table_name
ORDER BY high_value DESC)
rnum
FROM (SELECT DBMS_XMLGEN.getxmltype ('
select p.table_owner,
p.table_name,
k.column_name,
&_VERSION_11 p.interval,
p.high_value
from dba_part_key_columns k,
dba_tab_cols c,
dba_tab_partitions p
&_VERSION_11 ,dba_part_tables e
where k.owner = c.owner
and k.column_name = c.column_name
and k.name = c.table_name
and k.owner = p.table_owner
and k.name = p.table_name
&_VERSION_11 and e.owner=p.table_owner and e.table_name=p.table_name and e.interval is null
and (c.data_type = ''DATE'' or
c.data_type like ''TIMESTAMP%'')') AS xml FROM DUAL) p,
XMLTABLE (
'/ROWSET/ROW'
PASSING p.xml
COLUMNS table_owner VARCHAR2 (30) PATH '/ROW/TABLE_OWNER',
table_name VARCHAR2 (30) PATH '/ROW/TABLE_NAME',
column_name VARCHAR2 (50) PATH '/ROW/COLUMN_NAME',
interval VARCHAR2 (30) PATH '/ROW/INTERVAL',
high_value VARCHAR2 (100) PATH '/ROW/HIGH_VALUE')
x
WHERE x.high_value NOT LIKE '%MAXVALUE%'
AND table_name NOT LIKE 'BIN$%'
and (table_owner,table_name) in (select owner, name from dba_part_key_columns group by owner, name having count(*)=1 and owner NOT IN(select schema_name from v$sysaux_occupants) )
)
WHERE rnum = 1
-- and TO_DATE (SUBSTR (high_value, INSTR (high_value, '''') + 2, 19),'yyyy-mm-dd hh24:mi:ss') < (TRUNC (SYSDATE, 'mm') + 365)
and TO_DATE (SUBSTR (high_value, INSTR (high_value, '''') + 2, 19),'yyyy-mm-dd hh24:mi:ss') < (TRUNC (SYSDATE, 'mm') +2)
/
and multi partkeys
-- Copyright 2022 Zhang. Weizhao . All rights reserved. More info at http://www.anbob.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
-- List max partition (partkesy tow columns, 2th column is date style range partition)
set serveroutput on size 1000000
SET VERIFY OFF
set linesize 200
set echo off
set lines 2000 pages 1000
col table_owner for a20
col table_name for a30
col interval for a10
col HIGH_VALUE for a100
col interval_d for a20
define _SQL_MONITOR = "--"
define _VERSION_11 = "--"
define _VERSION_10 = "--"
col version11 noprint new_value _VERSION_11
col version10 noprint new_value _VERSION_10
select /*+ no_parallel */case
when substr(banner,
instr(banner, 'Release ') + 8,
instr(substr(banner, instr(banner, 'Release ') + 8), ' ')) >=
'10.2' and
substr(banner,
instr(banner, 'Release ') + 8,
instr(substr(banner, instr(banner, 'Release ') + 8), ' ')) < '11.2' then ' ' else '--' end version10, case when substr(banner, instr(banner, 'Release ') + 8, instr(substr(banner, instr(banner, 'Release ') + 8), ' ')) >=
'11.2' then
' '
else
'--'
end version11
from v$version
where banner like 'Oracle Database%';
column subkey for a10
column subkey1 for a40 trunc
col high_value for a40 trunc
col n_high_value for a40 trunc
select *
from (
select * from (SELECT table_owner,
table_name,
interval,
high_value,
LAG (
high_value)
OVER (PARTITION BY table_owner, table_name,substr(high_value,1,instr(high_value,',')-1)
ORDER BY high_value)
n_high_value,
ROW_NUMBER ()
OVER (PARTITION BY table_owner, table_name,substr(high_value,1,instr(high_value,',')-1)
ORDER BY high_value DESC)
rnum,
substr(high_value,1,instr(high_value,',')-1) subkey,
substr(high_value,instr(high_value,',')+1) subkey1
FROM (SELECT DBMS_XMLGEN.getxmltype ('
select p.table_owner,
p.table_name,
&_VERSION_11 p.interval,
p.high_value, count(*) over() partkeys
from dba_part_key_columns k,
dba_tab_cols c,
dba_tab_partitions p
&_VERSION_11 ,dba_part_tables e
where k.owner = c.owner
and k.column_name = c.column_name
and k.name = c.table_name
and k.owner = p.table_owner
and k.name = p.table_name
&_VERSION_11 and e.owner=p.table_owner and e.table_name=p.table_name and e.interval is null
and (c.data_type = ''DATE'' or
c.data_type like ''TIMESTAMP%'')') AS xml FROM DUAL) p,
XMLTABLE (
'/ROWSET/ROW'
PASSING p.xml
COLUMNS table_owner VARCHAR2 (30) PATH '/ROW/TABLE_OWNER',
table_name VARCHAR2 (30) PATH '/ROW/TABLE_NAME',
interval VARCHAR2 (30) PATH '/ROW/INTERVAL',
high_value VARCHAR2 (100) PATH '/ROW/HIGH_VALUE')
x
WHERE x.high_value NOT LIKE '%MAXVALUE%'
AND table_name NOT LIKE 'BIN$%'
and (table_owner,table_name) in (select owner, name from dba_part_key_columns group by owner, name having count(*)>1 and owner NOT IN(select schema_name from v$sysaux_occupants) )
)
)
WHERE rnum = 1
例子
SQL> @max_partition.sql
TABLE_OWNER TABLE_NAME COLUMN_NAME INTERVAL_DAYS UNTIL_DAYS_BEFORE INTERVAL HIGH_VALUE
-------------------- ------------------------------ -------------------------------------------------- ------------- ----------------- ---------- ----------------------------------------------------------------------------------------------------
XXX ORDERS ORDER_DATE 3652 5383 NO TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
XXX SALES S_SALEDATE 92 9095 NO TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
XXX SALES_RANGE_HASH S_SALEDATE 92 9095 NO TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SQL>
SQL> @tabpart xxx.orders
TABLE_OWNER TABLE_NAME POS COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW HIGH_VALUE_LENGTH COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ ---------- --- ------------------------------ ---------- ------------------ ---------------------------------------------------------------------------------------------------- ----------------- -------- ------------
XXX ORDERS 1 NO ORD_P1 0 0 TO_DATE(' 1999-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 DISABLED
XXX ORDERS 2 NO ORD_P2 0 0 TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 DISABLED
XXX ORDERS 3 NO ORD_P3 0 0 TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 DISABLED
XXX ORDERS 4 NO ORD_P4 0 0 TO_DATE(' 2000-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 DISABLED
XXX ORDERS 5 NO ORD_P5 0 0 TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 83 DISABLED
— over —