How avg_row_len in dba_tables is calculated
Today, I will talk to you about how the avg_row_len in dba_tables is calculated. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.
Avg_row_len is calculated when collecting information, and the difference between using analyze and dbms_stats to calculate avg_row_len is that the latter does not take into account the three byte occupied by row header
Doc is as follows:
Http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref720
C: > sqlplus xys/manager
SQL*Plus: Release 10.2.0.1.0-Production on Monday October 8 22:10:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connect to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL > desc tab1
Is the name empty? Types
-
ID NUMBER (38)
NAME VARCHAR2 (10)
TIME DATE
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
sixteen
SQL > SELECT * FROM TAB1
ID NAME TIME
1 a 08-October-07
SQL > insert into tab1 values (2Zero, sysdate)
1 line has been created.
SQL > commit
The submission is complete.
SQL > analyze table tab1 compute statistics
The table has been analyzed.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
fifteen
SQL > select * from tab1
ID NAME TIME
1 a 08-October-07
2 08-10-07
-- the 3 here is the byte,1 occupied by row header, which stands for The column length requires 1 byte for columns that store 250 bytes or less.
SQL > select 3+vsize (id) + 1+vsize (name) + 1+vsize (time) + 1 from tab1
3+VSIZE (ID) + 1+VSIZE (NAME) + 1+VSIZE (TIME) + 1
-
sixteen
SQL > select 3+vsize (id) + 1+nvl (vsize (name), 0) + 1+vsize (time) + 1 from tab1
3+VSIZE (ID) + 1+NVL (VSIZE (NAME), 0) + 1+VSIZE (TIME) + 1
sixteen
fifteen
SQL > truncate table tab1
The watch is truncated.
SQL > insert into tab1 values (2Zero, sysdate)
1 line has been created.
SQL > commit
The submission is complete.
SQL > analyze table tab1 compute statistics
The table has been analyzed.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
fifteen
If it is null and not at the end, that is, not the last field, then only column len occupies one byte
SQL > select 3+vsize (id) + 1+nvl (vsize (name), 0) + 1+vsize (time) + 1 from tab1
3+VSIZE (ID) + 1+NVL (VSIZE (NAME), 0) + 1+VSIZE (TIME) + 1
fifteen
SQL > truncate table tab1
The watch is truncated.
SQL > insert into tab1 values (2)
1 line has been created.
SQL > commit
The submission is complete.
SQL > analyze table tab1 compute statistics
The table has been analyzed.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
eight
SQL > select * from tab1
ID NAME TIME
2 a
If the last field is null, then column len does not occupy bytes, which is the for trailing null columns Oracle does not even store the column length mentioned on doc.
A few days ago, I saw someone ask what is "trailing null columns". Master biti answered.
SQL > select 3+vsize (id) + 1+vsize (name) + 1 from tab1
3+VSIZE (ID) + 1+VSIZE (NAME) + 1
-
eight
SQL > alter table tab1 add col1 varchar2
The table has changed.
SQL > analyze table tab1 compute statistics
The table has been analyzed.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
eight
SQL > update tab1 set col1=rpad ('a', 249)
1 row has been updated.
SQL > commit
The submission is complete.
SQL > analyze table tab1 compute statistics
The table has been analyzed.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
two hundred and fifty nine
SQL > select id, name, time, substr (col1, 1,1) from tab1
ID NAME TIME SU
2 an a
SQL > select 3+vsize (id) + 1+vsize (name) + 1+nvl (vsize (time), 0) + 1+1+vsize (col1) from
Tab1
3+VSIZE (ID) + 1+VSIZE (NAME) + 1+NVL (VSIZE (TIME), 0) + 1+1+VSIZE (COL1)
two hundred and fifty nine
SQL > update tab1 set col1=rpad ('a', 250)
1 row has been updated.
SQL > commit
The submission is complete.
SQL > analyze table tab1 compute statistics
The table has been analyzed.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
two hundred and sixty
SQL > select 3+vsize (id) + 1+vsize (name) + 1+nvl (vsize (time), 0) + 1+1+vsize (col1) from
Tab1
3+VSIZE (ID) + 1+VSIZE (NAME) + 1+NVL (VSIZE (TIME), 0) + 1+1+VSIZE (COL1)
two hundred and sixty
-- The column length requires 1 byte for columns that store 250 bytes or less, or 3 bytes for columns that store more than 250 bytes
When the value on the column exceeds 250, column len requires 3 byte
SQL > update tab1 set col1=rpad ('a', 251)
1 row has been updated.
SQL > commit
The submission is complete.
SQL > analyze table tab1 compute statistics
The table has been analyzed.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
two hundred and sixty three
SQL > select 3+vsize (id) + 1+vsize (name) + 1+nvl (vsize (time), 0) + 1+1+vsize (col1) from
Tab1
3+VSIZE (ID) + 1+VSIZE (NAME) + 1+NVL (VSIZE (TIME), 0) + 1+1+VSIZE (COL1)
two hundred and sixty one
SQL > select 3+vsize (id) + 1+vsize (name) + 1+nvl (vsize (time), 0) + 1+3+vsize (col1) from
Tab1
3+VSIZE (ID) + 1+VSIZE (NAME) + 1+NVL (VSIZE (TIME), 0) + 1+3+VSIZE (COL1)
two hundred and sixty three
-- =
Using dbms_stats to collect statistics is different. Row header is not calculated when calculating avg_row_len.
SQL > truncate table tab1
The watch is truncated.
SQL > desc tab1
Is the name empty? Types
-
ID NUMBER (38)
NAME VARCHAR2 (10)
TIME DATE
COL1 VARCHAR2 (300)
SQL > insert into tab1 (id, name) values (1,'a')
1 line has been created.
SQL > commit
The submission is complete.
SQL > exec dbms_stats.gather_table_stats ('xys',' tab1')
The PL/SQL process completed successfully.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
six
SQL > select vsize (id) + 1+vsize (name) + 1 from tab1
VSIZE (ID) + 1+VSIZE (NAME) + 1
-
five
SQL > update tab1 set time=sysdate
1 row has been updated.
SQL > commit
The submission is complete.
SQL > exec dbms_stats.gather_table_stats ('xys',' tab1')
The PL/SQL process completed successfully.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
thirteen
SQL > select vsize (id) + 1+vsize (name) + 1+vsize (time) + 1 from tab1
VSIZE (ID) + 1+VSIZE (NAME) + 1+VSIZE (TIME) + 1
-
thirteen
SQL > update tab1 set col1=rpad
1 row has been updated.
SQL > commit
The submission is complete.
SQL > exec dbms_stats.gather_table_stats ('xys',' tab1')
The PL/SQL process completed successfully.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
two hundred and sixty three
SQL > select vsize (id) + 1+vsize (name) + 1+vsize (time) + 1+vsize (col1) + 1 from tab1
VSIZE (ID) + 1+VSIZE (NAME) + 1+VSIZE (TIME) + 1+VSIZE (COL1) + 1
two hundred and sixty three
SQL > update tab1 set col1=rpad
1 row has been updated.
SQL > commit
The submission is complete.
SQL > exec dbms_stats.gather_table_stats ('xys',' tab1')
The PL/SQL process completed successfully.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
two hundred and sixty four
SQL > select vsize (id) + 1+vsize (name) + 1+vsize (time) + 1+vsize (col1) + 1 from tab1
VSIZE (ID) + 1+VSIZE (NAME) + 1+VSIZE (TIME) + 1+VSIZE (COL1) + 1
two hundred and sixty four
SQL > update tab1 set col1=rpad ('axiaqingjin251)
1 row has been updated.
SQL > commit
The submission is complete.
SQL > exec dbms_stats.gather_table_stats ('xys',' tab1')
The PL/SQL process completed successfully.
SQL > select avg_row_len from user_tables where table_name='TAB1'
AVG_ROW_LEN
-
two hundred and sixty five
SQL > select vsize (id) + 1+vsize (name) + 1+vsize (time) + 1+vsize (col1) + 1 from tab1
VSIZE (ID) + 1+VSIZE (NAME) + 1+VSIZE (TIME) + 1+VSIZE (COL1) + 1
two hundred and sixty five
SQL >
After reading the above, do you have any further understanding of how the avg_row_len in dba_tables is calculated? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
