John Parott wrote:
>
> I've been told that VarChar fields should go at the end of your table as
> they are the slowest fields to search on. I have a couple of questions
> regarding the best way to order my columns.
>
> 1. If I have a multiple-field index, is there any benefit in having the
> order of the fields in the table the same as the order in the index?
> e.g. Table: fld1, fld2, fld3, fld4, fld5
> Index: fld3, fld4
>
> 2. Is it true that there is a small performance gain in putting varchar
> fields at the end of the table. If I do a lot of searching on varchar
> flds does it make a difference where I put them? Would it help to put
> them near the front of the table in this case?
>
> Thanks for your advice?
> --
> John Parrott
> Elysium Financial Systems
> spo_at_interlog.com
> http://www.interlog.com/~spo
A1.) None whatsoever, as the index is an entirely separate database
object, created without regard to the table's column order.
A2.) The only difference here is when a varchar is the last column and
contains null, the actual length of the stored information is slightly
less than when a varchar appears in any other column position. The
important issue here is to try and make your largest row fit in 1/2 of a
data block to eliminate row chaining and row migration.
--
Rich Woods
Technical Field Support Specialist, Oracle Corporation, USA
The above statements and opinions are my own and do not
necessarily represent those of Oracle Corporation.
Received on Tue Feb 18 1997 - 00:00:00 CST
