In one of the recent interviews I was asked DB question - There is a DB with ta table having million rows. Over time will it's performance degrade. Why and how? My answer was due to fragmentation (We already discussed indexes and other performance impacts large table has). What the interviewer was primarily interested is to know how data is stored in blocks for a DB and how fragmentation really affects the performance.

He also gave an example saying lets say there are 3 records. 1- > 2 -> 3 and lets say 2 gets deleted will 1->3 or 1 will still point to 2 which on knowing its deleted will move to 3.

I would appreciate if someone who has faced such a similar problem is actual DB and know how it works please do help me understand.

  • The index fragmentation which affects performance is logical one, which means the ordering or data does not matches index key and so disk head has to move a lot to read the required data. For physical disk fragmentation the effect would not be much what would be required is more amount of I/O to read the blocks – Shanky Jun 8 '17 at 6:00
  • 1
    Disk fragmentation is also meaningless in modern SSD hard disks. I think that explanation of the interviewer would be wrong for e.g. the index structure in Oracle. Did you talk about a specific DBMS? – a_horse_with_no_name Jun 8 '17 at 6:56
  • No not a specific DBMS. Though he had worked on DB2 and sybase databases. So question could have been specific to those. But he did not mention any such specifics. – Aniket Thakur Jun 10 '17 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.