[20171203]平均長度和虛拟列.txt

[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.

         54

              48

--//傳統analyze的分析不包括前面的長度訓示器.而平均長度計算僅僅包括前面的fb,cc長度(占3個位元組),以及前面長度訓示器.

--//這樣3+17*3 = 54位元組.