Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥

Database Clustered vs Non-Clustered Index

Database Clustered vs Non-Clustered Index

Learn how a Database works under the hood, and the differences and benefits of the Heap-Organized table vs an Index-Organized Table.
Understand how a Non-Clustered index is represented depending on the type of the storage, and the advantages or disadvantages compared to a Clustered Index.

https://youtu.be/Q4iYwXiy5ww

Matteo Bertozzi

August 29, 2023
Tweet

More Decks by Matteo Bertozzi

Other Decks in Programming

Transcript

  1. vs Index o r g a n i z e

    d t a b l e Heap o r g a n i z e d t a b l e
  2. Page 0
 Database Page Employee ID Country City Surname Page

    0 Tables are Organized in Pages Small size Blocks Easy to read, cache and evict
  3. Page 0
 Database Page Employee ID Country City Surname Page

    0 Block 8-16KiB Tables are Organized in Pages Small size Blocks Easy to read, cache and evict
  4. Page 0
 (format, stats, …) 0 483 29 Database Page

    Employee ID Country City Surname 344 US San Francisco Miller 483 Row 0 344 Miller San Francisco US Page 0
  5. Page 0
 (format, stats, …) 0 483 29 1 455

    28 Database Page Employee ID Country City Surname 459 344 EU US Stockholm San Francisco Andersson Miller 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU Page 0
  6. Page 0
 (format, stats, …) 0 483 29 1 455

    28 2 434 21 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 459 344 EU US Stockholm San Francisco Andersson Miller 826 EU Berlin Meyer 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU Page 0
  7. Page 0
 (format, stats, …) 0 483 29 1 455

    28 2 434 21 3 403 31 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 459 344 405 EU US US Stockholm San Francisco San Francisco Andersson Martinez Miller 826 EU Berlin Meyer 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US Page 0
  8. Page 0
 (format, stats, …) 0 483 29 1 455

    28 2 434 21 3 403 31 4 380 23 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 459 Berlin 310 344 405 EU US US EU Stockholm San Francisco San Francisco Andersson Martinez Miller Schmidt 826 EU Berlin Meyer 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU Page 0
  9. Page 0
 (format, stats, …) 0 483 29 1 455

    28 2 434 21 3 403 31 4 380 23 5 358 22 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 459 Berlin 310 344 405 517 UK EU US US EU London Stockholm San Francisco San Francisco Davies Andersson Martinez Miller Schmidt 826 EU Berlin Meyer 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU 358 Row 5 517 Davies London EU Page 0
  10. 6 332 26 Page 0
 (format, stats, …) 0 483

    29 1 455 28 2 434 21 3 403 31 4 380 23 5 358 22 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 235 459 Berlin Asplund EU 310 344 405 517 UK EU US US EU London Stockholm San Francisco San Francisco Stockholm Davies Andersson Martinez Miller Schmidt 826 EU Berlin Meyer 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU 358 Row 5 517 Davies London EU 332 Row 6 235 Asplund Stockholm EU Page 0
  11. 6 332 26 7 302 30 Page 0
 (format, stats,

    …) 0 483 29 1 455 28 2 434 21 3 403 31 4 380 23 5 358 22 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 235 459 111 Berlin Asplund EU 310 344 405 517 UK EU US US EU US London Stockholm San Francisco San Francisco Stockholm San Francisco Davies Andersson Martinez Miller Schmidt Jackson 826 EU Berlin Meyer 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU 358 Row 5 517 Davies London EU 332 Row 6 235 Asplund Stockholm EU 302 Row 7 111 Jackson San Francisco US Page 0
  12. 6 332 26 7 302 30 8 281 21 Page

    0
 (format, stats, …) 0 483 29 1 455 28 2 434 21 3 403 31 4 380 23 5 358 22 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 235 459 111 Berlin Asplund EU 310 344 405 517 UK EU US US EU US London Stockholm San Francisco San Francisco Stockholm San Francisco Davies Andersson Martinez Miller Schmidt Jackson 826 878 EU UK Berlin London Meyer Scott 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU 358 Row 5 517 Davies London EU 332 Row 6 235 Asplund Stockholm EU 302 Row 7 111 Jackson San Francisco US 281 Row 8 878 Scott London EU Page 0
  13. 6 332 26 7 302 30 8 281 21 9

    254 27 Page 0
 (format, stats, …) 0 483 29 1 455 28 2 434 21 3 403 31 4 380 23 5 358 22 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 235 459 111 Berlin Asplund EU 310 344 405 504 517 UK EU EU US US EU US London Stockholm Stockholm San Francisco San Francisco Stockholm San Francisco Davies Karlsson Andersson Martinez Miller Schmidt Jackson 826 878 EU UK Berlin London Meyer Scott 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU 358 Row 5 517 Davies London EU 332 Row 6 235 Asplund Stockholm EU 302 Row 7 111 Jackson San Francisco US 281 Row 8 878 Scott London EU 254 Row 9 504 Karlsson Stockholm EU Page 0
  14. 6 332 26 7 302 30 8 281 21 9

    254 27 Page 0
 (format, stats, …) 0 483 29 1 455 28 2 434 21 3 403 31 4 380 23 5 358 22 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 235 459 111 Berlin Asplund EU 310 344 405 504 517 UK EU EU US US EU US London Stockholm Stockholm San Francisco San Francisco Stockholm San Francisco Davies Karlsson Andersson Martinez Miller Schmidt Jackson 826 878 EU UK Berlin London Meyer Scott 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU 358 Row 5 517 Davies London EU 332 Row 6 235 Asplund Stockholm EU 302 Row 7 111 Jackson San Francisco US 281 Row 8 878 Scott London EU 254 Row 9 504 Karlsson Stockholm EU Page 0 Each database has it’s own “optimized” Page Layout
  15. 6 332 26 7 302 30 8 281 21 9

    254 27 Page 0
 (format, stats, …) 0 483 29 1 455 28 2 434 21 3 403 31 4 380 23 5 358 22 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 235 459 111 Berlin Asplund EU 310 344 405 504 517 UK EU EU US US EU US London Stockholm Stockholm San Francisco San Francisco Stockholm San Francisco Davies Karlsson Andersson Martinez Miller Schmidt Jackson 826 878 EU UK Berlin London Meyer Scott 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU 358 Row 5 517 Davies London EU 332 Row 6 235 Asplund Stockholm EU 302 Row 7 111 Jackson San Francisco US 281 Row 8 878 Scott London EU 254 Row 9 504 Karlsson Stockholm EU Page 0 “Row Data” Column values Encoded based on the Schema
  16. 6 332 26 7 302 30 8 281 21 9

    254 27 Page 0
 (format, stats, …) 0 483 29 1 455 28 2 434 21 3 403 31 4 380 23 5 358 22 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 235 459 111 Berlin Asplund EU 310 344 405 504 517 UK EU EU US US EU US London Stockholm Stockholm San Francisco San Francisco Stockholm San Francisco Davies Karlsson Andersson Martinez Miller Schmidt Jackson 826 878 EU UK Berlin London Meyer Scott 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU 358 Row 5 517 Davies London EU 332 Row 6 235 Asplund Stockholm EU 302 Row 7 111 Jackson San Francisco US 281 Row 8 878 Scott London EU 254 Row 9 504 Karlsson Stockholm EU Page 0 “Row Offset” For direct lookups Sometimes sorted by PK for Binary Search
  17. 6 332 26 7 302 30 8 281 21 9

    254 27 Page 0
 (format, stats, …) 0 483 29 1 455 28 2 434 21 3 403 31 4 380 23 5 358 22 434 Row 2 826 Meyer Berlin EU Database Page Employee ID Country City Surname 235 459 111 Berlin Asplund EU 310 344 405 504 517 UK EU EU US US EU US London Stockholm Stockholm San Francisco San Francisco Stockholm San Francisco Davies Karlsson Andersson Martinez Miller Schmidt Jackson 826 878 EU UK Berlin London Meyer Scott 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU 403 Row 3 405 Martinez San Francisco US 380 Row 4 310 Schmidt Berlin EU 358 Row 5 517 Davies London EU 332 Row 6 235 Asplund Stockholm EU 302 Row 7 111 Jackson San Francisco US 281 Row 8 878 Scott London EU 254 Row 9 504 Karlsson Stockholm EU Page 0 “Page Flags & Stats” Metadata useful for the Optimizer & Co
  18. Page 1
 (format, stats, …) Database Page Employee ID Country

    City Surname 235 459 111 Berlin Asplund EU 310 344 405 504 517 UK EU EU US US EU US London Stockholm Stockholm San Francisco San Francisco Stockholm San Francisco Davies Karlsson Andersson Martinez Miller Schmidt Jackson 826 878 EU UK Berlin London Meyer Scott Page 0 Page 1
  19. Page 1
 (format, stats, …) 0 483 29 Database Page

    Employee ID Country City Surname 235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson 826 878 EU UK Berlin London Meyer Scott 483 Row 0 344 Miller San Francisco US Page 0 Page 1
  20. Page 1
 (format, stats, …) 0 483 29 1 455

    28 Database Page Employee ID Country City Surname 105 235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU Page 0 Page 1
  21. … Page 1
 (format, stats, …) 0 483 29 1

    455 28 Database Page Employee ID Country City Surname 105 235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott 483 Row 0 344 Miller San Francisco US 455 Row 1 459 Andersson Stockholm EU Page 0 Page 1
  22. Employee ID Country City Surname 105 235 459 111 Berlin

    Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Heap-Organized Tables SELECT * FROM employee WHERE country = ‘US’; SELECT * FROM employee WHERE employee_id = ‘235’; Data is stored in no particular order
  23. Employee ID Country City Surname 105 235 459 111 Berlin

    Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Non-Clustered Index Heap-Organized Tables Heap-Organized Table Non-Clustered Index
  24. Non-Clustered Index Heap-Organized Tables CREATE INDEX index_name ON table_name (column_name_a,

    column_name_b); Heap-Organized Table Non-Clustered Index It contains a copy of the indexed columns Index Fields
  25. Non-Clustered Index Heap-Organized Tables CREATE INDEX index_name ON table_name (column_name_a,

    column_name_b); Heap-Organized Table Non-Clustered Index It contains a copy of the indexed columns Index Fields Block Offset
  26. Non-Clustered Index Heap-Organized Tables Index Fields Block Offset CREATE INDEX

    index_name ON table_name USING BTREE (column_name_a, column_name_b); Heap-Organized Table Non-Clustered Index Fast Range & Lookup Queries SELECT * FROM table_name WHERE foo > ‘xyz’; SELECT * FROM table_name WHERE foo < ‘xyz’; SELECT * FROM table_name WHERE foo LIKE ‘xy%’; SELECT * FROM table_name WHERE foo > ‘bbb’ AND foo < ‘eee’; SELECT * FROM table_name WHERE foo = ‘xyz’; A BTREE index can be used for column comparisons I n expressions that use the =, >, >=, <, <=, or LIKE with a prefix 'foo%'
  27. Non-Clustered Index Heap-Organized Tables Index Fields Block Offset CREATE INDEX

    index_name ON table_name USING HASH (column_name_a, column_name_b); Hash Indexes are used only for equality comparisons SELECT * FROM table_name WHERE foo = ‘xyz’;
  28. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Employee ID Page Offset 344 0 0 459 0 1 826 0 2 405 0 3 310 0 4 517 0 5 235 0 6 111 0 7 878 0 8 504 0 9 166 1 0 105 1 1 CREATE INDEX eid_index ON employee (employee_id);
  29. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Employee ID Page Offset 344 0 0 459 0 1 826 0 2 405 0 3 310 0 4 517 0 5 235 0 6 111 0 7 878 0 8 504 0 9 166 1 0 105 1 1 SELECT * FROM employee WHERE employee_id = 105; CREATE INDEX eid_index ON employee (employee_id);
  30. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Employee ID Page Offset 344 0 0 459 0 1 826 0 2 405 0 3 310 0 4 517 0 5 235 0 6 111 0 7 878 0 8 504 0 9 166 1 0 105 1 1 SELECT * FROM employee WHERE employee_id = 105; CREATE INDEX eid_index ON employee (employee_id);
  31. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Employee ID Page Offset 344 0 0 459 0 1 826 0 2 405 0 3 310 0 4 517 0 5 235 0 6 111 0 7 878 0 8 504 0 9 166 1 0 105 1 1 SELECT * FROM employee WHERE employee_id = 105; CREATE INDEX eid_index ON employee (employee_id);
  32. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Employee ID Page Offset 344 0 0 459 0 1 826 0 2 405 0 3 310 0 4 517 0 5 235 0 6 111 0 7 878 0 8 504 0 9 166 1 0 105 1 1 SELECT * FROM employee WHERE employee_id = 105; CREATE INDEX eid_index ON employee (employee_id);
  33. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Employee ID Page Offset 344 0 0 459 0 1 826 0 2 405 0 3 310 0 4 517 0 5 235 0 6 111 0 7 878 0 8 504 0 9 166 1 0 105 1 1 SELECT * FROM employee WHERE employee_id = 105; CREATE INDEX eid_index ON employee (employee_id);
  34. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Employee ID Page Offset 344 0 0 459 0 1 826 0 2 405 0 3 310 0 4 517 0 5 235 0 6 111 0 7 878 0 8 504 0 9 166 1 0 105 1 1 SELECT * FROM employee WHERE employee_id = 105; CREATE INDEX eid_index ON employee (employee_id);
  35. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Page Offset Stockholm 0 1 Berlin 0 2 Berlin 0 4 London 0 5 Stockholm 0 6 San Francisco 0 7 Stockholm 0 9 London 1 0 CREATE INDEX cities_index ON employee (country, city); San Francisco 0 0 Berlin 1 1 San Francisco 0 3 London 0 8 US UK EU EU City Country EU EU EU EU UK UK US US
  36. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Page Offset Stockholm 0 1 Berlin 0 2 Berlin 0 4 London 0 5 Stockholm 0 6 San Francisco 0 7 Stockholm 0 9 London 1 0 SELECT * FROM employee WHERE country = ‘UK’; CREATE INDEX cities_index ON employee (country, city); San Francisco 0 0 Berlin 1 1 San Francisco 0 3 London 0 8 US UK EU EU City Country EU EU EU EU UK UK US US
  37. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Page Offset Stockholm 0 1 Berlin 0 2 Berlin 0 4 London 0 5 Stockholm 0 6 San Francisco 0 7 Stockholm 0 9 London 1 0 SELECT * FROM employee WHERE country = ‘UK’; CREATE INDEX cities_index ON employee (country, city); San Francisco 0 0 Berlin 1 1 San Francisco 0 3 London 0 8 US UK EU EU City Country EU EU EU EU UK UK US US
  38. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Page Offset Stockholm 0 1 Berlin 0 2 Berlin 0 4 London 0 5 Stockholm 0 6 San Francisco 0 7 Stockholm 0 9 London 1 0 SELECT * FROM employee WHERE country = ‘UK’; CREATE INDEX cities_index ON employee (country, city); San Francisco 0 0 Berlin 1 1 San Francisco 0 3 London 0 8 US UK EU EU City Country EU EU EU EU UK UK US US
  39. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Page Offset Stockholm 0 1 Berlin 0 2 Berlin 0 4 London 0 5 Stockholm 0 6 San Francisco 0 7 Stockholm 0 9 London 1 0 SELECT * FROM employee WHERE country = ‘UK’; CREATE INDEX cities_index ON employee (country, city); San Francisco 0 0 Berlin 1 1 San Francisco 0 3 London 0 8 US UK EU EU City Country EU EU EU EU UK UK US US
  40. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Page Offset Stockholm 0 1 Berlin 0 2 Berlin 0 4 London 0 5 Stockholm 0 6 San Francisco 0 7 Stockholm 0 9 London 1 0 San Francisco 0 0 Berlin 1 1 San Francisco 0 3 London 0 8 US UK EU EU City Country EU EU EU EU UK UK US US SELECT * FROM employee WHERE country = ‘EU’ AND city = ‘Stockholm’; CREATE INDEX cities_index ON employee (country, city);
  41. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Page Offset Stockholm 0 1 Berlin 0 2 Berlin 0 4 London 0 5 Stockholm 0 6 San Francisco 0 7 Stockholm 0 9 London 1 0 San Francisco 0 0 Berlin 1 1 San Francisco 0 3 London 0 8 US UK EU EU City Country EU EU EU EU UK UK US US SELECT * FROM employee WHERE country = ‘EU’ AND city = ‘Stockholm’; CREATE INDEX cities_index ON employee (country, city);
  42. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Page Offset Stockholm 0 1 Berlin 0 2 Berlin 0 4 London 0 5 Stockholm 0 6 San Francisco 0 7 Stockholm 0 9 London 1 0 San Francisco 0 0 Berlin 1 1 San Francisco 0 3 London 0 8 US UK EU EU City Country EU EU EU EU UK UK US US SELECT * FROM employee WHERE country = ‘EU’ AND city = ‘Stockholm’; CREATE INDEX cities_index ON employee (country, city);
  43. Non-Clustered Index Heap-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 344 405 504 517 UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott Page Offset Stockholm 0 1 Berlin 0 2 Berlin 0 4 London 0 5 Stockholm 0 6 San Francisco 0 7 Stockholm 0 9 London 1 0 San Francisco 0 0 Berlin 1 1 San Francisco 0 3 London 0 8 US UK EU EU City Country EU EU EU EU UK UK US US SELECT * FROM employee WHERE country = ‘EU’ AND city = ‘Stockholm’; CREATE INDEX cities_index ON employee (country, city);
  44. Clustered Index PostgreSQL MySQL SQL Server Oracle CREATE TABLE (

    … PRIMARY KEY CLUSTERED, ); CREATE TABLE ( …PRIMARY KEY ) ORGANIZATION INDEX; InnoDB CREATE TABLE ( PRIMARY KEY, ); CREATE INDEX idx_name ON my_table (col_a, col_b); CLUSTER my_table USING idx_name; When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation when the table is updated, the changes are not clustered. Data is physical stored order based on the clustered key (Primary Key) Microsoft Index-Organized Tables (IOT) have primary key data and non-key columns data stored within the same B+Tree structure Data can be retrieved quickly based on the clustered key, if the query uses the indexed columns.
  45. Clustered Index Index-Organized Tables Data is physical stored order based

    on the clustered key (Primary Key) Data can be retrieved quickly based on the clustered key, if the query uses the indexed columns. Index-Organized Tables (IOT) have primary key data and non-key columns data stored within the same B+Tree structure
  46. Clustered Index Index-Organized Tables Row Data Data is physical stored

    order based on the clustered key (Primary Key) Data can be retrieved quickly based on the clustered key, if the query uses the indexed columns. Index-Organized Tables (IOT) have primary key data and non-key columns data stored within the same B+Tree structure
  47. Clustered Index Index-Organized Tables Employee ID Country City Surname 105

    235 459 111 Berlin Asplund EU 310 166 405 504 517 UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez 344 US San Francisco Miller Schmidt Stewart Jackson Schneider 826 878 EU UK Berlin London Meyer Scott SELECT * FROM employee WHERE country = ‘UK’ AND city = ‘London’; CREATE TABLE employee ( PRIMARY KEY (country, city, surname, employee_id) ); SELECT * FROM employee WHERE country = ‘UK’ AND city = ‘London’ AND surname LIKE ’S%’; SELECT * FROM employee WHERE country = ‘EU’;
  48. Non-Clustered Index Index-Organized Tables Row Data CREATE INDEX index_name ON

    table_name USING BTREE (column_name_a, column_name_b); Non-Clustered Index Clustered Index
  49. Index Fields Non-Clustered Index Index-Organized Tables Row Data CREATE INDEX

    index_name ON table_name USING BTREE (column_name_a, column_name_b); Non-Clustered Index Clustered Index
  50. PK Fields Index Fields Non-Clustered Index Index-Organized Tables Row Data

    CREATE INDEX index_name ON table_name USING BTREE (column_name_a, column_name_b); Non-Clustered Index Clustered Index
  51. PK Fields Index Fields Non-Clustered Index Index-Organized Tables Row Data

    CREATE INDEX index_name ON table_name USING HASH (column_name_a, column_name_b); Non-Clustered Index Clustered Index
  52. Employee ID Country City 826 459 235 111 Berlin EU

    310 878 344 504 166 UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Surname Asplund Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider Meyer Scott Role HR Financial Operation Support Marketing IT Operation Support Operation HR Support IT Primary Key Non-Clustered Index Index-Organized Tables Employee ID Country City 826 459 235 111 Berlin EU 310 344 166 UK EU US EU US EU London Stockholm San Francisco Stockholm San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Role HR Financial Support Marketing IT Operation Operation HR Support IT 504 EU Stockholm Operation 878 UK London Support CREATE INDEX role_index ON employee (role);
  53. Employee ID Country City 826 459 235 111 Berlin EU

    310 878 344 504 166 UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Surname Asplund Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider Meyer Scott Role HR Financial Operation Support Marketing IT Operation Support Operation HR Support IT Primary Key Non-Clustered Index Index-Organized Tables Employee ID Country City 826 459 235 111 Berlin EU 310 344 166 UK EU US EU US EU London Stockholm San Francisco Stockholm San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Role HR Financial Support Marketing IT Operation Operation HR Support IT 504 EU Stockholm Operation 878 UK London Support CREATE INDEX role_index ON employee (role); SELECT * FROM employee WHERE role = ‘Operation’;
  54. Employee ID Country City 826 459 235 111 Berlin EU

    310 878 344 504 166 UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Surname Asplund Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider Meyer Scott Role HR Financial Operation Support Marketing IT Operation Support Operation HR Support IT Primary Key Non-Clustered Index Index-Organized Tables Employee ID Country City 826 459 235 111 Berlin EU 310 344 166 UK EU US EU US EU London Stockholm San Francisco Stockholm San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Role HR Financial Support Marketing IT Operation Operation HR Support IT 504 EU Stockholm Operation 878 UK London Support CREATE INDEX role_index ON employee (role); SELECT * FROM employee WHERE role = ‘Operation’;
  55. Employee ID Country City 826 459 235 111 Berlin EU

    310 878 344 504 166 UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Surname Asplund Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider Meyer Scott Role HR Financial Operation Support Marketing IT Operation Support Operation HR Support IT Primary Key Non-Clustered Index Index-Organized Tables Employee ID Country City 826 459 235 111 Berlin EU 310 344 166 UK EU US EU US EU London Stockholm San Francisco Stockholm San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Role HR Financial Support Marketing IT Operation Operation HR Support IT 504 EU Stockholm Operation 878 UK London Support CREATE INDEX role_index ON employee (role); SELECT * FROM employee WHERE role = ‘Operation’;
  56. Employee ID Country City 826 459 235 111 Berlin EU

    310 878 344 504 166 UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Surname Asplund Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider Meyer Scott Role HR Financial Operation Support Marketing IT Operation Support Operation HR Support IT Primary Key Non-Clustered Index Index-Organized Tables Employee ID Country City 826 459 235 111 Berlin EU 310 344 166 UK EU US EU US EU London Stockholm San Francisco Stockholm San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Role HR Financial Support Marketing IT Operation Operation HR Support IT 504 EU Stockholm Operation 878 UK London Support CREATE INDEX role_index ON employee (role); SELECT * FROM employee WHERE role = ‘Operation’;
  57. Employee ID Country City 826 459 235 111 Berlin EU

    310 878 344 504 166 UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Surname Asplund Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider Meyer Scott Role HR Financial Operation Support Marketing IT Operation Support Operation HR Support IT Primary Key Non-Clustered Index Index-Organized Tables Employee ID 105 235 459 111 310 166 405 504 517 344 826 878 Country City Berlin EU UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin US San Francisco EU UK Berlin London CREATE INDEX id_index ON employee (employee_id);
  58. Employee ID Country City 826 459 235 111 Berlin EU

    310 878 344 504 166 UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Surname Asplund Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider Meyer Scott Role HR Financial Operation Support Marketing IT Operation Support Operation HR Support IT Primary Key Non-Clustered Index Index-Organized Tables Employee ID 105 235 459 111 310 166 405 504 517 344 826 878 Country City Berlin EU UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin US San Francisco EU UK Berlin London CREATE INDEX id_index ON employee (employee_id); SELECT * FROM employee WHERE employee_id = 310;
  59. Employee ID Country City 826 459 235 111 Berlin EU

    310 878 344 504 166 UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin 405 US San Francisco 105 517 EU UK Berlin London Surname Asplund Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider Meyer Scott Role HR Financial Operation Support Marketing IT Operation Support Operation HR Support IT Primary Key Non-Clustered Index Index-Organized Tables Employee ID 105 235 459 111 310 166 405 504 517 344 826 878 Country City Berlin EU UK EU EU US EU UK US EU London Stockholm Stockholm San Francisco Stockholm London San Francisco Berlin US San Francisco EU UK Berlin London CREATE INDEX id_index ON employee (employee_id); SELECT * FROM employee WHERE employee_id = 310;
  60. RowId Tables Role Country City Surname Support Support HR Operation

    Berlin Asplund EU Operation Financial Marketing IT Operation IT UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider HR Support EU UK Berlin London Meyer Scott RowId 1 2 3 4 5 6 7 8 9 10 11 12 Index-Organized Tables order based on RowId
  61. RowId Tables Role Country City Surname Support Support HR Operation

    Berlin Asplund EU Operation Financial Marketing IT Operation IT UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider HR Support EU UK Berlin London Meyer Scott RowId 1 2 3 4 5 6 7 8 9 10 11 12 Index-Organized Tables order based on RowId AUTOINCREMENT
  62. RowId Tables Role Country City Surname Support Support HR Operation

    Berlin Asplund EU Operation Financial Marketing IT Operation IT UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider HR Support EU UK Berlin London Meyer Scott RowId 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE employee (country, city, surname, role); SELECT rowid, * FROM employee; Index-Organized Tables order based on RowId SQLite (Default) AUTOINCREMENT
  63. RowId Tables Role Country City Surname Support Support HR Operation

    Berlin Asplund EU Operation Financial Marketing IT Operation IT UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider HR Support EU UK Berlin London Meyer Scott RowId 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE employee (country, city, surname, role); SELECT rowid, * FROM employee; Index-Organized Tables order based on RowId SQLite (Default) CREATE TABLE my_table (…) WITHOUT ROWID; AUTOINCREMENT SQLite (With a Proper Clustered Index)
  64. RowId Tables Role Country City Surname Support Support HR Operation

    Berlin Asplund EU Operation Financial Marketing IT Operation IT UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider HR Support EU UK Berlin London Meyer Scott RowId 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE employee (country, city, surname, role); SELECT rowid, * FROM employee; Index-Organized Tables order based on RowId SQLite (Default) CREATE TABLE my_table (…) WITHOUT ROWID; AUTOINCREMENT SQLite (With a Proper Clustered Index) (Insertion Order)
  65. RowId 2 11 7 1 5 8 3 4 12

    6 Role HR Financial Support Marketing IT Operation Operation HR Support IT 10 Operation 9 Support Role Country City Surname Support Support HR Operation Berlin Asplund EU Operation Financial Marketing IT Operation IT UK EU EU US US EU UK US EU London Stockholm Stockholm San Francisco San Francisco Stockholm London San Francisco Berlin Davies Karlsson Andersson Martinez Miller Schmidt Stewart Jackson Schneider HR Support EU UK Berlin London Meyer Scott RowId 1 2 3 4 5 6 7 8 9 10 11 12 CREATE INDEX role_index ON employee (role); SELECT * FROM employee WHERE role = ‘Operation’; Non-Clustered Index RowId Tables Reduced Index size (RowId vs Primary Key) Less Fragmentation (B+Tree Append Only) Same pitfalls of Heap-Organized Tables Too many Disk I/O from a Non-Clustered Index for range queries or sorting.
  66. Fragmentation Heap-Organized Tables Page 0 Row 0 Row 1 Row

    2 Row 3 Row 4 Row 5 Row 6 New rows are added without any ordering constraint
  67. Fragmentation Heap-Organized Tables Page 0 Row 0 Row 1 Row

    2 Row 3 Row 4 Row 5 Row 6 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not.
  68. Fragmentation Heap-Organized Tables Page 0 Row 0 Row 1 Row

    3 Row 4 Row 5 Row 6 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not.
  69. Fragmentation Heap-Organized Tables Page 0 Row 0 Row 1 Row

    3 Row 4 Row 5 Row 6 Row 2 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor)
  70. Fragmentation Heap-Organized Tables Page 0 Row 0 Row 1 Row

    3 Row 4 Row 5 Row 6 Row 2 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor)
  71. Fragmentation Heap-Organized Tables Page 0 Row 0 Row 1 Row

    3 Row 4 Row 5 Row 6 Row 2 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor)
  72. Fragmentation Heap-Organized Tables Page 0 Row 0 Row 1 Row

    3 Row 4 Row 5 Row 6 Row 2 Page 1 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor) But if the row is too large, a new Page is created
  73. Page 1 Fragmentation Heap-Organized Tables Page 0 Row 0 Row

    1 Row 3 Row 4 Row 5 Row 6 Row 2 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor) But if the row is too large, a new Page is created
  74. Page 1 Fragmentation Heap-Organized Tables Page 0 Row 0 Row

    1 Row 3 Row 4 Row 5 Row 6 Row 2 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor) But if the row is too large, a new Page is created
  75. Page 1 Fragmentation Heap-Organized Tables Page 0 Row 0 Row

    1 Row 3 Row 4 Row 5 Row 6 Row 2 Row 7 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor) But if the row is too large, a new Page is created
  76. Page 1 Fragmentation Heap-Organized Tables Page 0 Row 0 Row

    1 Row 3 Row 4 Row 5 Row 6 Row 2 Row 7 Row 8 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor) But if the row is too large, a new Page is created
  77. Page 1 Fragmentation Heap-Organized Tables Page 0 Row 0 Row

    1 Row 3 Row 4 Row 5 Row 6 Row 2 Row 7 Row 8 Row 9 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor) But if the row is too large, a new Page is created
  78. Page 1 Fragmentation Heap-Organized Tables Page 0 Row 0 Row

    1 Row 3 Row 4 Row 5 Row 6 Row 2 Row 7 Row 8 Row 9 Row 10 Row 11 New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor) But if the row is too large, a new Page is created
  79. Page 1 Fragmentation Heap-Organized Tables Page 0 Row 0 Row

    3 Row 4 Row 5 Row 2 Row 7 Row 10 Row 11 unused spaces leads to more I/O, compared to a “more compact” table More I/O generally means: Slower query performance More memory usage (worse cache usage) More costs (Cloud providers bill based on I/O usage) New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor) But if the row is too large, a new Page is created Fragmentation
  80. Page 1 Fragmentation Heap-Organized Tables Page 0 Row 0 Row

    3 Row 4 Row 5 Row 2 Row 7 Row 10 Row 11 unused spaces leads to more I/O, compared to a “more compact” table More I/O generally means: Slower query performance More memory usage (worse cache usage) More costs (Cloud providers bill based on I/O usage) New rows are added without any ordering constraint If Rows are variable in size (e.g. VARCHAR) An update may find space in the same table, or not. Some space is always kept free for this case (Page Fill Factor) But if the row is too large, a new Page is created Fragmentation MySQL: OPTIMIZE TABLE SQL Server: ALTER TABLE REBUILD Oracle: ALTER TABLE CLEANUP PostgreSQL: VACUUM
  81. Fragmentation Index-Organized Tables Index-Organized Tables are implemented using a B+Tree

    or a variant. (The balancing behaviors are implementation dependent)
  82. Fragmentation Index-Organized Tables Index-Organized Tables are implemented using a B+Tree

    or a variant. (The balancing behaviors are implementation dependent)
  83. Fragmentation Index-Organized Tables Row C Index-Organized Tables are implemented using

    a B+Tree or a variant. (The balancing behaviors are implementation dependent)
  84. Fragmentation Index-Organized Tables Row C Row F Index-Organized Tables are

    implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent)
  85. Fragmentation Index-Organized Tables Row A Row C Row F Index-Organized

    Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted
  86. Fragmentation Index-Organized Tables Row A Row C Row F Row

    H Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted
  87. Fragmentation Index-Organized Tables Row A Row C Row F Row

    F Row H Row H Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted
  88. Fragmentation Index-Organized Tables Row A Row C Row F Row

    F Row H Row H Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Leaf Pages Contains the row with columns data
  89. Fragmentation Index-Organized Tables Row A Row C Row F Row

    F Row H Row H Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Leaf Pages Contains the row with columns data Root & Internal Pages Contains the key and a pointer to the nodes of the level below
  90. Fragmentation Index-Organized Tables Row A Row C Row D Row

    D Row F Row H Row H Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted
  91. Fragmentation Index-Organized Tables Row A Row C Row D Row

    D Row F Row I Row H Row I Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted
  92. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row H Row I Row F Row I Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted
  93. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted
  94. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted
  95. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row J Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted
  96. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row J Row I Row J Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted
  97. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row L Row I Row L Row K Row L Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted
  98. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row L Row O Row I Row O Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around.
  99. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row L Row O Row R Row I Row R Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around.
  100. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row L Row O Row R Row U Row I Row R Row U Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around.
  101. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row L Row O Row R Row U Row X Row I Row R Row X Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around.
  102. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row Y Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around.
  103. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row Y Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around.
  104. Fragmentation Index-Organized Tables Row A Row B Row C Row

    C Row D Row F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row Y Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around.
  105. Fragmentation Index-Organized Tables Row A Row B Row B Row

    D Row F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row Y Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around.
  106. Fragmentation Index-Organized Tables Row A Row B Row B Row

    D Row F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row Y Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around.
  107. Fragmentation Index-Organized Tables Row A Row B Row B Row

    F Row G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row Y Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around. Removes may merge and remove “half-empty” nodes
  108. Fragmentation Index-Organized Tables Row A Row B Row B Row

    G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row Y Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around. Removes may merge and remove “half-empty” nodes
  109. Fragmentation Index-Organized Tables Row A Row B Row B Row

    G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row Y Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around. Removes may merge and remove “half-empty” nodes
  110. Fragmentation Index-Organized Tables Row A Row B Row B Row

    G Row H Row F Row I Row I Row J Row K Row L Row M Row N Row O Row P Row Q Row R Row S Row T Row U Row V Row W Row X Row Y Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around. Removes may merge and remove “half-empty” nodes
  111. Fragmentation Index-Organized Tables Row A Row B Row B Row

    H Row F Row I Row I Row J Row L Row N Row O Row P Row R Row T Row U Row V Row X Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around. Removes may merge and remove “half-empty” nodes Some sort of fragmentation is inevitable
  112. Fragmentation Index-Organized Tables Row A Row B Row B Row

    H Row F Row I Row I Row J Row L Row N Row O Row P Row R Row T Row U Row V Row X Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around. Removes may merge and remove “half-empty” nodes Some sort of fragmentation is inevitable
  113. Fragmentation Index-Organized Tables Row A Row B Row B Row

    H Row F Row I Row I Row J Row L Row N Row O Row P Row R Row T Row U Row V Row X Row Z Row L Row O Row R Row U Row X Row Z Row I Row R Row Z Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent) Data is always sorted Random Order insertions causes a lot of “row movement” to keep the data sorted Append insertions are fast, data is already sorted No need to move things around. Removes may merge and remove “half-empty” nodes Some sort of fragmentation is inevitable Each Database provides tools to optimize the table and rebuild the indexes.
  114. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, )
  115. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values
  116. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test If the value is small, it may be inlined.
  117. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test Row 1 Db Fun 1693083025 If the value is small, it may be inlined.
  118. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test Row 1 Db Fun 1693083025 ?a|E??ڭ/aYc8?X’H\siBIn7 Q?]?Kd???Z???ua?:?҇x??F??u??! ?5????%?&??d??g?D??ΖD?6????@ u?B??ř?M<?L}?|?? ̇??K].?Ε?%{ ? H???B?n5?F<??g?R?\???xF?<??S ?”?d????I?Ӣ?lYB?W?Fg?c?n0?? h,?? V#R{e??Ujp??X??/Cѯ?@ M@G?8??\ʨ?’L? f????R?wz?$??,t"'??-i???㓔 If the value is large, it is written externally, and the column has a reference to it. If the value is small, it may be inlined.
  119. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test Row 1 Db Fun 1693083025 ?a|E??ڭ/aYc8?X’H\siBIn7 Q?]?Kd???Z???ua?:?҇x??F??u??! ?5????%?&??d??g?D??ΖD?6????@ u?B??ř?M<?L}?|?? ̇??K].?Ε?%{ ? H???B?n5?F<??g?R?\???xF?<??S ?”?d????I?Ӣ?lYB?W?Fg?c?n0?? h,?? V#R{e??Ujp??X??/Cѯ?@ M@G?8??\ʨ?’L? f????R?wz?$??,t"'??-i???㓔 If the value is large, it is written externally, and the column has a reference to it. If the value is small, it may be inlined.
  120. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test Row 1 Db Fun 1693083025 Row 2 Blobs are Slow 1693171550 ?a|E??ڭ/aYc8?X’H\siBIn7 Q?]?Kd???Z???ua?:?҇x??F??u??! ?5????%?&??d??g?D??ΖD?6????@ u?B??ř?M<?L}?|?? ̇??K].?Ε?%{ ? H???B?n5?F<??g?R?\???xF?<??S ?”?d????I?Ӣ?lYB?W?Fg?c?n0?? h,?? V#R{e??Ujp??X??/Cѯ?@ M@G?8??\ʨ?’L? f????R?wz?$??,t"'??-i???㓔 6?2?X¼2k?#?un???Sۓut?+n/܆? (st?/??v6?k???^~???5??? XpGy??D???vO6?3Y")U???? 1[??t??~?c?i? If the value is large, it is written externally, and the column has a reference to it. If the value is small, it may be inlined.
  121. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test Row 1 Db Fun 1693083025 Row 2 Blobs are Slow 1693171550 ?a|E??ڭ/aYc8?X’H\siBIn7 Q?]?Kd???Z???ua?:?҇x??F??u??! ?5????%?&??d??g?D??ΖD?6????@ u?B??ř?M<?L}?|?? ̇??K].?Ε?%{ ? H???B?n5?F<??g?R?\???xF?<??S ?”?d????I?Ӣ?lYB?W?Fg?c?n0?? h,?? V#R{e??Ujp??X??/Cѯ?@ M@G?8??\ʨ?’L? f????R?wz?$??,t"'??-i???㓔 6?2?X¼2k?#?un???Sۓut?+n/܆? (st?/??v6?k???^~???5??? XpGy??D???vO6?3Y")U???? 1[??t??~?c?i? If the value is large, it is written externally, and the column has a reference to it. If the value is small, it may be inlined. SELECT * must perform an “extra read” to lookup the large values.
  122. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test Row 1 Db Fun 1693083025 Row 2 Blobs are Slow 1693171550 ?a|E??ڭ/aYc8?X’H\siBIn7 Q?]?Kd???Z???ua?:?҇x??F??u??! ?5????%?&??d??g?D??ΖD?6????@ u?B??ř?M<?L}?|?? ̇??K].?Ε?%{ ? H???B?n5?F<??g?R?\???xF?<??S ?”?d????I?Ӣ?lYB?W?Fg?c?n0?? h,?? V#R{e??Ujp??X??/Cѯ?@ M@G?8??\ʨ?’L? f????R?wz?$??,t"'??-i???㓔 6?2?X¼2k?#?un???Sۓut?+n/܆? (st?/??v6?k???^~???5??? XpGy??D???vO6?3Y")U???? 1[??t??~?c?i? If the value is large, it is written externally, and the column has a reference to it. If the value is small, it may be inlined. Leaf nodes (Rows Data) SELECT * must perform an “extra read” to lookup the large values.
  123. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test Row 1 Db Fun 1693083025 Row 2 Blobs are Slow 1693171550 ?a|E??ڭ/aYc8?X’H\siBIn7 Q?]?Kd???Z???ua?:?҇x??F??u??! ?5????%?&??d??g?D??ΖD?6????@ u?B??ř?M<?L}?|?? ̇??K].?Ε?%{ ? H???B?n5?F<??g?R?\???xF?<??S ?”?d????I?Ӣ?lYB?W?Fg?c?n0?? h,?? V#R{e??Ujp??X??/Cѯ?@ M@G?8??\ʨ?’L? f????R?wz?$??,t"'??-i???㓔 6?2?X¼2k?#?un???Sۓut?+n/܆? (st?/??v6?k???^~???5??? XpGy??D???vO6?3Y")U???? 1[??t??~?c?i? If the value is large, it is written externally, and the column has a reference to it. If the value is small, it may be inlined. Leaf nodes (Rows Data) TEXTs & BLOBs SELECT * must perform an “extra read” to lookup the large values.
  124. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test Row 1 Db Fun 1693083025 Row 2 Blobs are Slow 1693171550 ?a|E??ڭ/aYc8?X’H\siBIn7 Q?]?Kd???Z???ua?:?҇x??F??u??! ?5????%?&??d??g?D??ΖD?6????@ u?B??ř?M<?L}?|?? ̇??K].?Ε?%{ ? H???B?n5?F<??g?R?\???xF?<??S ?”?d????I?Ӣ?lYB?W?Fg?c?n0?? h,?? V#R{e??Ujp??X??/Cѯ?@ M@G?8??\ʨ?’L? f????R?wz?$??,t"'??-i???㓔 6?2?X¼2k?#?un???Sۓut?+n/܆? (st?/??v6?k???^~???5??? XpGy??D???vO6?3Y")U???? 1[??t??~?c?i? If the value is large, it is written externally, and the column has a reference to it. If the value is small, it may be inlined. Leaf nodes (Rows Data) TEXTs & BLOBs SELECT * must perform an “extra read” to lookup the large values. Slowing down query performance.
  125. Large Rows BLOB, TEXT, … If a row does not

    fit in a page… Page X TABLE posts ( post_date TIMESTAMP, post_title VARCHAR(128), post_content LONGTEXT, ) TEXT & BLOB Used for storing “large” values Row 0 Hello World 1693116445 This is a Test Row 1 Db Fun 1693083025 Row 2 Blobs are Slow 1693171550 ?a|E??ڭ/aYc8?X’H\siBIn7 Q?]?Kd???Z???ua?:?҇x??F??u??! ?5????%?&??d??g?D??ΖD?6????@ u?B??ř?M<?L}?|?? ̇??K].?Ε?%{ ? H???B?n5?F<??g?R?\???xF?<??S ?”?d????I?Ӣ?lYB?W?Fg?c?n0?? h,?? V#R{e??Ujp??X??/Cѯ?@ M@G?8??\ʨ?’L? f????R?wz?$??,t"'??-i???㓔 6?2?X¼2k?#?un???Sۓut?+n/܆? (st?/??v6?k???^~???5??? XpGy??D???vO6?3Y")U???? 1[??t??~?c?i? If the value is large, it is written externally, and the column has a reference to it. If the value is small, it may be inlined. Leaf nodes (Rows Data) TEXTs & BLOBs SELECT * must perform an “extra read” to lookup the large values. Slowing down query performance. Specify only the fields you need to avoid the extra lookup.
  126. Understanding what the storage does under the hood is crucial

    for optimizing performance and reduce costs
  127. Heap-Organized Tables Index Fields Page Offset Heap-Organized Tables Non-Clustered Index

    Row Data Data is stored in no particular order. Without a matching non-clustered index, the entire table must be read (full table scan) to find any row. A Non-Clustered Index is a separate structure from the Data Table. It contains a copy of the indexed columns. Individual rows are identified by reference (page block, row offset) takes less space in non-clustered index than most primary keys. If the selected columns are part of the index, no extra lookup the the heap table has to be performed. Heap Tables excel at bulk insert operations since they don't require maintaining index order during insertion. Good for Archival or Staging Tables. Do not use when data is frequently sorted or grouped. Too many Disk I/O, Slow Performance, and the Cloud Provider bills everything
  128. Index-Organized Tables Non-Clustered Index Index Fields Primary Key Clustered Index

    Index-Organized Tables Row Data Data is physical stored order based on the clustered key (Primary Key) Data can be retrieved quickly based on the clustered key, if the query uses the indexed columns. (Range & Lookup queries) Choose the Clustered Index based on the most frequent access pattern. A Non-Clustered Index is a separate structure from the Data Table. It contains a copy of the indexed columns. Individual rows are identified by the Primary Key takes more space (depending on the PK) compared to the block pointer of heap-organized tables. For Insert-heavy workloads, consider a Heap-organized table or use a Sequential RowId or a Time-based Id to reduce fragmentation.
  129. Index Fields Page Offset Heap-Organized Tables Non-Clustered Index Row Data

    Non-Clustered Index Index Fields Primary Key Clustered Index Index-Organized Tables Row Data Heap-Organized Tables Index-Organized Tables Clustered Index Non-Clustered Index Data Pages Page Fragmentation BLOBs