Sherlock Holmes and Partition Exchange

In my job as an Oracle consultant I sometimes feel like Sherlock Holmes. It’s not always easy to find the evidence or even a logical explanation for a strange behavior of SQL.

Today I had such a case where the culprit was hard to find. But fortunately, some research on the data dictionary and my assistant Dr. Watson – sorry, Dr. Google – helped to solve the problem. A PL/SQL package that generates several EXCHANGE PARTITION statements failed with the error message

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

This error usually occurs when the table structure of the partitioned table and the work table does not match. In other words: When there is a difference in column names, data types, indexes or constraints between the two tables, a partition exchange will not work.

Searching for Evidence

I compared all relevant information from the data dictionary views, but I did not find any difference. Very strange.

As I remembered from a similar situation several years ago, a potential issue could be unused columns. When a column of the partitioned table is not dropped directly, but set to unused, the partition exchange will fail if this unused column does not exist on the work table. I checked DBA_TAB_COLS (instead of DBA_TAB_COLUMNS) because this data dictionary views contains unused columns:

SELECT column_id, column_name, hidden_column
 FROM user_tab_cols
 WHERE table_name = 'PART_TABLE'

Unused columns are displayed with HIDDEN_COLUMN = ‘YES’. But in this situation, this was not the case. I checked and compared all constraints, indexes, data types, length and default values – everything seemed to be identical. Finally, I found an evidence in the data dictionary table COL$: The PROPERTY value was 0 for all columns – with one exception:

SELECT c.name, c.property
 FROM SYS.col$ c
 JOIN SYS.obj$ o ON (o.obj# = c.obj#)
WHERE o.NAME = 'PART_TABLE'
NAME      PROPERTY
--------- ----------
ID 0
DT 0
N1 0
N2 0
N3 1073741824

I had absolutely no idea what this property value 1073741824 means, but I found the explanation in the blog post COL$.PROPERTY = 1073741824 of Martin Nash, and finally on My Oracle Support in the document ORA-14097 At Exchange Partition After Adding Column With Default Value (Doc ID 1334763.1).

Cause of the Issue

The reason was a new column that was added to the partitioned table. Since Oracle 11g it is possible to add a column with NOT NULL and a DEFAULT clause in one step, as described in the documentation: If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.

This feature is very useful, but causes problems in combination with a partition exchange, as shown in the following example:

-- Create partitioned table:
CREATE TABLE part_table (id NUMBER NOT NULL ,dt DATE NOT NULL ,n1 NUMBER NOT NULL ,n2 NUMBER NOT NULL) PARTITION BY RANGE(dt) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION p0 VALUES LESS THAN (TO_DATE('01.01.1990', 'DD.MM.YYYY')));

Table created.
-- Add additional column with NOT NULL and DEFAULT:
ALTER TABLE part_table
ADD n3 NUMBER DEFAULT 0 NOT NULL;

Table altered.
-- Create work table with same structure:
CREATE TABLE work_table AS
SELECT * FROM part_table WHERE 1 = 2;

Table created.
-- Partition exchange:
ALTER TABLE part_table
EXCHANGE PARTITION p0
WITH TABLE work_table
INCLUDING INDEXES WITHOUT VALIDATION;

ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Oracle describes different solutions in Doc ID 1334763.1. The simplest is to turn off the default value optimization feature with an undocumented parameter before a new column is added:

ALTER SESSION SET "_add_col_optim_enabled" = FALSE;

ALTER TABLE part_table
ADD n3 NUMBER DEFAULT 0 NOT NULL;

In the current situation, this and all other solutions were not feasible because I wanted to avoid to recreate and copy a table with about 58 million rows and more than 300 partitions. The solution was quite simple – and faster than to rebuild the whole table:

ALTER TABLE part_table MODIFY n3 NULL;
ALTER TABLE part_table MODIFY n3 NOT NULL;

After disabling and enabling the NOT NULL constraint, the partition exchange worked as it always did before the new column was added.

Advertisements

3 thoughts on “Sherlock Holmes and Partition Exchange

  1. Proof of table rebuild – about a minute for ‘modify null’ for about 1M records:

    drop table tst_source purge ;
    create table tst_source nologging as
    select rownum id, text
    from dba_source;

    set timing on
    ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT ‘N’ NOT NULL);
    set timing off
    Elapsed: 00:00:00.009

    set timing on
    ALTER TABLE tst_source modify name NULl;
    set timing off
    Elapsed: 00:01:07.221

    exec dbms_stats.gather_table_stats(user, ‘tst_source’);

    select count(*) from tst_source where name is NULL ;

    COUNT(*)
    ———-
    0

    select count(*) from tst_source;
    COUNT(*)
    ———-
    1186729

    Like

  2. @Sam M: I did some tests today on a test table with 17 million rows:

    ALTER SESSION SET “_add_col_optim_enabled” = FALSE;

    ALTER TABLE test ADD (status VARCHAR2(2) DEFAULT ‘ok’ NOT NULL);
    — 3 minutes 49 seconds
    ALTER TABLE test MODIFY (status NULL);
    — 0.16 seconds

    ALTER SESSION SET “_add_col_optim_enabled” = TRUE; — default in 11g

    ALTER TABLE test ADD (TEST_STATUS VARCHAR2(2) DEFAULT ‘ok’ NOT NULL);
    — 0.2 seconds
    ALTER TABLE test MODIFY (TEST_STATUS VARCHAR2(2) NULL);
    — 3 minutes 57 seconds

    You’re right, MODIFY NULL is expensive, but a reorg of the whole table would take more time.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s