Table columns position and performance impact

You Asked

Hi Tom,

I have a question "Do columns position in a table have any impact on overall query performance?"
If we create table
-- create table_A (col1 varchar2(2000), col_date date, col_id number);
-- create table_b (col_id number, col_date date, col1 varchar2(2000));

Do you think query will perform better with table_B when it will have millions of records as compared to table_A?

Thanks,

Vipin

 

and we said...

It depends.

If you always access col1 and sometimes do not access col_date, col_id or just col_id - then TABLE_A would be your best bet.

 

If you always access col_id and or col_date - but only sometimes access col1 - then TABLE_B would be.

 

The difference in this case would be minuscule. It would come from the fact that in order to get the i'th column from a row - we have to parse over all of the preceding fields. Also, if the row was chained - and we needed the last column - we'd have to do an additional IO to retrieve it.

 

But in general, the difference is so tiny, so unmeasurable in real life that I would not worry about it. Put the columns in the order that makes the most sense.

For example - most people - pretty much all people - would put the primary key FIRST in the table. It is a universally accepted approach - convention.

But, we frequently query:

select first_name, last_name, address from table where primary_key = :x;

Here - we don't access the primary key in the table (we use an index to find rowids). We force the database however to parse over the primary_key column (because it is first) to get to the other attributes. Therefore - there would be a reason for putting the primary key LAST in your table.

 

On the other hand... If you put the primary key last - you'll make it so that you never have trailing null columns in any row (the last column will ALWAYS be not-null) and we won't be able to save a row without the trailing null columns (if the last N columns of a given row are NULL - we use zero bytes of storage for them - if the last column however is NOT NULL - we have to use storage for EVERY column in the table)

So - please don't go move your primary keys - the performance differential would be teeny tiny and the potential downside could be storage related.

I would put columns LAST in the table that would be mostly NULL - putting the column(s) that have the highest probability of being null last can make the rows take a little less space.

Try to put the most frequently accessed columns in the first 255 columns of the table create if you can and are using more than 255 columns (since every row is chained if you use more than 255 columns - with the additional columns above 255 (and every set of 255 columns) is stored on a different rowpiece somewhere else in the table).