Creating a table Describe the order in which a list is created in a table, there is a certain impact on performance in some extent. (Column with data in the table) Oracle's storage structure Row Header and Column Data.row header stored information is a Flag Byte, a LOCK BYTE and Column
Count.column data contains Column Length and Column Data
About these we can do DUMP table to test 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 1179alter System Dump DataFile 13 Block 1180
The files you have come in UDump. We view the following information block_row_dump: Tab 0, Row 0, @ 0x1f20tl: 96 fb: --h-fl - lb: 0x0 cc: 13 -------- Row Header Information .COL 0: [3] 53 59 53 ------------------ Column Datafb: --h-fl - is Flag Byte. FB FLAG BYTE: K = CLUSTER Key (Flags may change meaning if this is set to show HASH cluster) C = Cluster table member H = Head piece of row D = Deleted row F = First data piece L = Last data piece P = First column continues from previous piece N = Last Column Continues in Next Piecelb: 0x0 ----------- Lock Byte, Lock Information CC: 13 ---------- COLUMN Count Col 0 -------- - First column [3] ------------- Column Length 53 59 53 -------- actual data
Here some information can be referred to the article written by GrassBell << Peaked Data Block's physical structure >>. For each column, there is a column length before each column data. When doing queries, a query line
The value of the column, Oracle first is to check the length of these related columns. This operation is relatively fast and efficient. But if you repeated frequently, this will also bring a performance impact. One of the following examples created one There are 10 columns of tables and insert data. First set DB_BLOCK_SIZE = 2K (set with parameter settings, set here to this is just for testing convenience) SQL> Create Table Small (2 N0 Number, 3 N1 Number, 4 N2 Number, 5 N3 Number , 6 N4 Number, 7 Number, 8 N6 Number, 9 N9 Number, 10 N8 Number, 11 N9 Number 12) 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); 4 end loop; 5 end; 6 / pl / SQL Procedure SuccessFully Completed.
SQL> SET TIMING ONSQL> 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.30SQL> 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
From the above example, it is clear that the data and columns of the query are the same when you do a query in a table, but the query speed is more than the 10th time when the column is in the first column.
10%. So the rule is in front of the table, the rule is based on the previously visited the list of the listed fields, and there is a column of Primary Key, and the column like this property is generally directly accessible.
There is not much asking. So we generally don't put it in the first column. About this actually, the inner table of the Oracle itself is also like this. Desc dba_objects. Or other table can be tried.
There is also another position of the column to consider, the location of the column is put in the column. When the NULL value is placed. Oracle stores a null value, there is a null value in a row, and the column of this column exists in the column With data (non-null), Oracle assigns 1Byte to store null. If this column is behind
Not column or is NULL value. This column and the Null value Oracle do not store. Columns are not stored. This can be seen in this example.
SQL> CREATE TABLE NULL_ORDER (2 Column1 Number, 3 Column2 Number, 4 Column3 Number 5);
Table created.
SQL> INSERT INTO NULL_ORDER (color) value (0);
1 row created.
SQL> SELECT Header_File, Header_Block from DBA_SEGMENTS 2 Where segment_name = 'test' and oowner = 'test';
Header_file header_block ----------- ------------ 3 50010
SQL> ALTER System Dump DataFile 3 block 50011;
SYSTEM altered.
Then view the information about DUMP's file block_row_dump: Tab 0, Row 0, @ 0x7b2tl: 6 fb: --h-fl - lb: 0x1 cc: 2col 0: * null * --------- ----- Nullcol 1: [1] 80 ----------------------------------------------------------------
Conclusion: When you create a table, you can discharge our regularly in front of the list. Generally, very few Primary Key columns that you only come out can be placed in the middle. If you may contain more NULL values in the column can be included Placed in the end. It can be considered for two points, and the corresponding operation is made according to the application of the system.
