change order of table?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: change order of table?

  1. #1
    Join Date
    Nov 2000
    Posts
    169

    Unhappy

    Can the column arrangement in a table
    affect query performance?
    Below is the way my table is created:

    MY_ID VARCHAR2(18)
    MY_CODE VARCHAR2(12)
    EMP_NAME VARCHAR2(4)
    EMP_CODE VARCHAR2(2)
    EMP_ADD VARCHAR2(2)
    EMP_NO VARCHAR2(15)
    EMP_RES VARCHAR2(18)
    EMP_ST VARCHAR2(1)
    HIRE_DATE DATE
    CLASS_DATE DATE

    This is how the index is created:

    Create index my_emp_idx on my_emp (EMP_NO,EMP_CODE,MY_CODE)

    NOTE: My select and updates statements uses a
    where clause which is always
    in the order of the index craetion.

    e.g where EMP_NO = and EMP_CODE = and MY_CODE=


    QUESTION:
    Currently the index is being used correctly
    for all the sql's

    If I change the way the table is created
    using the indexed columns as the
    first columns during the table creation
    (like the example below) before the other
    columns, would this increase the speed of my queries?

    EMP_NO VARCHAR2(15)
    EMP_CODE VARCHAR2(2)
    MY_CODE VARCHAR2(12)
    EMP_RES VARCHAR2(18)
    HIRE_DATE DATE
    CLASS_DATE DATE
    EMP_NAME VARCHAR2(4)
    MY_ID VARCHAR2(18)
    EMP_ADD VARCHAR2(2)
    EMP_ST VARCHAR2(1)
    MY_ID VARCHAR2(18)

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The short answer is: No.

    Mind you, it does take a small amount of time to move across the columns of a given row, so there is some small benefit to moving shorter and/or more oft-used columns towards the beginning of the row. Here is a thread where we discuss some of these issues: http://www.dbasupport.com/forums/sho...threadid=29359 (a lengthy read, but good info).

    In your exact case, however, there will be no difference whatsoever. First, understand that column order in the WHERE clause does not matter at all (read: 99% of the time). Now, I am assuming that the optimizer is using the index in question. Therefore, the optimizer will use the index to get the ROWIDs for the rows that satisfy the specified predicates. It will then use the ROWIDs to get to the physical rows, *not* the 3 column values. Effectively, it will not be looking at those 3 columns *in the table* at all, so their physical location within the row is irrelevant (for this particular usage).

    Make sense?

    Note that there are many other things that could be done. As I said in the thread I linked above, switching to numeric IDs would be particularly beneficial here, but that's a topic for another day ;D

    - Chris

    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width