How to reuse pages in other tables? 
Author Message
 How to reuse pages in other tables?

Hi, All!
Please, help.

Created a table: CREATE TABLE ...
Checked: oncheck -pT database:table
    ...
    Number of extents              1
    First extent size              4
    Next extent size               4
    Number of pages allocated      8
    Number of pages used           7
    Number of data pages           0
    Number of rows                 0
    ...
1st question: Why number of pages allocated is 8 in 1 extent?

Loaded data:  LOAD FROM ...
Checked: oncheck -pT database:table
    ...
    Number of extents              2
    First extent size              4
    Next extent size               4
    Number of pages allocated      9336
    Number of pages used           9334
    Number of data pages           7955
    Number of rows                 103411
    ...
2nd question: Why number of pages allocated is 9336 in 2 extents?

Deleted data: DELETE FROM ...
Checked: oncheck -pT database:table
    ...
    Number of extents              2
    First extent size              4
    Next extent size               4
    Number of pages allocated      9336
    Number of pages used           9334
    Number of data pages           0
    Number of rows                 0
    ...
3rd question: Can I use pages allocated for this table ( but not used )
for other tables?




Wed, 18 Jun 1902 08:00:00 GMT
 How to reuse pages in other tables?

Quote:

> Created a table: CREATE TABLE ...
> Checked: oncheck -pT database:table
>     Number of extents              1
>     First extent size              4
>     Next extent size               4
>     Number of pages allocated      8
>     Number of pages used           7
>     Number of data pages           0
>     Number of rows                 0
> 1st question: Why number of pages allocated is 8 in 1 extent?

You set EXTENT SIZE and NEXT SIZE to 4 pages but Informix will compress
contiguous extents into a single extent containing all of the pages
from both.  That is what has happened here, when the second extent was
needed no other extents had yet been allocated in the chunk so the new
extent was contiguous with the old one and they were compressed into
one 8 page extent.

Quote:
> Loaded data:  LOAD FROM ...
> Checked: oncheck -pT database:table
>     Number of extents              2
>     First extent size              4
>     Next extent size               4
>     Number of pages allocated      9336
>     Number of pages used           9334
>     Number of data pages           7955
>     Number of rows                 103411
> 2nd question: Why number of pages allocated is 9336 in 2 extents?

Same here except that either:
1) At some point another table had allocated an extent from the same
chunk so that the next extent allocated to this table was not
contiguous with the first extent so that a second was created.
2) There was not enough room in the chunk containing the first extent
to allocate a full "NEXT SIZE" extent there and the extent was
allocated in another chunk (extents cannot cross chunk boundaries as
they must contain contiguous space).
3) Some space was freed up on the chunk (by dropping a table detached
index or database) that was physically before the current extent on the
chunk and that space was grabbed for the next extent.

Quote:
> Deleted data: DELETE FROM ...
> Checked: oncheck -pT database:table
>     Number of extents              2
>     First extent size              4
>     Next extent size               4
>     Number of pages allocated      9336
>     Number of pages used           9334
>     Number of data pages           0
>     Number of rows                 0
> 3rd question: Can I use pages allocated for this table ( but not used )
> for other tables?

Only by dropping the table and recreating it.  You can perform such
compression on a table with data by either

1) unload the data (using dbaccess/4Gl unload command, dbexport, or
onunload), drop the table, recreate the table, reload the data or
2) create a new table insert into new_table select * from old_table,
then drop old_table, rename new_table to old_table.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 How to reuse pages in other tables?



Quote:

>> Created a table: CREATE TABLE ...
>> Checked: oncheck -pT database:table

>>     Number of extents              1
>>     First extent size              4
>>     Next extent size               4
>>     Number of pages allocated      8
>>     Number of pages used           7
>>     Number of data pages           0
>>     Number of rows                 0

>> 1st question: Why number of pages allocated is 8 in 1 extent?

>You set EXTENT SIZE and NEXT SIZE to 4 pages but Informix will compress
>contiguous extents into a single extent containing all of the pages
>from both.  That is what has happened here, when the second extent was
>needed no other extents had yet been allocated in the chunk so the new
>extent was contiguous with the old one and they were compressed into
>one 8 page extent.

>> Loaded data:  LOAD FROM ...
>> Checked: oncheck -pT database:table

