Re: Poser? What happens to empty pages in a database unload/load?

Thanks, Art, but your statement is not consistent with what I see
happening in my company on a weekly basis.

At my company, several time each weekend, a database is unloaded at
one server, the UNL set sent via RCP to another server, where that
database is loaded afresh. If indexes were lot loaded back at the
target server, the users would be complaining real quick about bad
performance.

Still, thanks for attempting this one.

-- J.


On Mar 17, 6:57 am, "Art S. Kagel (Oninit)" <a...@xxxxxxxxxx> wrote:
Beau Nanaz wrote:

Beau, see below:



Hi Family,
This is your errant, infrequently writing relative with a question
that seemed to stump my tech support guy.

My users had a minor scare last week when he unloaded and loaded a
database (onunload/onload). Before the unload, the database occupied
about 10 million pages and after the load it occupied only about 7
million pages. Missing 3 million pages of data? Well, after checking
my facts with IBM personel, I explained that a common benefit of
unloading and loading a database (or table) that that empty pages are
squeezed out of the load.

(Of course, we presume that a sparse page remains sparse; you would
need and export/import to squeeze that out.)

One more assumption: That index pages are unloaded and loaded the same
way as data pages. This is one assumption I suspect needs correction.

Yes it does.



I am stating all the above to get my assumptions out in the open. Now
the question:

Suppose you have a table where, due to deletions, pages 900-1000 are
all empty but I still have data in pages 1001-2000 of the tablespace.
I unload the table and load it back and now all those empty pages are
gone. The rows that were in pages 1001-2000 are shifted down to fill
the gap.

The problem I see is that those rows in page 1001 had rowid's that
identify page 1001. Any index on that table has been referring to
those rows as being in page 1001. Those index entries are now wrong.
For this reason, the index pages can not just be loaded back as I
assumed above.

The alternative explanation is that the indexes are all rebuilt when
the table is reloaded. If this is the case, it would pay to set
PSORT_NPROCS before starting a load.

I simply want to understand what's happening internally. (I am kicking
myself for not have asked this 15 years ago. ;-)

AFAIK, onunload only unloads data and blob pages. Rows on pages
onloaded back to the table are added to any existing indexes at load
time. The index is not rebuilt so PSORT_ parameters will not help
unless you drop the indexes prior to the onload and recreate them
afterwards, which will tend to be faster if you are loading a
significant portion of the table.

Art S. Kagel
Oninit



Thanks much.

-- J
_______________________________________________
Informix-list mailing list
Informix-l...@xxxxxxxx
http://www.iiug.org/mailman/listinfo/informix-list

===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.oninit.com/home/disclaimer.php

===========================================================================================

===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.oninit.com/home/disclaimer.php

===========================================================================================


.

Relevant Pages

  • Poser? What happens to empty pages in a database unload/load?
    ... Before the unload, the database occupied ... squeezed out of the load. ... all empty but I still have data in pages 1001-2000 of the tablespace. ... I unload the table and load it back and now all those empty pages are ...
    (comp.databases.informix)
  • Re: Generating one table with a terabyte of data
    ... I didn't know we could split a database file group over multiple HDs. ... > Run perfmon counters to a log and analyze them after a bulk load. ... Another thing is to split the updates across file sets. ... >> loading of daily data in the fastest time. ...
    (microsoft.public.sqlserver.dts)
  • Re: ORACLE RAC and ORACLE Standby
    ... standby is better for disaster recovery and RAC is ... with taking any downtime on your primary database instance. ... The standby database provides a local or off-site recovery instance. ... Load balancing capabilities of RAC include ...
    (comp.databases.oracle.server)
  • Re: Too many objects loaded at the same time and cause OutOfMemory exception.
    ... Stored procedures won't necessarily help here, ... allow the database to pre-plan the implementation. ... There should be a way to load objects without having to load all ... each student has 5 lessons. ...
    (microsoft.public.dotnet.framework.aspnet)
  • VM in 2.6 doing a worse job of caching than 2.4?
    ... day by a proprietary database system. ... I recently started evaluating the 2.6 kernel for these machines. ... and see's CPU idle of 30-35%. ... Theoretically, they both should receive similar traffic, though the load ...
    (comp.os.linux.development.system)