In Oracle, why are the columns of type LONG created last?

Question

This Oracle documentation page mentions

... columns of type LONG are created last

but doesn't say why.

What can the reason be to store them at the end of a row?

Answer 1

A LONG in Oracle is quite different to a long in C or C++. In Oracle, a LONG is somewhat like VARCHAR2 but with an even bigger maximum length (2GB, I believe?)

The reason it's stored last is because if you had a table that was (INT a, LONG foo, INT b) and it was stored in that order, then in order to fetch the value of the b column, it would have to read in all the data page that's taken up by the LONG column. By storing it at the end, it doesn't have to read in all that data if it doesn't have to.

Answer 2

(Not an expert at Oracle - just had to look up what a LONG is)

In general, LONGs (and other BLOB types) are not going to be subject to search conditions (in JOINs or WHERE clauses). As such, it makes sense to shift them to the end, so that the other columns (which are more likely to be queried together) are located closely together. Then, only these other columns need be read whilst assessing whether this particular row matches the query criteria.

Answer 3

It's worth pointing out that this is only INTERNAL storage.

From the developer's point of view, the LONG can be the third, sixth or first column, or wherever. And you can still add columns to a table with a LONG. That said, you almost certainly shouldn't be creating a table with a LONG any more. They should be CLOBs (or BLOBs rather than a LONG RAW).

create table test_long (id number, val long, create_date date);

desc test_long
           Name                           Null?    Type
           ------------------------------ -------- --------------
    1      ID                                      NUMBER
    2      VAL                                     LONG
    3      CREATE_DATE                             DATE

My guess (and unless someone who worked for Oracle speaks up, it is speculation) is that it is because LONGs were/are most likely to 'not fit' with the row in a block. A row with a LONG would have a good chance of needing to be more than 1 block in size. The LONG is most likely to be split (because of its size). By pushing that to the end, it is much more likely that all the other columns would sit together on one block.