Classics Forum

Re: Informix SE 7.25 Recovery followup

Posted By: Art S. Kagel (Oninit)
Date: Monday, 7 April 2008, at 4:51 p.m.

In Response To: Re: Informix SE 7.25 Recovery followup (Walt Hultgren)

Walt Hultgren wrote:
> ----- Original Message -----
> From: "Art S. Kagel (Oninit)" <art@oninit.com>
> To: <classics@iiug.org>
> Sent: Monday, April 07, 2008 11:26 AM
> Subject: Re: Informix SE 7.25 Recovery followup [2759]
>
>
>> [...]
>>
>
>
>>>> You can create the indexes before the load or after.
>>>>
>>>>
>>> The above statement is technically correct, but I thought the
>>> conventional
>>> wisdom was to load tables before indexing, because bulk indexing is much
>>> faster than row-by-row index insertion. That used to be the
>>> recommendation, anyway, though I haven't done any timing tests recently.
>>>
>>>
>> Walt, you are correct as usual. Building the indexes after the load
>> will under ideal conditions result in faster total load times. However,
>> sometimes, especially on a busy or resource starved server it can be
>> almost as fast to load with the indexes in place since you cannot take
>> advantage of parallel index builds anyway. Similarly on many smaller
>> databases the time savings is not worth the extra hassles. I simply
>> answered the question that was asked. I should have been more verbose
>> and explained. Me succinct! What's the world coming to?
>>
>
> Don't worry. It will take more than one post for us to accuse you of that.
> :-)
>

;^)

> A follow-up on this thread related to engine-enforced referential
> integrity... If any of the tables have named primary or foreign keys, the
> above discussion will be moot because indexes will be created when the
> tables are re-created as part of disaster recovery. Correct?
>

That depends on how the schema file is processed. I had suggested to
Will that he utilize my myexport utility. That optionally takes
advantage of a nice feature in myschema to write all of the index,
trigger, and constraint DDL to a separate .sql file which myimport can
run after the load is complete. Dbimport does the same thing, but by
performing the load before creating the manual indexes for the table.
Unfortunately, as you note, the constraint indexes and constraints are
already in place. Myimport covers the possibility of dirty data by
optionally putting the constraints into FILTER mode to remove bad data
when the constraint is added to the table later.

> Question 2: If tables are rebuilt using "LOAD FROM ... INSERT INTO ...",
> will SE enforce referential integrity constraints during that process? I
> know that data that was clean going out should be clean going back in, but
> things happen. Is there any way of postponing those checks short of
> dropping the constraints before loading?
>

It should. Don't know, actually, if SE supports constraint filter mode.

Art S. Kagel
Oninit

> Walt.
>
>

Messages In This Thread

• Informix SE 7.25 Recovery followup
  WILL LANDSTROM -- Sunday, 6 April 2008, at 10:08 a.m.
  • Re: Informix SE 7.25 Recovery followup
    Art S. Kagel (Oninit) -- Sunday, 6 April 2008, at 11:36 a.m.
    • Re: Informix SE 7.25 Recovery followup
      Walt Hultgren -- Monday, 7 April 2008, at 10:59 a.m.
      • Re: Informix SE 7.25 Recovery followup
        Art S. Kagel (Oninit) -- Monday, 7 April 2008, at 11:26 a.m.
        • Re: Informix SE 7.25 Recovery followup
          Walt Hultgren -- Monday, 7 April 2008, at 11:52 a.m.
          • Re: Informix SE 7.25 Recovery followup
            Art S. Kagel (Oninit) -- Monday, 7 April 2008, at 4:51 p.m.
      • Re: Informix SE 7.25 Recovery followup
        LazarusGOP@aol.com -- Monday, 7 April 2008, at 12:53 p.m.
        • Re: Informix SE 7.25 Recovery followup
          Art S. Kagel (Oninit) -- Monday, 7 April 2008, at 4:43 p.m.

Classics Forum is maintained by Administrator with WebBBS 5.12.