主页 / dba / 问题 / 254793
Accepted
Lennart - Slava Ukraini
Asked: 2019-12-05 06:47:18 +0800 CST

VARCHAR / VARGRAPHIC 列的分布统计信息？

  • 772

似乎只有前 n 个字符用作 varchar/vargraphic 列的 colvalue。从我下面的测试来看，它似乎是 33/16。长度是否记录在某处？我试图搜索文档，但找不到任何东西（可能是搜索错误的东西）

简单的测试用例：

DROP TABLE LEJO0004.T;
CREATE TABLE LEJO0004.T (
        ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY 
            ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 NO CYCLE CACHE 20 NO ORDER ),
        TCOL_C VARCHAR(64) NOT NULL,
        TCOL_G VARGRAPHIC(64) NOT NULL
);

insert into LEJO0004.T (tcol_c, tcol_g)
with n100 (n) as ( values 0 union all select n+1 from n100 where n<100 )
   , n10  (n) as ( values 0 union all select n+1 from n10 where n<10 )
   , n1   (n) as ( values 0 )
select 'commons.domain.regel.avbrottsregel'
     , 'commons.domain.regel.avbrottsregel'
from n100
union all
select 'commons.domain.regel.grupp.overfors.nya.endast.som.merit'
     , 'commons.domain.regel.grupp.overfors.nya.endast.som.merit'
from n10
union all
select 'commons.domain.regel.ingar.i.grupp.overfors.till.nya'
    ,  'commons.domain.regel.ingar.i.grupp.overfors.till.nya'
from n1;

runstats on table lejo0004.T with distribution on all columns;

select cast(colname as varchar(15))
    ,  type
    ,  seqno
    ,  cast(colvalue as varchar(40))
    ,  valcount
from sysstat.COLDIST
where tabschema = 'LEJO0004'
  and tabname = 'T'
  and colname in ('TCOL_C', 'TCOL_G')
  and colvalue is not null
  and type = 'F'
order by type, colname, seqno;

结果是：

TCOL_C  F 1 'commons.domain.regel.avbrottsrege' 101
TCOL_C  F 2 'commons.domain.regel.grupp.overfo'  11
TCOL_G  F 1 g'commons.domain.r'                 113

Q似乎是一样的

values char_length(g'commons.domain.r' using codeunits32)
16

values char_length('commons.domain.regel.avbrottsrege' using codeunits32)
33

测试：

db2level
DB21085I  This instance or install (instance name, where applicable: 
"lejo0004") uses "64" bits and DB2 code release "SQL11050" with level 
identifier "0601010F".
Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64", 
and Fix Pack "0".
Product is installed at "/home/lejo0004/sqllib".
db2 db2-luw

1 个回答

  1. Best Answer

    它是“部分记录”/“没有真正记录”的 AFAIK。更具体地说，有 APAR：

    IT13369：在公共前缀大于 32 字节的字符串列上收集分布统计信息时的次优查询性能

    提到它。我假设您真正关心的是查询的基数估计，该查询在具有公共前缀的列上具有谓词，在这种情况下，您可以尝试使用它的解决方法（即不收集该列的分布统计信息）

    • 3

相关问题