Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: mutliple index reads on an insert

    Can someone explain why an insert cause multiple logical index reads?
    Our application does bulk inserts and insert of each rows caused about 20-30 index reads, if the reads are physical we have a slow insert. The table has 4 indexes , 3 unique and 1 non-unique ( no clustered indexes). We are having random insert spike because of index reads.

  2. #2
    Join Date
    Oct 2005
    Posts
    4
    so on an avg bulk insert of 200 rows causes about 4000 index reads, each index is isolated to its own tablespace (table partitioning) each index gets about 1000 reads.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Do you think that referential constraints might be somehow involved?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Obviously it has to insert the data into the index, and it has to traverse the b-tree to find the correct page to update, so this could account for several reads each time. If there is no room on the correct index page for the new data, then a page-split of the index page is necessary, which can be costly and require additional index reads.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Oct 2005
    Posts
    4
    There is no referential constraints on this table.

    Marcus,
    How can i monitor for page-splits?
    Here is from db2pd:
    IIDEmpPgDel BndrySplts PsEmPg Scans KeyUpdates NonBndSpts PgAllocs PseuDels DelClean IntNodSpl
    4 79 0 82 588287 550865 32767 33367 550865 123563 723
    3 21 0 24 300991 550209 28283 29485 550209 474021 1245
    2 147 3915 151 12 550209 25537 29569 550209 56946 342
    1 20 0 23 100 550209 28095 29275 550209 436091 1222

    RootSplits=0
    InclUpdats=0
    Merges=0
    What can we do to iptimize this?

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by span3d
    Our application does bulk inserts
    In that case it could make sense to postpone the index updates to after the bulk of inserts. This is what the LOAD utility does (as opposed to the SQL INSERT).
    Maybe that's an option?
    (If on LUW, also have a look at the INDEXING MODE option of LOAD.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Oct 2005
    Posts
    4
    load is not an option for us, the table is updated/read constantly...

Posting Permissions

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