Re: Order of fields in a table.

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3F059BF9.DC7B9833_at_exxesolutions.com...
> ben brugman wrote:
>
> > Order of fields in a table.
> >
> > Do you insert fields in a table, or do you
> > append all new fields to the end.
> >
> > I prefer to insert the fields, but inserting a field
> > can be cumbersome and takes some time
> > in a real live database. I am aware that applications
> > should not use any order, but there are also humans
> > seeing the order of the fields in the database.
> > (Checking which fields exist and using general utilities
> > etc.).
> >
> > What is common in your world, adding or inserting ?
> >
> > ben brugman.
>
> Time spent worrying about the ordering of columns in a relational table
> are just wasted.
>
> If you make more than one change a year there is something wrong with
> the design process.
> --

Not entirely true, actually.

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).

OK, so LONGs are deprecated and LOBS don't suffer the same problem because they are stored out-of-line. So what about IOTs then?

With an IOT, you can set PCTHRESHOLD... which will cause all columns after the INCLUDING clause, or all columns after the primary key if one isn't specified, to be 'snapped off' and stored separately in an overflow tablespace if a row's length exceeds the specified percentage of a block's space. If you don't order your columns appropriately, then the 'wrong' data is going to get snapped off and stored elsewhere.

I would call an IOT a relational table. And (especially with 9i) their appropriateness for widespread use has come on leaps and bounds since they were first invented in 8.0.

So the days of not caring about column order are not entirely over!

(For heap tables, I agree with you, though).

~QM

> Daniel Morgan
> http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
>
Received on Fri Jul 04 2003 - 15:46:35 CDT