IDS Forum

Re: create indexes before or after inserting rows?

Posted By: Marcus Haarmann
Date: Monday, 1 June 2015, at 11:00 a.m.

In Response To: Re: create indexes before or after inserting rows? (Art Kagel)

Hi,

there are some things to take into consideration depending on the amount of data loaded.
1) If you load a lot data, and your goal is to optimize the load process for timing, I would say you have to add the index afterwards, best with the online keyword,
since the data will be available at the earliest time (followed by a big checkpoint probably). Also (in case you do not need HDR or a Log Backup (data is restorable
with the same load process)) best way would be to load without logging.
2) When loading data with the indexes already active, the load process will take longer, since each insert has to produce the index leaves.
While loading, the index might become unbalanced and the background process might start re-balancing the index, which can take some time,
but the index will be present immediately after load (which might be an advantage)
3) When loading a lot of data, consider using HPL, which does things in parallel. Also here, I would recommend to build the indexes after load,
even if it seems to take long when the indexes are created.
4) There is no ideal solution, it depends on your actual data. For small tables, maybe creating the index before the load might not harm the performance,
but there is a reason that dbimport creates all indexes after load ....
5) Table extends should be pre-calculated roughly when loading a lot of data in one run. Of course there are ways to re-organize tables online,
but thinking about extends before the load just helps to avoid that.
6) Fragmentation can be used to help when loading very huge amount of data (load into different fragments of same structure in parallel and attach
afterwards). This is only possible if the load data can be split using a well known mechanism, which can be described as expression (e.g. on a date base).
Indexes will be smaller then (per fragment) but I did not measure timing when index is created before/after load/attach.

Marcus Haarmann

----- Ursprüngliche Mail -----

Von: "Art Kagel" <art.kagel@gmail.com>
An: ids@iiug.org
Gesendet: Freitag, 29. Mai 2015 22:54:07
Betreff: Re: create indexes before or after inserting rows? [35186]

Yeah, see that's why I mentioned that you should have stated your version!
B^(

The API functions were introduced in v11.50 but the DEFRAGMENT operation
wasn't added until v11.70.xC1 so you will have to use either the REPACK
SHRINK, the ALTER FRAGMENT, or your method. Sorry.

Art

Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.

On Fri, May 29, 2015 at 4:43 PM, BEN KOPACZ <ben.kopacz@gmail.com> wrote:

> Hello Art,
>
> Thank you for your expert answers! I was indeed planning on using dostats.
> I'd
> like to try your proposed method:
>
> - Set the new first extent and next extent sizes.
>
> - Execute the DEFRAGMENT SQL API function for the target table:
>
> execute function sysadmin:task( 'DEFRAGMENT', 'database:tablename' );
>
> Provided that it would work with IDS Version 11.50.FC5, which is what we're
> using.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001a113f90ea2747cb05173eadc7

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• create indexes before or after inserting rows?
  BEN KOPACZ -- Friday, 29 May 2015, at 3:15 p.m.
  • RE: create indexes before or after inserting rows?
    Mueller, Daniel D. -- Friday, 29 May 2015, at 3:17 p.m.
    • Re: create indexes before or after inserting rows?
      Jack Parker -- Friday, 29 May 2015, at 4:02 p.m.
  • Re: create indexes before or after inserting rows?
    John Miller iii -- Friday, 29 May 2015, at 3:27 p.m.
    • RE: create indexes before or after inserting rows?
      Everett Mills -- Friday, 29 May 2015, at 3:37 p.m.
    • Re: create indexes before or after inserting rows?
      Art Kagel -- Friday, 29 May 2015, at 4:30 p.m.
      • Re: create indexes before or after inserting rows?
        BEN KOPACZ -- Friday, 29 May 2015, at 4:50 p.m.
  • Re: create indexes before or after inserting rows?
    Art Kagel -- Friday, 29 May 2015, at 4:29 p.m.
    • Re: create indexes before or after inserting rows?
      BEN KOPACZ -- Friday, 29 May 2015, at 4:43 p.m.
      • Re: create indexes before or after inserting rows?
        Art Kagel -- Friday, 29 May 2015, at 4:54 p.m.
        • Re: create indexes before or after inserting rows?
          Marcus Haarmann -- Monday, 1 June 2015, at 11:00 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.