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