Table and Index manipulation

Posted on 2009-04-25
Last Modified: 2012-08-13
1) When we INSERT a record in a table, an entry gets into all its indexes whether clustered (physically sorted) or non-clustered (logically sorted through pointers).
2) When we UPDATE/DELETE a table, index also gets affected.
3) How does all this take place? Post me link(s) if it is too big to fit in here.
Question by:k_murli_krishna
    LVL 41

    Assisted Solution

    1) Yes. All indexes are updated when new records is inserted.
    2) Yes.
    3) Everything is done in one internal transaction to ensure data integrity:
    - The INSERT/UPDATE/DELETE command is issued
    - Transaction begins, appropriate records are locked if necessary
    - All checks are validated (constraints, uniqueness, triggers are fired, etc.)
    - If all checks succed the record is inserted/updated/deleted
    - All indexes are updated
    - The internal transaction is commited and locks are released to allow other processes access data

    LVL 37

    Expert Comment

    what do you mean by how it is done?
    why are you asking? sheer interest or do you have a problem you are trying to understand the cause?
    LVL 17

    Author Comment

    Sheer interest. When there is a index scan on a clustered index it is quite clear since it is physically sorted. But on a non-clustered index, values are sorted in indexed column via pointers. How these pointers are reached and how do they point to the 1st, 2nd, 3rd etc. ordered values? Similarly on update/delete, how records in index are identified?
    LVL 41

    Assisted Solution

    Records in indexes are identified by key data values. If you have index created on LastName column you can access this index by any value which represents the LastName from indexed table.

    I would say it does not matter if you have clustered or nonclustered index. Its structure is almost the same. It is some binary tree or another index tree which contains key values organized in a way which could easily and quickly find the requested key. If you find the key then it is always followed by pointer to raw data. Other pointers can point to the previous and next index key value to easily trace the table rows in indexed order. Thus deleting index means no data loss.

    Creating the index does not reorganize data except of Clustered index creation/update. When you create Clustered index data are physically resorted in the same order as index keys. This allows faster sequential access after the key match. But the clustered index must reorganize both index and table data after each insert/update so it is slower than nonclustered one.

    The ideal index tree is symmetric. It ensures the shortest path when looking for any key value. Continuous table updates, inserts, and deletes make the tree asymmetric obviously because you cannot control application behaviour. So indexes must be balanced during some data maintenance.

    Some systems store all data in indexes only. Each table column is indexed so there are no "raw data" present in database.
    LVL 17

    Author Comment

    Thank you pcelba. One last question. When index scan occurs is binary search carried out i.e. middle value is checked with sought value in WHERE and HAVING to split the relevant set to half each time? If so, what is the advantage that this binary search gives for GROUP BY and ORDER BY which are essentially the same unless grouping has a relevance for the end results?
    LVL 37

    Accepted Solution

    first things first, pcelba is mistaken
    when you create a clustered index in sql server, the data is sorted
    in db2 physical table data is sorted only during reorg or load operations

    db2 does not use a binary search in the index
    the index is not organized as a binary tree, it is organized as an n tree, that is,
    it is possible that the first level will be the root level,
    the next level will contain 100 pages,
    the next one will contain 10k pages (100*100) etc,
    which make the tree much more efficient than a binary tree
    when db2 search for a value x in the index, it will look at the root and will search for the first value that is greater than x (in a db2 index, each pointer points to the largest key value in the next level). then it will follow that pointer to the second level in the index. in the second level page, it will again look for the first value greater than x etc...

    the index helps GROUP BY and ORDER BY operation by eliminating the need to sort the data. for ORDER BY, if you have an index, no work should be done,
    for group by, avoiding the sort will allow db2 to go straightly to the computation of the aggreagate functions
    LVL 41

    Assisted Solution

    Thanks for the clarification momi_sabag. I would point to one more advantage of indexes and it is query optimization based on WHERE expression. If the index contains all the keys which are used in WHERE expression then the initial data "extraction" does not look at raw data and everything is done based on index files only.
    LVL 17

    Author Closing Comment

    Thank you very much, momi & pcelba.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now