Indexes degrade performance of DML operations Insert / Update /Delete
As many as indexes increases cost of DML sql statements.
Insert - Sql need to find correct nodes as per indexes defined on table to insert data then it checks whether node have enough space to store data if not then it splits node and distributes entry in split nodes that is also having cost. Also balances nodes in the tree.
While for table without index, sql directly can inserts entry in available data blocks.
More number of indexes more cost.
Insert statements has more cost than update and delete for indexed tables.
Update - Traverse using tree, removes the old references and adds new references, balances tree
More number of indexes more cost.
Delete - Traverse using tree, removes the old references, balances tree
More number of indexes more cost.
As many as indexes increases cost of DML sql statements.
Insert - Sql need to find correct nodes as per indexes defined on table to insert data then it checks whether node have enough space to store data if not then it splits node and distributes entry in split nodes that is also having cost. Also balances nodes in the tree.
While for table without index, sql directly can inserts entry in available data blocks.
More number of indexes more cost.
Insert statements has more cost than update and delete for indexed tables.
Update - Traverse using tree, removes the old references and adds new references, balances tree
More number of indexes more cost.
Delete - Traverse using tree, removes the old references, balances tree
More number of indexes more cost.
No comments:
Post a Comment