Creating Indexes ....Very slow?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Creating Indexes ....Very slow?

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    I am trying to create a index on a table which has as many as 37Million records. This is a child table and has the foreign key with the parent table.

    Earlier in the day, I created the indexes on the column(which is also the foreign key) and the index creation went through in less than 45 minutes. At that time the FK constraint was disabled.

    Now I have enabled the FK and am trying to create index on another column, It's taking a long time more the 3hours and still going on.

    Is there any relation between FK constraint and Indexes.
    Please clarify.

    Badrinath

  2. #2
    Join Date
    Mar 2001
    Posts
    45
    Just confirm whether there are any long running batch jobs going in parallel.

    If your in Unix , try with 'top' command to see what is the swap free memoray and %CPU utilised for this process.

    Thanx
    Ramesh.
    ______________________________
    There is nothing Impossible.
    Even Impossible says
    I M POSSIBLE

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    There is no correlation between the FK and a creation of an index. When creating the index, FK constraints need not to be checked, they were checked either when constraint was created or when the data was inserted/updated.

    So how can it be that index on one column takes a couple of minutes, while index creation on on another column of the same table can take an hour? It depends on many things. For example, the majority of values in the first index column might be null (nulls are not included in regular B*tree indexes), while in the second index column there might be "not null" constraint. Or the selectivity of the column of the first index is very low, while in the second index's column it is high, hence spending much more time on performing sorting.

    Setting the larger sort_area_size mighd speed up the index creation process significantly, this would be my first shot.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width