IDS Forum

Re: usage space question

Posted By: Andreas Legner
Date: Wednesday, 22 February 2017, at 3:40 a.m.

In Response To: usage space question (CHUAN LU)

How sure are you your 11 'Number of pages used' figures really are for=20
this table's data fragments (rather than its index fragments)?

Can you repeat the math using 'Number of data pages' instead?

Andreas

From: "CHUAN LU" <luchuan@cn.ibm.com>
To: ids@iiug.org
Date: 22.02.2017 05:53
Subject: usage space question [38665]
Sent by: ids-bounces@iiug.org

1, I defined below table .=20

{ TABLE "cbs".aghmx row size =3D 1129 number of columns =3D 45 index size =
=3D=20
140 }=20

create table "cbs".aghmx=20
(=20

zhangh char(20) not null ,=20

jioyrq char(8) not null ,=20

zhujrq char(8),=20

jioysj integer=20

default 0,=20

jiaoym char(4) not null ,=20

pngzhh char(13),=20

jiedbz char(1) not null ,=20

jio1je decimal(13,2)=20

default 0,=20

zhhuye decimal(15,2)=20

default 0,=20

yueexz char(1) not null ,=20

yueefx char(1) not null ,=20

yngyjg char(4) not null ,=20

zhngjg char(4) not null ,=20

zhyyjg char(4) not null ,=20

zhkjjg char(4) not null ,=20

jio1gy char(8) not null ,=20

shoqgy char(8),=20

guiyls char(12) not null ,=20

yngyls char(12),=20

xnzhbz char(1) not null ,=20

zhyodm char(22),=20

cpznxh integer=20

default 0,=20

kehuzh char(20),=20

khzhlx char(1),=20

shunxh char(4),=20

chbubz char(1) not null ,=20

czzpbz char(1),=20

daynbz char(1),=20

xuhao1 integer=20

default 0,=20

shjnch integer=20

default 0,=20

jiluzt char(1)=20

default '0',=20

rzzhbz char(1),=20

dfhmnv char(200),=20

dfzhhv char(40),=20

dfgbdv char(3),=20

guifhv char(12),=20

dfhumv char(200),=20

dfzjhv char(40),=20

dfzzlv char(4),=20

dfbzxv char(128),=20

byxx01 char(60),=20

byxx02 char(60),=20

byxx03 char(60),=20

byxx04 char(60),=20

byxx05 char(60)=20
) with rowids=20
fragment by expression=20

(zhyyjg < '3109' ) in datacbs02,=20

((zhyyjg >=3D '3109' ) AND (zhyyjg < '7109' ) ) in datacbs03,=20

((zhyyjg >=3D '7109' ) AND (zhyyjg < '7702' ) ) in datacbs04,=20

((zhyyjg >=3D '7702' ) AND (zhyyjg < '8206' ) ) in datacbs05,=20

((zhyyjg >=3D '8206' ) AND (zhyyjg < '8908' ) ) in datacbs06,=20

((zhyyjg >=3D '8908' ) AND (zhyyjg < '9301' ) ) in datacbs07,=20

((zhyyjg >=3D '9301' ) AND (zhyyjg < '9413' ) ) in datacbs08,=20

((zhyyjg >=3D '9413' ) AND (zhyyjg < '9709' ) ) in datacbs09,=20

((zhyyjg >=3D '9709' ) AND (zhyyjg < '9821' ) ) in datacbs10,=20

((zhyyjg >=3D '9821' ) AND (zhyyjg < '9855' ) ) in datacbs11,=20

(zhyyjg >=3D '9855' ) in datacbs12=20
extent size 500000 next size 200000 lock mode row;=20

revoke all on "cbs".aghmx from "public" as "cbs";=20

create unique index "cbs".aghmx=5Fidx1 on "cbs".aghmx (jioyrq,guiyls,=20

cpznxh) using btree in idxdbs05;=20
create index "cbs".aghmx=5Fidx2 on "cbs".aghmx (khzhlx,kehuzh,shunxh,=20

jioyrq,jioysj) using btree in idxdbs07;=20
create index "cbs".aghmx=5Fidx3 on "cbs".aghmx (zhangh,jioyrq,jiluzt)=20

using btree in idxdbs09;=20
create index "cbs".aghmx=5Fidx4 on "cbs".aghmx (zhyyjg,jioyrq,daynbz,=20

jiluzt) using btree in idxdbs10;=20

2. row count information=20

> select count(*) from aghmx=20
> ;=20

(count(*))=20

917336051=20
3. oncheck -pt show the number of page used=20

Number of pages used 668151=20

Number of pages used 3403091=20

Number of pages used 2434915=20

Number of pages used 2799605=20

Number of pages used 1896666=20

Number of pages used 2574304=20

Number of pages used 1611425=20

Number of pages used 3786625=20

Number of pages used 2889476=20

Number of pages used 1698308=20

Number of pages used 1394682=20

totally 25157248 pages.=20
the page size is 8192, so it use 25157248*8192/1024/1024/1024=3D192GB ;=20
the rowsize is 1129 , row count is 917336051, and most column is char data =

type, the space is 917336051*1129/1024/1024/1024=3D964GB;=20

the char data type will pre allocate the space, why the real space is only =

192GB, I do not use varchar column.=20
thanks for your time=20

***************************************************************************=
****=20

Forum Note: Use "Reply" to post a response in the discussion forum.=20

Messages In This Thread

• usage space question
  CHUAN LU -- Tuesday, 21 February 2017, at 11:52 p.m.
  • Re: usage space question
    Andreas Legner -- Wednesday, 22 February 2017, at 3:40 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.