Hi,
I have a quite detailed question about upgrading DST time zone files on databases with large tables.
We have customers with large partitioned tables (~ 25.000.000.000 rows, e.g. 25 billion rows) with columns of timestamp with time zone. We like to upgrade DST time zone file.
Database : 11g R2
DST-Version: 4 => 11
Ohter db settings should be regarded as generic.
According to doc we can upgrade table by table, we can use parallel option as well,
using the following command:
DECLARE
x NUMBER;
BEGIN
DBMS_DST.UPGRADE_TABLE( x, 'MHTEST.TSTAMP_LOCK_TEST' );
END;
/
This command runs (internal) for example the following update command:
UPDATE /*+ NO_DST_UPGRADE_INSERT_CONV */ "MHTEST"."TSTAMP_LOCK_TEST" "T" SET
"T".TSTAMP = ORA_DST_CONVERT("T".TSTAMP)
WHERE
ORA_DST_AFFECTED("T".TSTAMP) = 1;
Afterward the table will be marked as upgraded. This is visible in DBA_TSTZ_TABLES (internal tab$.property will be updated)
Question 1: Is there any supported option to skip upgrade process for a specific table?
- we are sure no data is affected, e.g. ORA_DST_AFFECTED("T".TSTAMP) = 1 => FALSE for every row in the table
- we do not regard to add functional indexes (like ORA_DST_AFFECTED("T".TSTAMP) to fasten up update (because of the large table)
- we like to shorten upgrade window because of negative impact on concurrency issues and index usage (see performance impact on dst_upgrade_insert_conv)
Question 2: I tried the following "hack" which "seems" to work. Is this a possible Solution:
a) build an empty "stage" table for each partition of the large table
b) exchange all partition with alter table exchange partition
c) upgrade large table (very fast because no rows)
d) exchange all partitions back
e) upgrade stage tables (again very fast), or drop stage tables
Any comments will be appreciated
Martin