Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: Index pages split in db2

    Hi,
    I have a question on index pages split in db2.
    For example,lets say a 4k index page has following entries in it,
    1
    2
    3
    8
    9
    10

    and then i insert values 4,5,6 into the table for this key.
    Would index page splits happen in DB2(LUW), so after insertion physical allocation looks like this:
    page1
    ------
    1
    2
    3

    page2
    ------
    4
    5
    6
    8
    9
    10

    or db2 re-adjusts the index free space in the page during insertion and inserts these records in the same page.
    Please help me understand this concept.
    I am using db2 v9.5, v9.7 LUW.
    How does db2 manage this scenario in db2 z/OS.

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    New index keys are placed on the same page if there is still enough space. Splits only occur if you run out of space. So in your example, no index leaf page split is done because there are just 10 index entries.

    Pretty much all database systems handle things that way. The reason is quite simple: adding another page to the index introduces more overhead because additional I/O may be needed. Furthermore, space utilization would degrade. Doing an in-place reorg and shuffling the index entries around is a lot cheaper in the long run.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    Thanks Stolze.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Also, see PAGE SPLIT .... options on the create index statement (LUW)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •