Consider the order of columns when creating a table

Create
Consider the order of columns when creating a table
The order of the columns in the table when you create a table has some degree of performance impact. (The columns in the table have data)
Oracle's storage structure for row data row HEADER (wardrobe) and column (column data). The ROW header stores information that is a flag byte, a lock byte and column

COUNT. Column data contains column length and column data

We can do a test on this dump table.
Create test as SELECT * from Dba_objects;
Select Header_file,header_block from dba_segments where owner= ' test ' and segment_name= ' test ';

Header_file Header_block
------------------------   ---------------------------
13 1179
alter system dump DATAFILE 1180

The papers that come out are in the udump. We look at the following information
Block_row_dump:
tab 0, row 0, @0x1f20
tl:96 FB:--h-fl--lb:0x0 cc:13--------row header information.
Col 0: [3]-------------------COLUMN DATA
FB:--h-fl--is flag BYTE.
FB Flag Byte:
K = Cluster Key (Flags may change meaning if it is set to show HASH Cluster)
C = Cluster Table Member
H = head piece of row
D = Deleted Row
F = The data piece
L = Last data piece
P = continues from previous piece
N = Last column continues in next piece
lb:0x0-----------Lock BYTE, lock information
Cc:13------------COLUMN COUNT
Col 0---------First column
[3]-------------COLUMN LENGTH
---------Actual data

Here some information can refer to the article written by Grassbell << the physical structure of the data block >> For each column, the column length is included before each column data. When making a query, one of the

Column, Oracle first checks the length bits of these related columns. This operation is faster and more efficient. But doing so repeatedly can have a performance impact.
In the following example, a table with 10 columns is created and the data is inserted.
Set db_block_size=2k first (with parameter settings, set here for this only for testing convenience)
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,
N8 number,
One N9 number
(a) Pctfree 0;

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 the 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 the n from small;
6 end Loop;
7 End;
8/

Pl/sql procedure successfully completed.

elapsed:00:08:482.13

It is obvious from the example above that when querying in a table, the query's data and column properties are the same, but the query is faster in the first column than in the 10th.

10%. Therefore, the rules in the form of the table are based on the application of the table frequently visited in front of the column. When you build a table, you usually have a column of primary key, like this property of the column generally we visit directly

Not a lot of questions. So we don't usually put it in the first column. About this in fact, if we have noticed it, so is the table in Oracle's own dictionary.
Desc dba_objects take a look. or other tables to try.

Another place to consider is the position of the column where the column contains more null values.
When Oracle stores a null value, a column in a row has a null value, and there is data (not NULL) in the column that follows it, and Oracle allocates 1byte to hold null. If the back of this column

When there are no columns or null values. Neither this column nor the subsequent null value is stored by Oracle. Column information is not stored. This can be seen in the following example.

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 = ' Test ' and owner = ' Test ';

Header_file Header_block
----------- ------------
3 50010

sql> alter system dump DATAFILE 3 block 50011;

System altered.

Then look at the information about the dump file
Block_row_dump:
tab 0, row 0, @0x7b2
Tl:6 FB:--h-fl--lb:0x1 Cc:2
Col 0: *null*--------------NULL in the first column
Col 1: [1]----------------The value of the second column (the value of the third column is not followed)
End_of_block_dump

Conclusion: When you create a table, you can put the columns that we frequently access to the front of the table. The primary key column, which is rarely directly select, can be placed in the middle. Column if it might contain

Columns with more null values can be placed on the last side. You can finally consider the above two points, according to the application of the system to do the appropriate operation.