How to make a Manual Upgrade from version 10g to 11g (Step by Step Procedure) Depending on the case, pay sufficient attention to the order in which columns are defined in the physical design phase of the tables. To understand the situation that I suggest, first would be good to show them how Oracle stores the rows in the blocks.
A row is stored in a block as follows: First defines the header (H) that holds properties on the row itself, such as the number of columns you have and the flag that determines whether this locked. Then come the pairs format data (along the spine, contents of the column.) As each column can have different lengths, each of which consists of two parts: the length Lx and Dx data itself. Since the database engine does not know the offset of the columns in the row, you have to start from the first column, see the long, scroll to where the data length for the second column and so on until you find the column sought. Below, we show how the row is saved: As you may have noticed, if you need to find a column that is the end, Oracle will take much more than to find a column of principle, this overhead is not negligible and may affect performance, especially for applications with time requirements very low response, the order of milliseconds. That's why in some cases it is advisable to define the first column with the highest rate of referral and in the end that are less frequently consulted. To be able to observe the degree of impact of a suboptimal order of columns, I'll put together a simple example will better understand:
I will create a table T with 200 columns of type INT, and then I will insert 5000 rows:
I think the table T with the first column X1
create table t (x1 int); not write the ddl for the 200 columns I'll do it dynamically, adding the 199 remaining columns:
begin
for i in 2 .. 200
loop EXECUTE IMMEDIATE 'alter table t add x' rows for each column Xn:
l_cnt declare int, int l_foo
; l_stime int;
begin for i in 1 .. 200 loop
l_stime: = dbms_utility.get_time ();
for j in 1 1000 ..
loop EXECUTE IMMEDIATE 'select sum (x' pl above) to operate the column. As you can see the processing time is directly proportional to the location of the column in the row.