>>     Number of extents              2
>>     First extent size              4
>>     Next extent size               4
>>     Number of pages allocated      9336
>>     Number of pages used           9334
>>     Number of data pages           7955
>>     Number of rows                 103411

>> 2nd question: Why number of pages allocated is 9336 in 2 extents?

>Same here except that either:
>1) At some point another table had allocated an extent from the same
>chunk so that the next extent allocated to this table was not
>contiguous with the first extent so that a second was created.
>2) There was not enough room in the chunk containing the first extent
>to allocate a full "NEXT SIZE" extent there and the extent was
>allocated in another chunk (extents cannot cross chunk boundaries as
>they must contain contiguous space).
>3) Some space was freed up on the chunk (by dropping a table detached
>index or database) that was physically before the current extent on the
>chunk and that space was grabbed for the next extent.

>> Deleted data: DELETE FROM ...
>> Checked: oncheck -pT database:table

>>     Number of extents              2
>>     First extent size              4
>>     Next extent size               4
>>     Number of pages allocated      9336
>>     Number of pages used           9334
>>     Number of data pages           0
>>     Number of rows                 0

>> 3rd question: Can I use pages allocated for this table ( but not used )
>> for other tables?

>Only by dropping the table and recreating it.  You can perform such
>compression on a table with data by either

>1) unload the data (using dbaccess/4Gl unload command, dbexport, or
>onunload), drop the table, recreate the table, reload the data or
>2) create a new table insert into new_table select * from old_table,
>then drop old_table, rename new_table to old_table.

  Or do alter index to cluster for an index on the table.

Quote:
>Art S. Kagel

--
David Williams

Maintainer of the Informix FAQ
 Primary site (Beta Version)  http://www.smooth1.demon.co.uk
 Official site                http://www.iiug.org/techinfo/faq/faq_top.html

I see you standin', Standin' on your own, It's such a lonely place for you, For
you to be If you need a shoulder, Or if you need a friend, I'll be here
standing, Until the bitter end...
So don't chastise me Or think I, I mean you harm...
All I ever wanted Was for you To know that I care



Wed, 18 Jun 1902 08:00:00 GMT
 How to reuse pages in other tables?

Hi,
Answer your question one by one.


Quote:
>Hi, All!
>Please, help.

>Created a table: CREATE TABLE ...
>Checked: oncheck -pT database:table
>    ...
>    Number of extents              1
>    First extent size              4
>    Next extent size               4
>    Number of pages allocated      8
>    Number of pages used           7
>    Number of data pages           0
>    Number of rows                 0

==> Informix default first extent and next extent is 8k, or 4 pages.
Quote:
>    ...
>1st question: Why number of pages allocated is 8 in 1 extent?

>Loaded data:  LOAD FROM ...
>Checked: oncheck -pT database:table
>    ...
>    Number of extents              2
>    First extent size              4
>    Next extent size               4
>    Number of pages allocated      9336
>    Number of pages used           9334
>    Number of data pages           7955
>    Number of rows                 103411
>    ...
>2nd question: Why number of pages allocated is 9336 in 2 extents?

==> Informix count continues extents as one extent. Because you use load
command load huge data from file, so the disk use being used are conitnues,
that is the reason you see huge extent size.
Quote:
>Deleted data: DELETE FROM ...
>Checked: oncheck -pT database:table
>    ...
>    Number of extents              2
>    First extent size              4
>    Next extent size               4
>    Number of pages allocated      9336
>    Number of pages used           9334
>    Number of data pages           0
>    Number of rows                 0
>    ...
>3rd question: Can I use pages allocated for this table ( but not used )
>for other tables?

==> Once extent are allocated to table, they will occupy by table even you
delete rows. Only way to free up space is unload table, delete table and
load back data.

Hope I answer your question.

Paul Hsu
SKECHERS USA
228 Manhattan Beach Blvd
Manhanttan Beach, CA 90266
Phone : (310) 3183100 X 1287
Fax   : (310) 3185019



Wed, 18 Jun 1902 08:00:00 GMT
 How to reuse pages in other tables?

Quote:




