usage space question
1, I defined below table .
{ TABLE "cbs".aghmx row size = 1129 number of columns = 45 index size = 140 }
create table "cbs".aghmx
(
zhangh char(20) not null ,
jioyrq char(8) not null ,
zhujrq char(8),
jioysj integer
default 0,
jiaoym char(4) not null ,
pngzhh char(13),
jiedbz char(1) not null ,
jio1je decimal(13,2)
default 0,
zhhuye decimal(15,2)
default 0,
yueexz char(1) not null ,
yueefx char(1) not null ,
yngyjg char(4) not null ,
zhngjg char(4) not null ,
zhyyjg char(4) not null ,
zhkjjg char(4) not null ,
jio1gy char(8) not null ,
shoqgy char(8),
guiyls char(12) not null ,
yngyls char(12),
xnzhbz char(1) not null ,
zhyodm char(22),
cpznxh integer
default 0,
kehuzh char(20),
khzhlx char(1),
shunxh char(4),
chbubz char(1) not null ,
czzpbz char(1),
daynbz char(1),
xuhao1 integer
default 0,
shjnch integer
default 0,
jiluzt char(1)
default '0',
rzzhbz char(1),
dfhmnv char(200),
dfzhhv char(40),
dfgbdv char(3),
guifhv char(12),
dfhumv char(200),
dfzjhv char(40),
dfzzlv char(4),
dfbzxv char(128),
byxx01 char(60),
byxx02 char(60),
byxx03 char(60),
byxx04 char(60),
byxx05 char(60)
) with rowids
fragment by expression
(zhyyjg < '3109' ) in datacbs02,
((zhyyjg >= '3109' ) AND (zhyyjg < '7109' ) ) in datacbs03,
((zhyyjg >= '7109' ) AND (zhyyjg < '7702' ) ) in datacbs04,
((zhyyjg >= '7702' ) AND (zhyyjg < '8206' ) ) in datacbs05,
((zhyyjg >= '8206' ) AND (zhyyjg < '8908' ) ) in datacbs06,
((zhyyjg >= '8908' ) AND (zhyyjg < '9301' ) ) in datacbs07,
((zhyyjg >= '9301' ) AND (zhyyjg < '9413' ) ) in datacbs08,
((zhyyjg >= '9413' ) AND (zhyyjg < '9709' ) ) in datacbs09,
((zhyyjg >= '9709' ) AND (zhyyjg < '9821' ) ) in datacbs10,
((zhyyjg >= '9821' ) AND (zhyyjg < '9855' ) ) in datacbs11,
(zhyyjg >= '9855' ) in datacbs12
extent size 500000 next size 200000 lock mode row;
revoke all on "cbs".aghmx from "public" as "cbs";
create unique index "cbs".aghmx_idx1 on "cbs".aghmx (jioyrq,guiyls,
cpznxh) using btree in idxdbs05;
create index "cbs".aghmx_idx2 on "cbs".aghmx (khzhlx,kehuzh,shunxh,
jioyrq,jioysj) using btree in idxdbs07;
create index "cbs".aghmx_idx3 on "cbs".aghmx (zhangh,jioyrq,jiluzt)
using btree in idxdbs09;
create index "cbs".aghmx_idx4 on "cbs".aghmx (zhyyjg,jioyrq,daynbz,
jiluzt) using btree in idxdbs10;
2. row count information
> select count(*) from aghmx> ;
(count(*))
917336051
3. oncheck -pt show the number of page used
Number of pages used 668151
Number of pages used 3403091
Number of pages used 2434915
Number of pages used 2799605
Number of pages used 1896666
Number of pages used 2574304
Number of pages used 1611425
Number of pages used 3786625
Number of pages used 2889476
Number of pages used 1698308
Number of pages used 1394682
totally 25157248 pages.
the page size is 8192, so it use 25157248*8192/1024/1024/1024=192GB ;
the rowsize is 1129 , row count is 917336051, and most column is char data
type, the space is 917336051*1129/1024/1024/1024=964GB;
the char data type will pre allocate the space, why the real space is only
192GB, I do not use varchar column.
thanks for your time
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
Something isn't adding up. You are correct. On 8K pages and fixed length
rows of 1129 bytes your data should be taking up 131,048,008 pages plus
31,994 bitmap pages and maybe 9 extra partial pages (one for each
partition) for a total of 131,080,011 pages not 25,157,248 pages. Run an
oncheck -pT and post the entire report.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, Feb 21, 2017 at 11:52 PM, CHUAN LU <luchuan@cn.ibm.com> wrote:
> 1, I defined below table .
>
> { TABLE "cbs".aghmx row size = 1129 number of columns = 45 index size =
> 140 }
>
> create table "cbs".aghmx
> (
>
> zhangh char(20) not null ,
>
> jioyrq char(8) not null ,
>
> zhujrq char(8),
>
> jioysj integer
>
> default 0,
>
> jiaoym char(4) not null ,
>
> pngzhh char(13),
>
> jiedbz char(1) not null ,
>
> jio1je decimal(13,2)
>
> default 0,
>
> zhhuye decimal(15,2)
>
> default 0,
>
> yueexz char(1) not null ,
>
> yueefx char(1) not null ,
>
> yngyjg char(4) not null ,
>
> zhngjg char(4) not null ,
>
> zhyyjg char(4) not null ,
>
> zhkjjg char(4) not null ,
>
> jio1gy char(8) not null ,
>
> shoqgy char(8),
>
> guiyls char(12) not null ,
>
> yngyls char(12),
>
> xnzhbz char(1) not null ,
>
> zhyodm char(22),
>
> cpznxh integer
>
> default 0,
>
> kehuzh char(20),
>
> khzhlx char(1),
>
> shunxh char(4),
>
> chbubz char(1) not null ,
>
> czzpbz char(1),
>
> daynbz char(1),
>
> xuhao1 integer
>
> default 0,
>
> shjnch integer
>
> default 0,
>
> jiluzt char(1)
>
> default '0',
>
> rzzhbz char(1),
>
> dfhmnv char(200),
>
> dfzhhv char(40),
>
> dfgbdv char(3),
>
> guifhv char(12),
>
> dfhumv char(200),
>
> dfzjhv char(40),
>
> dfzzlv char(4),
>
> dfbzxv char(128),
>
> byxx01 char(60),
>
> byxx02 char(60),
>
> byxx03 char(60),
>
> byxx04 char(60),
>
> byxx05 char(60)
> ) with rowids
> fragment by expression
>
> (zhyyjg < '3109' ) in datacbs02,
>
> ((zhyyjg >= '3109' ) AND (zhyyjg < '7109' ) ) in datacbs03,
>
> ((zhyyjg >= '7109' ) AND (zhyyjg < '7702' ) ) in datacbs04,
>
> ((zhyyjg >= '7702' ) AND (zhyyjg < '8206' ) ) in datacbs05,
>
> ((zhyyjg >= '8206' ) AND (zhyyjg < '8908' ) ) in datacbs06,
>
> ((zhyyjg >= '8908' ) AND (zhyyjg < '9301' ) ) in datacbs07,
>
> ((zhyyjg >= '9301' ) AND (zhyyjg < '9413' ) ) in datacbs08,
>
> ((zhyyjg >= '9413' ) AND (zhyyjg < '9709' ) ) in datacbs09,
>
> ((zhyyjg >= '9709' ) AND (zhyyjg < '9821' ) ) in datacbs10,
>
> ((zhyyjg >= '9821' ) AND (zhyyjg < '9855' ) ) in datacbs11,
>
> (zhyyjg >= '9855' ) in datacbs12
> extent size 500000 next size 200000 lock mode row;
>
> revoke all on "cbs".aghmx from "public" as "cbs";>
> create unique index "cbs".aghmx_idx1 on "cbs".aghmx (jioyrq,guiyls,
>
> cpznxh) using btree in idxdbs05;
> create index "cbs".aghmx_idx2 on "cbs".aghmx (khzhlx,kehuzh,shunxh,
>
> jioyrq,jioysj) using btree in idxdbs07;
> create index "cbs".aghmx_idx3 on "cbs".aghmx (zhangh,jioyrq,jiluzt)
>
> using btree in idxdbs09;
> create index "cbs".aghmx_idx4 on "cbs".aghmx (zhyyjg,jioyrq,daynbz,
>
> jiluzt) using btree in idxdbs10;
>
> 2. row count information
>
> > select count(*) from aghmx> > ;
>
> (count(*))
>
> 917336051
> 3. oncheck -pt show the number of page used
>
> Number of pages used 668151
>
> Number of pages used 3403091
>
> Number of pages used 2434915
>
> Number of pages used 2799605
>
> Number of pages used 1896666
>
> Number of pages used 2574304
>
> Number of pages used 1611425
>
> Number of pages used 3786625
>
> Number of pages used 2889476
>
> Number of pages used 1698308
>
> Number of pages used 1394682
>
> totally 25157248 pages.
> the page size is 8192, so it use 25157248*8192/1024/1024/1024=192GB ;
> the rowsize is 1129 , row count is 917336051, and most column is char data
> type, the space is 917336051*1129/1024/1024/1024=964GB;
>
> the char data type will pre allocate the space, why the real space is only
> 192GB, I do not use varchar column.
> thanks for your time
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11442ee4924c0b05491cc402
My two guesses:
Have you done and alter table to add columns. If so you can have several
versions of the row. An oncheck -pT will show this.
Have you enabled compression on the table?
John F. Miller III
miller3@us.ibm.com
503-747-1366
ids-bounces@iiug.org wrote on 02/21/2017 08:52:49 PM:
> From: "CHUAN LU" <luchuan@cn.ibm.com>
> To: ids@iiug.org
> Date: 02/21/2017 08:53 PM
> Subject: usage space question [38665]
> Sent by: ids-bounces@iiug.org
>
> 1, I defined below table .
>
> { TABLE "cbs".aghmx row size =3D 1129 number of columns =3D 45 index size=
=3D
140 }
>
> create table "cbs".aghmx
> (
>
> zhangh char(20) not null ,
>
> jioyrq char(8) not null ,
>
> zhujrq char(8),
>
> jioysj integer
>
> default 0,
>
> jiaoym char(4) not null ,
>
> pngzhh char(13),
>
> jiedbz char(1) not null ,
>
> jio1je decimal(13,2)
>
> default 0,
>
> zhhuye decimal(15,2)
>
> default 0,
>
> yueexz char(1) not null ,
>
> yueefx char(1) not null ,
>
> yngyjg char(4) not null ,
>
> zhngjg char(4) not null ,
>
> zhyyjg char(4) not null ,
>
> zhkjjg char(4) not null ,
>
> jio1gy char(8) not null ,
>
> shoqgy char(8),
>
> guiyls char(12) not null ,
>
> yngyls char(12),
>
> xnzhbz char(1) not null ,
>
> zhyodm char(22),
>
> cpznxh integer
>
> default 0,
>
> kehuzh char(20),
>
> khzhlx char(1),
>
> shunxh char(4),
>
> chbubz char(1) not null ,
>
> czzpbz char(1),
>
> daynbz char(1),
>
> xuhao1 integer
>
> default 0,
>
> shjnch integer
>
> default 0,
>
> jiluzt char(1)
>
> default '0',
>
> rzzhbz char(1),
>
> dfhmnv char(200),
>
> dfzhhv char(40),
>
> dfgbdv char(3),
>
> guifhv char(12),
>
> dfhumv char(200),
>
> dfzjhv char(40),
>
> dfzzlv char(4),
>
> dfbzxv char(128),
>
> byxx01 char(60),
>
> byxx02 char(60),
>
> byxx03 char(60),
>
> byxx04 char(60),
>
> byxx05 char(60)
> ) with rowids
> fragment by expression
>
> (zhyyjg < '3109' ) in datacbs02,
>
> ((zhyyjg >=3D '3109' ) AND (zhyyjg < '7109' ) ) in datacbs03,
>
> ((zhyyjg >=3D '7109' ) AND (zhyyjg < '7702' ) ) in datacbs04,
>
> ((zhyyjg >=3D '7702' ) AND (zhyyjg < '8206' ) ) in datacbs05,
>
> ((zhyyjg >=3D '8206' ) AND (zhyyjg < '8908' ) ) in datacbs06,
>
> ((zhyyjg >=3D '8908' ) AND (zhyyjg < '9301' ) ) in datacbs07,
>
> ((zhyyjg >=3D '9301' ) AND (zhyyjg < '9413' ) ) in datacbs08,
>
> ((zhyyjg >=3D '9413' ) AND (zhyyjg < '9709' ) ) in datacbs09,
>
> ((zhyyjg >=3D '9709' ) AND (zhyyjg < '9821' ) ) in datacbs10,
>
> ((zhyyjg >=3D '9821' ) AND (zhyyjg < '9855' ) ) in datacbs11,
>
> (zhyyjg >=3D '9855' ) in datacbs12
> extent size 500000 next size 200000 lock mode row;
>
> revoke all on "cbs".aghmx from "public" as "cbs";>
> create unique index "cbs".aghmx=5Fidx1 on "cbs".aghmx (jioyrq,guiyls,
>
> cpznxh) using btree in idxdbs05;
> create index "cbs".aghmx=5Fidx2 on "cbs".aghmx (khzhlx,kehuzh,shunxh,
>
> jioyrq,jioysj) using btree in idxdbs07;
> create index "cbs".aghmx=5Fidx3 on "cbs".aghmx (zhangh,jioyrq,jiluzt)
>
> using btree in idxdbs09;
> create index "cbs".aghmx=5Fidx4 on "cbs".aghmx (zhyyjg,jioyrq,daynbz,
>
> jiluzt) using btree in idxdbs10;
>
> 2. row count information
>
> > select count(*) from aghmx> > ;
>
> (count(*))
>
> 917336051
> 3. oncheck -pt show the number of page used
>
> Number of pages used 668151
>
> Number of pages used 3403091
>
> Number of pages used 2434915
>
> Number of pages used 2799605
>
> Number of pages used 1896666
>
> Number of pages used 2574304
>
> Number of pages used 1611425
>
> Number of pages used 3786625
>
> Number of pages used 2889476
>
> Number of pages used 1698308
>
> Number of pages used 1394682
>
> totally 25157248 pages.
> the page size is 8192, so it use 25157248*8192/1024/1024/1024=3D192GB ;
> the rowsize is 1129 , row count is 917336051, and most column is char
data
> type, the space is 917336051*1129/1024/1024/1024=3D964GB;
>
> the char data type will pre allocate the space, why the real space is
only
> 192GB, I do not use varchar column.
> thanks for your time
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>