IDS Forum

Re: Don't Understand Table's Space Requirement

Posted By: JACQUES RENAUT
Date: Friday, 25 May 2018, at 4:13 p.m.

In Response To: Don't Understand Table's Space Requirement (DAVID GROVE)

Original post:

IDS 12.10.FC3
Solaris 10 1/13

Consider a table with 11 columns. 10 of the columns are various "usual" datatypes, and those 10 columns take 39 bytes. The eleventh column is type LVARCHAR(10000).

The table contains 300,000 rows. In 99.9% of the rows (299,700), the LVARCHAR column has LENGTH < 1,950, meaning that the entire row should fit on a single page (using default page size = 2K, and have MAX_FILL_DATA_PAGES = 1) the overwhelming majority of the time.

Only 300 rows have the LVARCHAR column greater than 1,950, and only 3 rows exceed LENGTH 5,000.

The SUM( LENGTH( <the_lvarchar_col> ) ) for the whole table is 30 MB (.03GB), and the remaining columns (39 bytes) should take up roughly .01MB.

Yet, as shown in Server Studio, the table requires >1.5GB (approx 1,500MB).

This seems like basic DBA material, but I don't understand why the table is taking up 50 times more space than it seems like it should.

Might anyone offer an explanation?

Thank you.

DG

Response:

It sounds like a bug. I did a quick test on 12.10.FC11 using using a 2 column table, a char(39) and a lvarchar(10000) and I inserted like 15 rows, and according to oncheck -pT all my rows are still on a single page (as expected, I used small lvarchars). For your table to be 1.5Gb it almost seems like server studio is taking max row size (so like a bit over 10k) and just multiplying that by the number of rows. I don't know how server studio is getting it's size...but what does oncheck -pt say for the number of pages allocate/used/number of data pages? As I don't know if it is a server defect or something odd that server studio is doing.

Jacques Renaut
HCL Informix Advanced Support

Messages In This Thread

• Don't Understand Table's Space Requirement
  DAVID GROVE -- Friday, 25 May 2018, at 1:37 p.m.
  • Re: Don't Understand Table's Space Requirement
    DAVID GROVE -- Friday, 25 May 2018, at 3:16 p.m.
  • Re: Don't Understand Table's Space Requirement
    JACQUES RENAUT -- Friday, 25 May 2018, at 4:13 p.m.
    • Re: Don't Understand Table's Space Requirement
      DAVID GROVE -- Friday, 25 May 2018, at 6:51 p.m.
      • Re: Don't Understand Table's Space Requirement
        JACQUES RENAUT -- Friday, 25 May 2018, at 7:33 p.m.
        • Re: Don't Understand Table's Space Requirement
          DAVID GROVE -- Tuesday, 29 May 2018, at 1:18 p.m.
          • Re: Don't Understand Table's Space Requirement
            JACQUES RENAUT -- Tuesday, 29 May 2018, at 3:19 p.m.
            • Re: Don't Understand Table's Space Requirement
              DAVID GROVE -- Tuesday, 29 May 2018, at 3:44 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.