Overview

The order of columns in a database table is not just a formatting issue; it directly affects storage efficiency and performance.

In particular, when some columns allow NULL values (Nullable), the position of those columns affects the actual storage space (in blocks) occupied by the table.

This analysis experimentally verifies the difference in physical storage efficiency when NULL columns are positioned at the end of the table versus in the middle, and presents principles for column placement to consider during table design.

Theoretical Background

DBMSs (such as Oracle, Tibero, etc.) physically store a row's data in the order of column definitions.

Each column stores data if an actual value exists; if the value is NULL, only internal information called the Null Indicator is stored.

Null Indicator: An internal flag (1 byte or more) indicating that a column is NULL

Key Differences

• When NULL columns are positioned in the middle
  Position information is required for each NULL column because the positions of subsequent columns must be calculated.

• When NULL columns are positioned at the end
  Since there are no columns after them, information about those columns can be omitted.

In other words, the further back NULL columns are placed, the higher the storage efficiency. In large tables, such subtle differences can cause the overall table size to vary by tens to hundreds of megabytes.

Method

Two tables were created with only the position of the NULL columns differing. After inserting the same amount of data, block usage and average row length (AVG_ROW_LEN) were compared.

1. Table with NULL Columns at the End

CREATE TABLE T_END_NULL (
  COL1 VARCHAR2(10)   NOT NULL,
  COL2 VARCHAR2(100)  NOT NULL,
  COL3 VARCHAR2(100)  NULL,
  COL4 VARCHAR2(100)  NULL,
  COL5 VARCHAR2(100)  NULL
);

INSERT INTO T_END_NULL
SELECT TO_CHAR(LEVEL),
       'A',
       NULL, NULL, NULL
FROM DUAL
CONNECT BY LEVEL <= 100000;

COMMIT;

2. Table with NULL Columns in the Middle

CREATE TABLE T_MID_NULL (
  COL1 VARCHAR2(10)   NOT NULL,
  COL2 VARCHAR2(100)  NULL,
  COL3 VARCHAR2(100)  NULL,
  COL4 VARCHAR2(100)  NULL,
  COL5 VARCHAR2(100)  NOT NULL
);

INSERT INTO T_MID_NULL
SELECT TO_CHAR(LEVEL),
       NULL, NULL, NULL,
       'A'
FROM DUAL
CONNECT BY LEVEL <= 100000;

COMMIT;

3. Statistics Collection and Comparison

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'T_END_NULL');
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'T_MID_NULL');

SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN
FROM USER_TABLES
WHERE TABLE_NAME IN ('T_END_NULL','T_MID_NULL');

Experimental Results

※ Actual numbers may vary depending on the environment, but generally, T_MID_NULL uses more blocks.

This result occurs because when NULL columns are in the middle, additional Null Indicator and offset information are stored for each column.

Comparison of Internal Block Structure

When NULL Columns Are at the End

• During block dump, NULL column data is omitted and not displayed.

• Only columns with actual data are stored in each row.

• As a result, more rows can be stored in one block, increasing block utilization.

When NULL Columns Are in the Middle

• During block dump, each NULL column explicitly contains a 00 value (Null Indicator).

• Position information is maintained to calculate offsets for subsequent columns.

• Unnecessary bytes accumulate, reducing the number of rows that can be stored per block.

Analysis Summary

Conclusion

This experiment confirms that the position of NULL columns affects table storage efficiency.

• When NULL columns are in the middle
  Additional Null Indicator and position information for each column cause space waste.

• When NULL columns are at the end
  Unnecessary NULL indicator information is omitted, reducing table size and increasing block utilization.

Therefore, it is advisable to apply the following principles during table design.

Recommended Design Principles

• Place NULL columns at the end of the table whenever possible.
• Place NOT NULL columns that are frequently queried or indexed toward the front.