• Dec
    21
    2011
  • Does order of columns in an Oracle table matters?
    Post By: admin

Yes, if table has columns defined for future usage and currently storing null values and No, if columns have data in them.
Here is a simple testcase that I performed on Oracle database 11g Release 1 (11.1.0.7). I created two tables (TAB_ORDERED_COLS,TAB_UNORDERED_COLS) with 7 columns. Tables have data in 2 columns and rest 5 columns contain NULL data. In table TAB_ORDERED_COLS, columns meant for future use and having NULL values are defined in the last where as columns with NULL values in table TAB_UNORDERED_COLS are in the middle order.

CREATE TABLE TAB_ORDERED_COLS
(
col1 NUMBER,
col2 VARCHAR2(50),
col3 CHAR(50),
col4 CHAR(50),
col5 CHAR(50),
col6 CHAR(50),
col7 CHAR(50)
)
/

CREATE TABLE TAB_UNORDERED_COLS
(
col1 NUMBER,
col3 CHAR(50),
col4 CHAR(50),
col5 CHAR(50),
col6 CHAR(50),
col7 CHAR(50),
col2 VARCHAR2(50)
)

Both tables have same data set as shown below:

DECLARE
x NUMBER := 100000;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO TAB_ORDERED_COLS VALUES(1,1||'Inside',NULL,NULL,NULL,NULL,NULL);
END LOOP;
COMMIT;
END;
/

DECLARE
x NUMBER := 100000;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO TAB_UNORDERED_COLS VALUES(i,NULL,NULL,NULL,NULL,NULL,i||'Inside');
END LOOP;
COMMIT;
END;
/

SQL> ANALYZE TABLE TAB_UNORDERED_COLS COMPUTE STATISTICS;

Table analyzed.

SQL>ANALYZE TABLE TAB_ORDERED_COLS COMPUTE STATISTICS;

Table analyzed.

Lets see the storage space consumed by table TAB_ORDERED_COLS, which has columns with NULL values defined as the trailing columns in the last.

SQL> SELECT file_id,block_id,blocks,extent_id FROM dba_extents
WHERE segment_name = 'TAB_ORDERED_COLS' order by extent_id;
2
FILE_ID BLOCK_ID BLOCKS EXTENT_ID
---------- ---------- ---------- ----------
9 20184 8 0
2 41120 8 1
9 20192 8 2
2 41136 8 3
9 20200 8 4
2 41144 8 5
9 20208 8 6
2 41160 8 7
9 20352 8 8
2 41168 8 9
9 20360 8 10

FILE_ID BLOCK_ID BLOCKS EXTENT_ID
---------- ---------- ---------- ----------
2 41200 8 11
9 20376 8 12
2 41208 8 13
9 20384 8 14
2 41312 8 15
9 28672 128 16

17 rows selected.

SQL>

SQL> SELECT blocks, empty_blocks,avg_space, num_freelist_blocks
FROM user_tables
WHERE table_name = 'TAB_ORDERED_COLS'; 2 3

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
244 12 1513 0

SQL>

Table TAB_ORDERED_COLS used 1.89 Mb storage as per following formula.
Space Used = (244 * 8192) – (12 * 1513) = 1980692 Bytes = 1.89 Mb
Now lets looks how much storage table TAB_UNORDERED_COLS used, which has columns with NULL values defined in the middle of column order for this table.

SQL> SELECT file_id,block_id,blocks,extent_id FROM dba_extents
WHERE segment_name = 'TAB_UNORDERED_COLS' order by extent_id;
2
FILE_ID BLOCK_ID BLOCKS EXTENT_ID
---------- ---------- ---------- ----------
9 20400 8 0
2 41328 8 1
9 20408 8 2
2 41336 8 3
9 20416 8 4
2 41472 8 5
9 20424 8 6
2 41496 8 7
9 20432 8 8
2 41504 8 9
9 20440 8 10

FILE_ID BLOCK_ID BLOCKS EXTENT_ID
---------- ---------- ---------- ----------
2 41528 8 11
9 20448 8 12
2 41536 8 13
9 20464 8 14
2 41552 8 15
9 29312 128 16
2 45440 128 17

18 rows selected.

SQL>
SQL> SELECT blocks, empty_blocks,avg_space, num_freelist_blocks
FROM user_tables
WHERE table_name = 'TAB_UNORDERED_COLS'; 2 3

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
370 14 833 0

Table TAB_UNORDERED_COLS used 2.88 Mb storage as per following formula.
Space Used = 370 * 8192 – (14 * 833) = 2.88 Mb
Table TAB_UNORDERED_COLS used approx 1 Mb extra space to store the same data set and the only difference was ordering of the columns.
Now question arises why this is happening?
Oracle does not store trailing columns having NULL values in the row piece inside the Oracle data block hence table TAB_ORDERED_COLS using less space than table TAB_UNORDERED_COLS to store the same data set.