Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Zoetermeer, Holland

    Unanswered: INSERT performance & NULLABLE indexes

    When you add more indexes to your table your INSERT performance get worse. That's a no-brainer no questions about that.
    But when the index is on a NULLABLE column and the content is always NULL. When you INSERT, what is the impact then? The update, giving that column it's value, will be more heavy, I can reason that out, but the INSERT? There is no INDEX to be updated, is there?
    And when an index is composed of a NOT NULL + a NULL column? Do we get a index entry during INSERT or not?

    Anyone ever found any documentation on those issues? Or done some testing?

  2. #2
    Join Date
    Dec 2005
    Whenever you insert a row into a table, a corresponding entry is inserted into each index of that table, regardless of the value the indexed columns contain.
    Even, if all of them contain <NULL>.

  3. #3
    Join Date
    Nov 2005
    +1 on above.

    If the content is always NULL why have an index on it? Or it is NULL during the initial INSERT only?
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os

  4. #4
    Join Date
    Jan 2007
    Jena, Germany
    An index can still be beneficial if the column is "mostly" NULL. If it is really "always" NULL, then why have this column in the first place?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2009
    Zoetermeer, Holland
    Quote Originally Posted by Cougar8000
    Or it is NULL during the initial INSERT only?
    NULL during insert. I'll explain:
    I am trying to tackle the "well known paradox" of relational vs object-oriented. The database I'm currently managing is "not really" according to the relation rules. I see many different tables sharing the same primary-key. The J2EE application just utilizes DB2 as an persistance-instrument. Well, you do not have to like it, you'll have to deal with it.

    My attempt:
    Let's create one big table to hold all the data and create a userview for each "table/object" the application sees.
    This means:
    - give each column name a code-suffix in the table and use the "as" clause in the view to give it back it's original name
    - write "instead of" triggers to capture the INSERT/DELETE fired from the J2EE app.
    - appoint 1 column per view to act as an boolean (true = this row is also a row in this view) . I was lucky to find a technicall field "creation_date" in all tables so I could use that. This same field is used in my CREATE VIEW in the WHERE clause. I test it for NOT NULL.

    Now we're back on topic. Shall I make that column an index or not? Some figures:

    it is one physical table with 27,000,000 rows
    there are 16 views defined as described above varying from 10,000,000 rows to only 12,000 rows. So I am left with 16 CREATION_DATE_code columns.
    The paradox I am facing:
    1) no index. a simple "select count(*) from view" is never executed. whenever it is executed, I left with a table-scan over 27 miljon rows and nobody likes me any more

    2) index. I was hoping that an INSERT in the main-table with 16 NULL columns forming 16 indexes would not generate too much delay because they are NULL... Thank you umayer for shaking me out of that dream.

    3) middle of the road. I'll just INCLUDE those 16 columns in my primary-key-index. Whenever a "select count(*)" is fired, it is not a table scan but only an index-scan.....

    I have not figured it out yet. help will be appriciated.
    Last edited by dr_te_z; 05-21-09 at 07:10.

  6. #6
    Join Date
    May 2009
    Provided Answers: 1
    dr_te_z, Indexes are a two-edge sword. Don't ignore one edge (Select performance) because the other edge (Insert performance) may be affected.

    For example, an Insert with 16 Indexes may take a second longer (could be less). This isn't necessarily good or bad depending and required response time. But consider that without the indexes, each of those 16 views are doing Table space scans (taking 30 minutes, 1 Hour?). With the indexes, accessing the data may take 1 minute (or less as it really depends on how uniquely the view filters data).

    Do you take no performance hit on an Insert but a huge performance hit on Selects (no Indexes)?
    Take a small hit on performance on an Insert but a huge performance improvement on Selects?

    The answer depends on your expectations and requirements.

    In the real another thing to consider is do you need all that data on-line during transaction processing or can it be moved to a data warehousing table where it can be indexed without affecting transaction processing. But that is another topic.

Posting Permissions

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