IDS Forum

Re: DELETE SQL & data pages

Posted By: Khaled Bentebal
Date: Wednesday, 7 October 2015, at 10:37 a.m.

In Response To: DELETE SQL & data pages (CHUAN LU)

This is a vague question, but if I understand it correctly, you would
like to know how many pages you would recuperate after a delete of x
rows in order to insert y new rows.

First of all this is complicated to calculate without having more
details since the table could have one or more indexes of different
sizes and also might contains varchars and may be blobs.

Also we need to know the size of the page (4K for AIX for example and 2K
for the HP, SOLARIS and LINUX).
A 2K page has 2020 bytes usable.
A 4K page has 4068 bytes usable.

*Let us assume that yout page is a 2 K page and assume that your table
does not not any varchars not special columns such as blobs. Let us also
assume that you do not have indexes. I am also assuming that your row
fits tottaly in the page and does notgo beyong 2K bytes.*
A page is 2048 bytes and has 2020 bytes usuable to store rows. A row
takes the rowsize + 4 bytes on the page.
So if your row is 130 bytes, you need to save 134 bytes for the row.
Your page will allow 15 rows to be stored in that page.

For the rest, you can do the math?

If you delete N rows and insert N+x rows, you will need the space for
the extra x rows since the system is able to reuse the space freed after
the delete operation. However, if you delete more rows than you will be
inserting, the space freed in the pages will still be part of the space
of that table aunles you you reorganize the table.

Cordialement, Regards,

Khaled Bentebal
Directeur Général - ConsultiX
Tél: 33 (0) 1 39 12 18 00
Fax: 33 (0) 1 39 12 18 18
Mobile: 33 (0) 6 07 78 41 97
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr

Le 07/10/15 15:56, CHUAN LU a écrit :
> A customer use IDS 10,he will delete some record for coming insert operation
> on the same table ,because of the disk utilization probme,customer hasn't
> enouth disk.
> He want to know how many data pages could be used for the coming insert
> operation after he run some delete SQL in the same table .
> thanks for your time.
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

Messages In This Thread

• DELETE SQL & data pages
  CHUAN LU -- Wednesday, 7 October 2015, at 9:55 a.m.
  • Re: DELETE SQL & data pages
    Ricardo Henriques -- Wednesday, 7 October 2015, at 10:25 a.m.
    • Re: DELETE SQL & data pages
      Ricardo Henriques -- Wednesday, 7 October 2015, at 10:28 a.m.
  • Re: DELETE SQL & data pages
    Art Kagel -- Wednesday, 7 October 2015, at 10:25 a.m.
  • Re: DELETE SQL & data pages
    Khaled Bentebal -- Wednesday, 7 October 2015, at 10:37 a.m.
  • RE: DELETE SQL & data pages
    Mike Walker -- Wednesday, 7 October 2015, at 11:13 a.m.
    • Re: DELETE SQL & data pages
      Madison Pruet -- Wednesday, 7 October 2015, at 11:46 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.