Examination of Neil Chandler’s Post about Adding NOT NULL Columns with DEFAULT values


Neil Chandler has a very interesting post about Adding NOT NULL Columns with DEFAULT values which:

From Oracle 11G, if you: ALTER TABLE table ADD ( column col-type DEFAULT def NOT NULL ) the default isn’t actually added to the data. It’s only added to the meta-data.

I wanted to try a few extra things with his example:

  1. Column Statistics
  2. Check Constraint
  3. Indexing

Preparation

This was done on ORACLE RDBMS 12.1.0.1

Similar to Chandler, I used the following commands to set up the environment:

CREATE TABLE ncha_tab1 (
  pk NUMBER
    CONSTRAINT ncha_tab1_pk PRIMARY KEY,
  c2 TIMESTAMP,
  filler char(1000)
  )
;
INSERT INTO ncha_tab1 (pk, c2, filler)
  SELECT
      rownum,
      systimestamp,
      'a'
    FROM
      dual
    CONNECT BY
      level <= 1e4
;
COMMIT;
ALTER TABLE ncha_tab1
  ADD (
    nnc CHAR(8)
      DEFAULT 'EXPAND'
      CONSTRAINT ncha_tab1_nnc_nn NOT NULL
  )
;

Column Statistics

I collected table statistics for NNC as follows:

EXEC dbms_stats.gather_table_stats(USER, 'NCHA_TAB1', method_opt => 'for columns nnc'); 

The column statistics for NNC are:

AVG_COL_LEN HISTOGRAM NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS
9 FREQUENCY 1 0.00005 0 1

The SQL used was:

SELECT
    avg_col_len,
    histogram,
    num_distinct,
    density,
    num_nulls,
    num_buckets
  FROM
    user_tab_columns
  WHERE
      column_name = 'NNC'
    AND
     table_name = 'NCHA_TAB1'
;

This id very odd: the density is 0.00005 even though there is one (1) value. But the number of distinct values is correct.

Now, let’s add some rare values that are not the default:

UPDATE ncha_tab1
  SET nnc = '127'
  WHERE MOD(pk, 127) = 0
;
UPDATE ncha_tab1
  SET nnc = '511'
  WHERE MOD(pk, 511) = 0
;
UPDATE ncha_tab1
  SET nnc = '1023'
  WHERE MOD(pk, 1023) = 0
;
COMMIT;
EXEC dbms_stats.gather_table_stats(USER, 'NCHA_TAB1', method_opt => 'for columns nnc'); 

The column statistics for NNC are:

AVG_COL_LEN HISTOGRAM NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS
9 FREQUENCY 4 0.00005 0 4

Still, we have that weird density, but the other values are correct.

Check Constraints

Let’s see if we can add a CHECK CONSTRAINT:

ALTER TABLE ncha_tab1
  ADD CONSTRAINT ncha_tab1_nnc_ck
    CHECK (nnc IN ('EXPAND', '127', '511', '1023'))

This was aaded without any problem.

Index

Let’s create an index on NNC and collect statistics as follows:

CREATE INDEX ncha_tab1_nnc_ix
  ON ncha_tab1(nnc)
;
EXEC dbms_stats.gather_index_stats(user, 'NCHA_TAB1_NNC_IX');

The statistics for both PK and NNC are:

INDEX_NAME AVG_DATA_BLOCKS_PER_KEY AVG_LEAF_BLOCKS_PER_KEY BLEVEL CLUSTERING_FACTOR DISTINCT_KEYS LEAF_BLOCKS NUM_ROWS SAMPLE_SIZE
NCHA_TAB1_PK 1 1 1 1429 10000 18 10000 10000
NCHA_TAB1_NNC_IX 383 7 1 1535 4 28 10000 10000

This was generated by the following SQL:

SELECT
    index_name,
    avg_data_blocks_per_key,
    avg_leaf_blocks_per_key,
    blevel,
    clustering_factor,
    distinct_keys,
    leaf_blocks,
    num_rows,
    sample_size
  FROM
    user_indexes
  WHERE
    table_name = 'NCHA_TAB1'
;

This would suggest to me that the index has the default value stored even though the table does not.

Advertisements

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