+ Reply to Thread
Results 1 to 8 of 8

Oracle - Column Order

  1. Oracle - Column Order


    Hi,

    Is there a way to change the order of the columns in a table? I know
    that you can change the name via using the COL$ dictionary table. Is
    there also a sneaky way like that to change the order of the columns?

    Thanks,

    Arthur


  2. Re: Oracle - Column Order

    Updating the dictionary is not a good option.
    create a new table with CTAS and recreate your constraints etc.
    or
    Just use a view.
    But, why do you want to change the columns?


  3. Re: Oracle - Column Order

    On Mon, 17 Oct 2005 13:46:11 -0700, amerar interested us by writing:

    >
    > Hi,
    >
    > Is there a way to change the order of the columns in a table? I know
    > that you can change the name via using the COL$ dictionary table. Is
    > there also a sneaky way like that to change the order of the columns?
    >


    Using sneaky ways to bypass the internal structures is a quick way of
    violating your support agreement. Hope you are not contemplating this
    for a production system.

    I would consider using "SELECT *" and depending on column order to be
    very unprofessional, sloppy and lazy for anything other than a demo or
    prototype environment. Also it will lead to surprises if you ever decide
    to use Oracle for what it is worth instead of just a big data bucket.


    --
    Hans Forbrich
    Canada-wide Oracle training and consulting
    mailto: Fuzzy.GreyBeard_at_gmail.com
    *** I no longer assist with top-posted newsgroup queries ***


  4. Re: Oracle - Column Order


    amerar@iwc.net wrote:
    > Hi,
    >
    > Is there a way to change the order of the columns in a table? I know
    > that you can change the name via using the COL$ dictionary table. Is
    > there also a sneaky way like that to change the order of the columns?
    >
    > Thanks,
    >
    > Arthur


    Most certainly there is, and it's just as idiotic as updating the data
    dictionary directly, bypassing the provided mechanisms. A question to
    you: why do you feel compellled to alter the column order in a table?
    Is it for 'convenience' so a 'select *' puts data into YOUR desired
    order without effort? If so I suggest you cease your lazy attitude and
    actually write usable queries absent the 'select *' shorthand as such
    has absolutely NO place in production code.

    Updating COL$ is foolishness run amok. Stop doing such things, lest
    you send your database into oblivion with no hope of resurrection as
    Oracle Support will disown you.


    David Fitzjarrell


  5. Re: Oracle - Column Order

    amerar@iwc.net wrote:
    > Hi,
    >
    > Is there a way to change the order of the columns in a table? I know
    > that you can change the name via using the COL$ dictionary table. Is
    > there also a sneaky way like that to change the order of the columns?
    >
    > Thanks,
    >
    > Arthur


    Why? What possible difference could it make?
    --
    Daniel A. Morgan
    http://www.psoug.org
    damorgan@x.washington.edu
    (replace x with u to respond)

  6. Re: Oracle - Column Order

    DA Morgan wrote:
    >
    > Why? What possible difference could it make?


    Move columns that are usually nulls to the end of the table. Having
    null column trailing, reduces row space footprint (a teensy tad - but
    can make a difference with VLTs).

    But then this not something that the OP has likely in mind?
    Conceptually - agree whole heartedly: the order of columns in a table
    has *NO* significance to application development, developers and
    end-users.

    --
    Billy


  7. Re: Oracle - Column Order

    On 18 Oct 2005 03:28:02 -0700, "Billy" wrote:

    >But then this not something that the OP has likely in mind?
    >Conceptually - agree whole heartedly: the order of columns in a table
    >has *NO* significance to application development, developers and
    >end-users.


    Too bad the column order actually DOES matter, as it takes time to
    traverse a record: the exact position of a column in a record can only
    be retrieved by traversing all previous columns.

    --
    Sybrand Bakker, Senior Oracle DBA

  8. Re: Oracle - Column Order

    Sybrand Bakker wrote:

    > >But then this not something that the OP has likely in mind?
    > >Conceptually - agree whole heartedly: the order of columns in a table
    > >has *NO* significance to application development, developers and
    > >end-users.

    >
    > Too bad the column order actually DOES matter, as it takes time to
    > traverse a record: the exact position of a column in a record can only
    > be retrieved by traversing all previous columns.


    Not talking about physically Sybrand, but conceptually. Applications
    should not be coded in such a way to expect column 1 to always contain
    CUST_ID, column 2 containing FOO_1, etc.

    Or expect the PK index to always be named PE_. Or views to
    be qualified with a V_. Or block sizes of always 8KB. Or
    indexes to be in a separate tablespaces. Etc.

    Column order does -not- matter from an application viewpoint. Physical
    characteristics of the database has no bearing at all to the
    application. If it does, the application is flawed and the smallest
    physical database change will break the application.

    The only people that should be concerned with the physical order of
    columns and stuff at that level, are people like you and me. DBAs. Not
    developers. Not end-users. And the only recommendation I've ever run
    across from Oracle in Oracle documentation ito column order is to put
    the most frequently null column at the end of a row so that the column
    label does not need to be stored with the row on the data block. If you
    know of any more in this regard, I will appreciate the Oracle
    documentation references.

    --
    Billy


+ Reply to Thread