Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.