Re: Order of fields in a table.

That's a good call Tanel... that's an optimization I wasn't aware of, and probably explains why multiple LONGS or LONG RAWS are not permitted within a single table.

Do you know if it was there in 7.0, though? I seem to recall documentation from that era urging users to make sure the LONG was the last column for themselves, which suggests that it wasn't there then.

But whatever: in the context of the current discussion, it rather goes to show that column order *can* be significant.

~QM

  "Tanel Poder" <tanel@@peldik.com> wrote in message news:3f06eba5_1_at_news.estpak.ee...

> If you use LONG datatypes, they should sensibly be the last columns in the
> table, because access to them is sequential and they are always stored
> in-line with your other data (meaning getting at that other data would
> require you to wade through the entire LONG if it was stored in a column
> positioned before it).

  Actually LONGs are *always* stored as last columns in row, no matter what you specify in create table statement.   This is an Oracle internal optimization.

  SQL> create table l (a number, b long, c number);

  Table created.

  SQL> select obj# from obj$ where name = 'L';

        OBJ#

       30120

  SQL> select name, col#, segcol# from col$ where obj# = 30120;

  NAME                                 COL#    SEGCOL#
  ------------------------------ ---------- ----------
  A                                       1          1
  B                                       2          3
  C                                       3          2

  SEGCOL# shows the physical column ordering, COL# shows ordering what we see using describe command.

  Tanel. Received on Sat Jul 05 2003 - 20:44:20 CDT