Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: creating index on temporary table --> when?

    Hallo everyone,

    I would like to create index on my temporary table -DECLARE GLOBAL TEMPORARY TABLE-

    In this table, I will do INSERT INTO of millions of rows. After that, this table will be used for a recursive aim. My questions:
    1. When should I create the index? Directly after creating the temporary table or directly after I have inserted the rows in the table?
    2. I would like to create the very simple index on the table(on 2 columns). Are these the same?

    Code:
    CREATE INDEX session.indexName
                 ON session.myTable(column_01,column_02);
    Code:
    CREATE INDEX session.indexName_01
                 ON session.myTable(column_01);
    
    CREATE INDEX session.indexName_02
                 ON session.myTable(column_02);

    Thank you for your help.
    Regards,
    Ratna

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ratnalein View Post
    Hallo everyone,

    I would like to create index on my temporary table -DECLARE GLOBAL TEMPORARY TABLE-

    In this table, I will do INSERT INTO of millions of rows. After that, this table will be used for a recursive aim. My questions:
    1. When should I create the index? Directly after creating the temporary table or directly after I have inserted the rows in the table?
    2. I would like to create the very simple index on the table(on 2 columns). Are these the same?

    Code:
    CREATE INDEX session.indexName
                 ON session.myTable(column_01,column_02);
    Code:
    CREATE INDEX session.indexName_01
                 ON session.myTable(column_01);
    
    CREATE INDEX session.indexName_02
                 ON session.myTable(column_02);

    Thank you for your help.
    Regards,
    Ratna
    It is usually better to create the indexes after the data has been inserted. That way you will "maintain" the indexes once. If you create the indexes before data is loaded the indexes has to be updated for each and every row that you insert.

    In general a composite index

    (column_01,column_02)

    is better than two individual indexes, but if you has a predicate that only uses column_02 you wont benefit from this index. DB2 can do "index anding" but it is more efficient to use one composite index.
    --
    Lennart

  3. #3
    Join Date
    May 2012
    Posts
    155
    thank you for the inputs, lelle12

Posting Permissions

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