本文共 4041 字，大约阅读时间需要 13 分钟。
[20171203]平均长度和虚拟列.txt
--//昨天看链接https://blog.dbi-services.com/doag-2017-avg_row_len-with-virtual-columns/
--//重复测试看看.1.环境:
SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID --------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 02.测试:
create table t ( GUID0 RAW(16) ,GUID1 RAW(16) ,GUID2 RAW(16) ,GUID0_CHAR as (SUBSTR(RAWTOHEX(GUID0),1,8)||'-'|| SUBSTR(RAWTOHEX(GUID0),9,4)||'-'|| SUBSTR(RAWTOHEX(GUID0),13,4)||'-'|| SUBSTR(RAWTOHEX(GUID0),17,4)||'-'|| SUBSTR(RAWTOHEX(GUID0),21,12)) ,GUID1_CHAR as (SUBSTR(RAWTOHEX(GUID1),1,8)||'-'|| SUBSTR(RAWTOHEX(GUID1),9,4)||'-'|| SUBSTR(RAWTOHEX(GUID1),13,4)||'-'|| SUBSTR(RAWTOHEX(GUID1),17,4)||'-'|| SUBSTR(RAWTOHEX(GUID1),21,12)) ,GUID2_CHAR as (SUBSTR(RAWTOHEX(GUID2),1,8)||'-'|| SUBSTR(RAWTOHEX(GUID2),9,4)||'-'|| SUBSTR(RAWTOHEX(GUID2),13,4)||'-'|| SUBSTR(RAWTOHEX(GUID2),17,4)||'-'|| SUBSTR(RAWTOHEX(GUID2),21,12)) ); insert into t (guid0,guid1,guid2) select sys_guid(), sys_guid(),sys_guid() from xmltable('1 to 10000'); commitexecute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 't',
Estimate_Percent => NULL,Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);SCOTT@test01p> select avg_row_len from tabs where table_name='T';
AVG_ROW_LEN ----------- 162SCOTT@test01p> select sum(avg_col_len) from user_tab_columns where table_name='T' and column_name in ('GUID0','GUID1','GUID2');
SUM(AVG_COL_LEN) ---------------- 51--//很明显,实际占用长度51(说明oracle考虑前面1个字节的长度指示器),而查询分析报平均总长度162.明显包括了虚拟列.
--//作者的解析oracle这样设计有它的道理,比如select * ,hash join连接的计算等.explain plan for
select a.*, b.guid0 b_guid0 from t a, t b where a.guid0_char=b.guid0_char;SCOTT@test01p> @dp
PLAN_TABLE_OUTPUT --------------------------- Plan hash value: 2135975663 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 2109K| 46 (0)| 00:00:01 | |* 1 | HASH JOIN | | 10000 | 2109K| 46 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| T | 10000 | 527K| 23 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T | 10000 | 1582K| 23 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / B@SEL$1 3 - SEL$1 / A@SEL$1Outline Data
-------------/*+
BEGIN_OUTLINE_DATA SWAP_JOIN_INPUTS(@"SEL$1" "B"@"SEL$1") USE_HASH(@"SEL$1" "B"@"SEL$1") LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1") FULL(@"SEL$1" "B"@"SEL$1") FULL(@"SEL$1" "A"@"SEL$1") OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('12.1.0.1') OPTIMIZER_FEATURES_ENABLE('12.1.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */Predicate Information (identified by operation id):
---------------------------------------------------1 - access("A"."GUID0_CHAR"="B"."GUID0_CHAR")
Column Projection Information (identified by operation id):
-----------------------------------------------------------1 - (#keys=1) "A"."GUID0_CHAR"[VARCHAR2,68], "GUID0"[RAW,16],
"GUID0"[RAW,16], "GUID1"[RAW,16], "GUID2"[RAW,16] 2 - "GUID0"[RAW,16] 3 - "GUID0"[RAW,16], "GUID1"[RAW,16], "GUID2"[RAW,16]48 rows selected.
--//select a.*, b.guid0 b_guid0 from t a, t b where a.guid0_char=b.guid0_char;
--//别名表b在前面,
--//(17+37)*10000/1024 = 527.34375K. --//162*10000/1024=1582.03125K.2.如果使用传统的analyze分析看看.
SCOTT@test01p> analyze table t compute statistics; Table analyzed.SCOTT@test01p> select avg_row_len from tabs where table_name='T'; AVG_ROW_LEN ----------- 54
SCOTT@test01p> select sum(avg_col_len) from user_tab_columns where table_name='T' and column_name in ('GUID0','GUID1','GUID2');
SUM(AVG_COL_LEN) ---------------- 48--//传统analyze的分析不包括前面的长度指示器.而平均长度计算仅仅包括前面的fb,cc长度(占3个字节),以及前面长度指示器.
--//这样3+17*3 = 54字节.转载地址：http://yeeql.baihongyu.com/