Re: Joins and the Optimizer - a question

David Williams <djw@smooth1.demon.co.uk> wrote in article <Hxeg3vAVHVXzEw$W@smooth1.demon.co.uk>... > Remember clustered indexes do not stay clustered - When a new row is > added is goes on the end of the table. Therefore this will not work. I was hoping to take advantage of the fact that we have some tables that are loaded only once a month. Now each month after we did a load, we would have to re-cluster the table, since as you said the new rows would go at the end of the table. But I was hoping that the time it would take to cluster the tables would be worth it, since every join between these tables for the month would be much faster since it could do just a merge-join as opposed to a hash-join. In the sysindexes tables, there is a field called clustered, which indicates if the index is clustered or not. So I was wishing that somehow this could tell the optimizer that the table is ordered by the fields of that index. If not, I don't see why that field is in the sysindexes table. It could tell you that at some point that index was clustered, but rows might have been added since and so it's no longer really clustered. I guess it's used to prevent you from clustering another index, but if the optimizer doesn't use the knowledge that an index is clustered, why can't you just re-cluster another index (instead of using ALTER INDEX to un-cluster an index)? One day I cluster index 1, the next day I cluster index 2, obviously the rows are ordered by index 2 now instead of index 1, but it seems like the engine could care less how the rows are ordered, and wouldn't care which index, if any, is clustered, or that a different one is now clustered. Someone mentioned that he thought that when a row is added, a clustered index is not updated, so therefore the engine needs to know if an index is cluster so as to not add the row to it. I do not find this fact mentioned in the manuals, but if this is true it would seem to be a bad thing because if rows had been added, those rows would not be pulled back if that index is used (I would think that the optimizer would not use that index due to it being unreliable). See ya, Paul