-
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
-
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.
-
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,