> >> 3rd question: Can I use pages allocated for this table ( but not used )
> >> for other tables?
> >Only by dropping the table and recreating it.  You can perform such
> >compression on a table with data by either
> >1) unload the data (using dbaccess/4Gl unload command, dbexport, or
> >onunload), drop the table, recreate the table, reload the data or
> >2) create a new table insert into new_table select * from old_table,
> >then drop old_table, rename new_table to old_table.
>   Or do alter index to cluster for an index on the table.

That will defragment the table but I did not think that it will release
any space?  Does this really release unused space Dave?

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 How to reuse pages in other tables?



Quote:




>> >> 3rd question: Can I use pages allocated for this table ( but not used )
>> >> for other tables?

>> >Only by dropping the table and recreating it.  You can perform such
>> >compression on a table with data by either

>> >1) unload the data (using dbaccess/4Gl unload command, dbexport, or
>> >onunload), drop the table, recreate the table, reload the data or
>> >2) create a new table insert into new_table select * from old_table,
>> >then drop old_table, rename new_table to old_table.

>>   Or do alter index to cluster for an index on the table.

>That will defragment the table but I did not think that it will release
>any space?  Does this really release unused space Dave?

  I though it would since it since it effectively rebuilds the table
  behind the scenes using another partnum, hence you need 2x the space
  of the table.

  After all it must allocate pages (I've done this and the table
  moved chunks). the code for extent allocation is already there and
  so just creating a new partnum would probably be the way Onine does
  it (why would the Online developers write new code when you can reuse
  the existing code).

Quote:
>Art S. Kagel

--
David Williams

Maintainer of the Informix FAQ
 Primary site (Beta Version)  http://www.smooth1.demon.co.uk
 Official site                http://www.iiug.org/techinfo/faq/faq_top.html

I see you standin', Standin' on your own, It's such a lonely place for you, For
you to be If you need a shoulder, Or if you need a friend, I'll be here
standing, Until the bitter end...
So don't chastise me Or think I, I mean you harm...
All I ever wanted Was for you To know that I care



Wed, 18 Jun 1902 08:00:00 GMT
 How to reuse pages in other tables?

If you are talking about alter index to cluster then yes it does free =
space, down to the limit of the initial extent size.
I have replication  log tables that are very volatile and can be blown out =
to several hundred meg in a days activity during the peak of the season.  =
I resize these nightly using 'alter index to cluster' and the space is =
recovered.

Just to complete Davids list, my fave for recovering space, and simultaneou=
slly moving the table to a different dbspace is to use the fragment =
syntax.

fragment table tablename init in dbsname;

rgds


Zespri International Limited.      Standard Disclaimers apply
All rights reserved, no party may use this document to vilify another

Zespri New Zealand Kiwifruit, The Worlds Finest

Quote:


>=20



> >> 3rd question: Can I use pages allocated for this table ( but not used =
)
> >> for other tables?
> >Only by dropping the table and recreating it.  You can perform such
> >compression on a table with data by either
> >1) unload the data (using dbaccess/4Gl unload command, dbexport, or
> >onunload), drop the table, recreate the table, reload the data or
> >2) create a new table insert into new_table select * from old_table,
> >then drop old_table, rename new_table to old_table.
>   Or do alter index to cluster for an index on the table.

That will defragment the table but I did not think that it will release=20
any space?  Does this really release unused space Dave?

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 How to reuse pages in other tables?

Quote:








[All useful stuff SNIPPED]

Quote:
>   it (why would the Online developers write new code when you can
>   reuse the existing code).

Ahh another unknowing adherent of Kagel's Second Law of Programming,
towit:

"Programmers are lazy, and should be."

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 How to reuse pages in other tables?

Quote:
>>   Or do alter index to cluster for an index on the table.

>That will defragment the table but I did not think that it will release
>any space?  Does this really release unused space Dave?

Yep - compresses the data pages under a new tablespace.

Madison Pruet



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Paging Cliff Helsel - others pls ignore

2. How can I publish a snapshot off a table with Foreign Keys to others tables

3. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

4. Reuse of Replicated DB when tables have IDENTITY columns

5. How can I reuse results in a temporary table

6. Reusing free space in a table

7. Empty datablocks not reused when inserting in a clustered table

8. Table space not reused

9. Reusing reports with another table

10. Reuse of Temp Tables w/Teradata

11. how are others encrypting passwords in sql7 tables

12. Create a table with the common elements of 2 others


 
Powered by phpBB® Forum Software