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

Transcript

1. Index Heap vs Organized Tables Clustered Non-Clustered vs Index Database
2. Let’s dive into
3. Clustered I n d e x vs Non-Clustered I n d e x
4. 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
5. RowID T a b l e s
6. and More…
7. Page 0
 Database Page Employee ID Country City Surname Page 0 Tables are Organized in Pages
8. 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
9. 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
10. 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
11. 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
12. 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
13. 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
14. 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
15. 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
16. 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
17. 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
18. 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
19. 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
20. 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
21. 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
22. 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
23. 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
24. 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
25. 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
26. 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
27. … 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
28. 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
29. 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
30. 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
31. 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
32. 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
33. 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%'
34. 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’;
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 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);
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 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);
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 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);
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 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);
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 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);
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 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);
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 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);
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 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
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 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
44. 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
45. 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
46. 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
47. 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);
48. 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);
49. 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);
50. 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);
51. 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.
52. 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
53. 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
54. 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’;
55. 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
56. 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
57. 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
58. 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
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 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);
60. 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’;
61. 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’;
62. 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’;
63. 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’;
64. 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);
65. 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;
66. 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;
67. 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
68. 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
69. 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
70. 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)
71. 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)
72. 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.
73. Fragmentation What is Page Fragmentation?
74. 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
75. 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.
76. 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.
77. 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)
78. 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)
79. 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)
80. 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
81. 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
82. 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
83. 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
84. 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
85. 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
86. 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
87. 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
88. 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
89. Fragmentation Index-Organized Tables Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent)
90. Fragmentation Index-Organized Tables Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent)
91. Fragmentation Index-Organized Tables Row C Index-Organized Tables are implemented using a B+Tree or a variant. (The balancing behaviors are implementation dependent)
92. 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)
93. 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
94. 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
95. 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
96. 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
97. 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
98. 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
99. 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
100. 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
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 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
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 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
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 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
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 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
105. 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
106. 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.
107. 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.
108. 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.
109. 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.
110. 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.
111. 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.
112. 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.
113. 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.
114. 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.
115. 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
116. 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
117. 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
118. 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
119. 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
120. 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
121. 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.
122. Large Rows BLOB, TEXT, … If a row does not fit in a page… Page X
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, )
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
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 If the value is small, it may be inlined.
126. 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.
127. 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.
128. 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.
129. 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.
130. 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.
131. 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.
132. 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.
133. 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.
134. 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.
135. Understanding what the storage does under the hood is crucial for optimizing performance and reduce costs
136. 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
137. 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.
138. 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