• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

Table and Index manipulation

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.
0
k_murli_krishna
Asked:
k_murli_krishna
  • 3
  • 3
  • 2
4 Solutions
 
pcelbaCommented:
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

0
 
momi_sabagCommented:
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?
0
 
k_murli_krishnaAuthor Commented:
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?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
pcelbaCommented:
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.
0
 
k_murli_krishnaAuthor Commented:
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?
0
 
momi_sabagCommented:
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
0
 
pcelbaCommented:
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.
0
 
k_murli_krishnaAuthor Commented:
Thank you very much, momi & pcelba.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now