[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 0
2.测试:
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');
commit
execute 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
-----------
162
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)
----------------
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$1
Outline 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字节.