Oracle Performance Tuning Tips

Consider table column order

The order of columns in a table can have an impact on performance. There are two factors to consider.

Firstly, Oracle rows are stored as a row header followed by column data. The row header contains a flag byte, lock byte and column count, then for each column there is a column length followed by the column data. To access the value of any column in a row, Oracle has to first examine the length bytes of all the preceding columns. This is a very quick and efficient operation, but it is done with such frequency that it nevertheless does have an impact on performance.

In the following example we will create a 10-column table and insert enough rows to fill a single 2K database block. We will then compare the response time of repeatedly accessing the first column and the last column.

SQL> create table small (
  2    n0 number,
  3    n1 number,
  4    n2 number,
  5    n3 number,
  6    n4 number,
  7    n5 number,
  8    n6 number,
  9    n7 number,
 10    n8 number,
 11    n9 number
 12  ) pctfree 0;

Table created.

SQL> begin
  2    for i in 1..78 loop
  3      insert into small values (0,0,0,0,0,0,0,0,0,0);
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> set timing on
SQL> declare
  2    n number;
  3  begin
  4    for i in 1..1000000 loop
  5      select sum(n0) into n from small;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:07:437.30
SQL> declare
  2    n number;
  3  begin
  4    for i in 1..1000000 loop
  5      select sum(n9) into n from small;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:08:482.13

This demonstrates that it took more than 10% longer to access the 10th column in this table than it did to access the 1st column. The principle is simple. Place frequently accessed columns early in the table column order. Remember that column values will normally be accessed more frequently for WHERE clause predicate evaluation, than for select-list expression evaluation. However, column values that appear in important driving predicates may be accessed relatively infrequently if the table is consistently accessed by rowid via an index on that column. In particular, primary key columns are seldom the most intensively accessed table columns, and should not normally be first in the table column order.

The second aspect of table column order that impacts performance is the position of columns that frequently contain NULLs. Oracle normally requires one byte to represent each NULL, except that it does not store trailing NULLs in a data row. This may be demonstrated as follows.

SQL> create table null_order (
  2    column1 number,
  3    column2 number,
  4    column3 number
  5  );

Table created.

SQL> insert into null_order (column2) values (0);

1 row created.

SQL> select header_file, header_block from dba_segments
  2  where segment_name = 'NULL_ORDER' and owner = user;

HEADER_FILE HEADER_BLOCK
----------- ------------
          3        50010

SQL> alter system dump datafile 3 block 50011;

System altered.

The first and third columns in the one row of this table are NULL. The following extract from the block dump shows how they are represented.

block_row_dump:
tab 0, row 0, @0x7b2
tl: 6 fb: --H-FL-- lb: 0x1 cc: 2
col  0: *NULL*
col  1: [ 1]  80
end_of_block_dump

The NULL in the first column is stored explicitly and increases the row length by one byte. But the NULL in the third column does not need to be stored explicitly because there are no subsequent non-NULL values in the row. When reading a table row, Oracle is able to infer that any trailing columns that are not represented explicitly just contain NULLs.

Placing columns that frequently contain NULLs last in the table column order, minimizes the average row length and optimizes the table data density, which of course benefits performance. This column ordering also minimizes the number of column length bytes that need to be navigated to access the non-NULL column values. This also benefits performance as discussed above.