August 15, 2007 at 8:35 am
I'm working on an existing database where the indexing is not great. For example, the primary key is composed of two columns: MONTH_DATE and ACCOUNT_ID. The order of the columns in the index is as specified.
The granularity of data in the first part of the index is very low (it only stores end of month so we have only about 50 distinct values) and it would be better to swap the columns around to improve performance.
The problem is that the primary key is used as a foreign constraint in another table so the standard DROP EXISTING in a create index statement will not work.
I could drop the constraint on the other table, recreate the index and then recreate the constraint but this situation exists on dozens of tables within the database. Another alternative would be to create a new index with the columns in the proper order but then we end up with two essentially identical indexes on the same table which I'm not happy with.
Has anyone got any ideas how I might go about swapping the columns around in the index?
J
August 15, 2007 at 9:02 am
If the problem is widespread, I don't see any great way to reorder them except by dropping and recreating.
However, if you don't have many indexes, why not just add an index in the order you want? It doesn't have to be a PK and it still could be a unique index. Indexing isn't design, it's tuning.
If the index clustered? If that's the case, you could rebuild it as not clustered, keeping it the PK, and then add a clustered index in the order you want.
August 15, 2007 at 9:09 am
I think you are probably right in adding a new index with the columns in the order that I want. The existing PK is clustered so the new index could not be clustered.
I created a new index on one table and the query time droped from 60 seconds to 3 secs (its a datawarehouse so I'm not expecteing subsecond repsonse times) so it's worth doing.
I guess I will have to accept that I will have a superfluous index on various tables. I don't have many indexes on each table (2 or 3 typically) so adding an extra one is not a real problem - I know that there is a small overhead when I insert rows with the extra index but it is a price worth paying.
J
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply