| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: add a new column into the middle of an existing table
There is a performance advantage, up to several percent (15% in our case doing a sum of a table with 22 million rows using 1 st column vs doing 30th column) to have regularlly accessed columns at the start of the table (to access a column oracle has to read metacolumn about the columns preceding it, including length, on a wide table with the column toward the end, this can have an effect espically if the table is accessed alot).
this is from the Ixora newsletter
A call to order
The order of columns in a table can have an impact on performance. There are two factors to consider.
Firstly, Oracle rows are stored as a row header followed by column data. The row header contains a flag byte, lock byte and column count, then for each column there is a column length followed by the column data. To access the value of any column in a row, Oracle has to first examine the length bytes of all the preceding columns. This is a very quick and efficient operation, but it is done with such frequency that it nevertheless does have an impact on performance.
In the following example we will create a 10-column table and insert enough rows to fill a single 2K database block. We will then compare the response time of repeatedly accessing the first column and the last column.
SQL> create table small (
2 n0 number, 3 n1 number, 4 n2 number, 5 n3 number, 6 n4 number, 7 n5 number, 8 n6 number, 9 n7 number, 10 n8 number,
Table created.
SQL> begin
2 for i in 1..78 loop
3 insert into small values (0,0,0,0,0,0,0,0,0,0);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> set timing on
SQL> declare
2 n number;
3 begin
4 for i in 1..1000000 loop
5 select sum(n0) into n from small;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:07:437.30
SQL> declare
2 n number;
3 begin
4 for i in 1..1000000 loop
5 select sum(n9) into n from small;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:08:482.13
This demonstrates that it took more than 10% longer to access the 10th column in this table than it did to access the 1st column. The principle is simple. Place frequently accessed columns early in the table column order. Remember that column values will normally be accessed more frequently for WHERE clause predicate evaluation, than for select-list expression evaluation. However, column values that appear in important driving predicates may be accessed relatively infrequently if the table is consistently accessed by rowid via an index on that column. In particular, primary key columns are seldom the most intensively accessed table columns, and should not normally be first in the table column order.
The second aspect of table column order that impacts performance is the position of columns that frequently contain NULLs. Oracle normally requires one byte to represent each NULL, except that it does not store trailing NULLs in a data row. This may be demonstrated as follows.
SQL> create table null_order (
2 column1 number,
3 column2 number,
4 column3 number
5 );
Table created.
SQL> insert into null_order (column2) values (0);
1 row created.
SQL> select header_file, header_block from dba_segments 2 where segment_name = 'NULL_ORDER' and owner = user;
HEADER_FILE HEADER_BLOCK
----------- ------------
3 50010
SQL> alter system dump datafile 3 block 50011;
System altered.
The first and third columns in the one row of this table are NULL. The following extract from the block dump shows how they are represented.
block_row_dump:
tab 0, row 0, @0x7b2
tl: 6 fb: --H-FL-- lb: 0x1 cc: 2
col 0: *NULL*
col 1: [ 1] 80
end_of_block_dump
The NULL in the first column is stored explicitly and increases the row length by one byte. But the NULL in the third column does not need to be stored explicitly because there are no subsequent non-NULL values in the row. When reading a table row, Oracle is able to infer that any trailing columns that are not represented explicitly just contain NULLs.
Placing columns that frequently contain NULLs last in the table column order, minimizes the average row length and optimizes the table data density, which of course benefits performance. This column ordering also minimizes the number of column length bytes that need to be navigated to access the non-NULL column values. This also benefits performance as discussed above.
In article <3CD18B56.E9213695_at_exesolutions.com>, Daniel Morgan
<dmorgan_at_exesolutions.com> wrote:
>M Buckle wrote:
>
>> If your relation (table) is just a bag of unrelated columns, fine, but
>> apart from efficiency condiderations, similar kinds of things should
>> group together if only for the sake of the poor s*d doing maintenance
>> work who is trying to understand the structure and meaning of some
>> under-documented system. If foreign keys are to be found together
>> near the head of the table, date and timestamps together at the
>> bottom, it makes things simpler in the long-run, or perhaps you would
>> disagree with such notions along with the use of column naming
>> conventions, use of consistent data types etc etc.
>>
>> Sorry if that sounded like a rant, but your reply sounded like a
>> smart-a*se put-down.
>>
>> Cheers, M
>>
>> jp_boileau_at_yahoo.com (J.P.) wrote in message
> news:<7e388bc3.0205010457.701efd87_at_posting.google.com>...
>> > You gott'a stop thinking in non Relational-DB style...
>> >
>> > The physical location of a column is totally irrelevant in a RDBMS.
>> >
>> > JP
>> >
>> > leiwang_q_at_hotmail.com (lei wang) wrote in message
> news:<af074b9c.0204301247.65da02f9_at_posting.google.com>...
>> > > Is there an easy way to add a new column into the middle of an
>> > > existing table? Seems to me that you have to create a new table and
>> > > copies data from the old table. <Alter table add column> statement
>> > > will only adds columns at the end of an existing table.
>> > >
>> > > I am using Oracle 8.1.7 EE on Solaris 2.7.
>> > >
>> > > Thanks.
>
>Anyone that thinks about reordering columns in a production database should
> consider the implications on
>everything from SQL*Loader scripts to insert statements. And the fact that they
> have, by virtue of this activity,
>cost any serious company a substantial sum of money in testing for a near
> gratuitous change.
>
>I will not believe that anyone has ever encountered a database with performance
> problems where the solution was
>reordering columns. If there is a performance improvement, something of which
> my own cursory testing has yet to
>demonstrate, it must be insignificant.
>
>Daniel Morgan
>
Received on Fri May 03 2002 - 15:07:12 CDT
 |
 |