This discussion is archived
13 Replies Latest reply: May 23, 2010 11:22 PM by santi RSS

Importance of column_position

santi Newbie
Currently Being Moderated
Hi,

Please help me to understand the importance of column_position in a table for Cost Based Optimizer.

Regards,
  • 1. Re: Importance of column_position
    sb92075 Guru
    Currently Being Moderated
    Please help me to understand the importance of column_position in a table for Cost Based Optimizer.
    100% NOT important
  • 2. Re: Importance of column_position
    PrafullaNath Pro
    Currently Being Moderated
    No importance you can have any order
  • 3. Re: Importance of column_position
    orawiss Oracle ACE
    Currently Being Moderated
    Depends!
    DO you mean the order in your select or in your where condition?
  • 4. Re: Importance of column_position
    EdStevens Guru
    Currently Being Moderated
    orawiss wrote:
    Depends!
    DO you mean the order in your select or in your where condition?
    or (3) order in the table definition.

    I think all previous responses assumed the OP meant (3).
  • 5. Re: Importance of column_position
    ajallen Pro
    Currently Being Moderated
    Does not matter here, either. Used to with RBO, but not w/CBO.
  • 6. Re: Importance of column_position
    CharlesHooper Expert
    Currently Being Moderated
    santi wrote:
    Hi,

    Please help me to understand the importance of column_position in a table for Cost Based Optimizer.

    Regards,
    If you are asking about the order of the columns in a table, the order could make a difference. Page 527 of the book "Troubleshooting Oracle Performance" includes a brief description why the position of a column may make a difference, as well as a chart showing the performance impact.
    http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA527

    I thought I remembered seeing a longer description in that book also (mentioned again on page 537 when discussing tables containing more than 255 columns).

    ------
    Edit:
    The second half - the column position affects the optimizer's cost calculation for retrieving the data from the table.
    Page 117 of "Troubleshooting Oracle Performance":
    http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA117

    http://tonguc.wordpress.com/2007/09/17/column-retrieval-cost-in-cbo-calculation/
    ------

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

    Edited by: Charles Hooper on May 19, 2010 1:36 PM
    Added the second half of the column position information.
  • 7. Re: Importance of column_position
    548860 Newbie
    Currently Being Moderated
    The decisions made by the optimizer are not influenced by the column positions in the table definition per se.

    However, the ordering of columns have an impact on the physical storage, since for example, null markers are not stored for trailing null columns in a row. That could mean a smaller or bigger table (blocks/bytes) which I guess could influence the optimizer.

    / Ronnie
  • 8. Re: Importance of column_position
    orawiss Oracle ACE
    Currently Being Moderated
    I agree with Charles. Makes sens!
  • 9. Re: Importance of column_position
    santi Newbie
    Currently Being Moderated
    Thanks Charles. The explanation cleared a lot. Now I wanted to understand what is it's impact when selecting an index:
    Query No.1 (BLINE PROCESS)
    --------------------------
    
    SELECT X,Y,Z
    FROM      f.N_T_ADVICE_DTL
    WHERE      A BETWEEN :1 AND :2
    AND      B BETWEEN :3 AND :4
    AND      C='A'
    ORDER BY A, B,  D;
    
    Query No.2
    ----------
    
    SELECT SUM(T)
    FROM     f.n_t_advice_dtl
    WHERE     A = :b1
    AND     E =93
    AND     C ='A';
    
    Index Details
    -------------
    
    INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
    ------------------------------ ------------------------------ ---------------
    BLINE_INDX                     A                                       1 <== Local
                                   B                                   2
                                   C                                       3
    
    N_T_ADVICE_DTL_IDX3            A                                       1 <== Local
                                   E                                     2
    Regards,
  • 10. Re: Importance of column_position
    sb92075 Guru
    Currently Being Moderated
    please DDL for all tables & indexes being discussed.
  • 11. Re: Importance of column_position
    515958 Pro
    Currently Being Moderated
    Column positions influence optimizer when it has to pick indexes. Richard Foote has described it:

    http://richardfoote.wordpress.com/2008/03/10/index-skip-scan-does-index-column-order-matter-any-more-warning-sign/

    Regards,
    S.K.
  • 12. Re: Importance of column_position
    CharlesHooper Expert
    Currently Being Moderated
    santi wrote:
    Thanks Charles. The explanation cleared a lot. Now I wanted to understand what is it's impact when selecting an index:
    I think that I misunderstood your question, and I could not quite understand why Santosh Kumar provided a link to one of Richard Foote's articles about index skip scans. I guess that if I read your post completely, including the code section, I would have understood that you were asking about whether or not the order of columns in an index definition mattered, and not about whether using either an index range scan or full table scan resulted in different performance results. Definitely, read Richard Foote's articles about performance characteristics of indexes.

    I put together a little test case, which probably required about 8 to 12 hours to assemble and execute, to see if the column order in a table really matters. The test was performed under both 64 bit Linux and 64 bit Windows, and used index range scans as well as full table scans in Oracle Database 11.2.0.1:
    http://hoopercharles.wordpress.com/2010/05/22/column-order-in-a-table-does-it-matter-1/

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 13. Re: Importance of column_position
    santi Newbie
    Currently Being Moderated
    Thanks Charles,

    I may be unclear in my question, but the explanation which I got from you was really wonderful and clear. Again the thread of 'Richard Foote' was also helpful.

    Actually I have seen SQL tuners always check the column_position during their all diagnostic processes (especially when they check why a particular composite index is not picked). I could not understand why do they always check for column_position. That is why I asked the question in the forum.

    Thanks to all.

    Regards,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points