I know that when we create a table in SQL Server with its primary key, a clustered index is automatically created. But if I delete a line from this table, does the clustered index related to this line still remaining at the index table or it automatically deleted? If it is not automatically deleted, do I have to create a job to rebuild and reorganize the indexes? (is the same for mysql, oracle, etc?)
-
Of course the clustered index (like any other index) is constantly updated. That's why too many indexes can be worse than no indexes at all! Having an index always also carries the burden of maintaining it!marc_s– marc_s2014-10-26 18:03:58 +00:00Commented Oct 26, 2014 at 18:03
Add a comment
|
2 Answers
I know that when we create a table in sqlserver with its primary key, a clustered index is automatically created.
This is only true if it's the default options or clustered is specified as a keyword. Primary keys can be non-clustered indexes as well.
But if I delete a line from this table, does the clustered index related to this line still remaining at the index table or it automatically deleted?
If it truly is a clustered index, then the index IS the table data. If you delete something from the table, it's gone. There are a few internal workings, such as ghost records, but yes the data is gone (from the application point of view).
If it is not automatically deleted, do I have to create a job to rebuild and reorganize the indexes?
It is, there are also other background tasks that deal with certain "hidden" functions. Eventually you'll want to re-org or rebuild your indexes when fragmentation (internal or external) starts to become a problem. This will depend on other variables including table structure, access, etc.
(is the same for mysql, oracle, etc?)
The question was tagged SQL Server, so I'm answering the SQL Server tag. That's a VERY broad question for a single post.
Comments
The defaults are not the same for Oracle or MySQL. Each database has its own defaults, and its specific features. Some even use the same term for different meanings.
Oracle doesn't default to a clustered index, and in Oracle, the equivalent is an Index Organized Table. In Oracle, the definition of CLUSTER is a structure which may store 2 or more tables and order them the same.
If it is not automatically deleted, do I have to create a job to rebuild and reorganize the indexes?
The #1 rule of rebuilding indexes - Measure, measure, measure. Prove that the rebuild was beneficial, else don't bother to do it again unless things change.
A simple delete (or thousand) is not an automatic reason for rebuilding an index.
If you are about to rebuild an index, you should know (1) how many data blocks before and after (2) the access times before and after.
There is much misinformation and superstition regarding index rebuilds as a general practice. The indexes you are referring to are B-Tree structures. These are designed to be scalable, O(log N) access. There is no proof that indexes have to be reorganized by default. Each index is its own animal. B-tree indexes reach a point of stasis (equilibrium) after some use time, and when you rebuild them you compact them (a good thing), but they eventually drift back to that point of stasis. Unless I spot a performance / IO issue around one of them, then I will do it manually on a case by case basis.
The two biggest benefits of rebuilding:
- More densely packs the data into less blocks, which improves cache and IO.
- Reorders non-clustered indexes by the actual data that has accumulated. If your access patterns are always sequential (including inserts) then this isn't usually a problem, the blocks will be ordered.
Oracle and SQL Server are wonderful pieces of technology. Rebuilding indexes without proof is no good for a professional DBA.
1 Comment
Just to be clear, the intention of rebuilding is always
1.More densely packs the data into less blocks, which improves cache and IO. You may be making room for inserts, etc, depending on how your clustered index was chosen so that page splitting isn't a problem. This would actually be the opposite of what is described. I'm not nit picking, just pointing out use cases I've actually encountered.