1
00:00:00,090 --> 00:00:06,080
‫Welcome to another Q&A session for the Fundamentals of Database Engineering Course.

2
00:00:06,090 --> 00:00:19,590
‫Today we have a question from Shiva Ashu Switching a page versus a row in an index high in lecture 1616

3
00:00:19,590 --> 00:00:22,140
‫where you explain how indexes and tables are stored.

4
00:00:22,140 --> 00:00:30,960
‫You mentioned that we that when we execute a SQL query behind the scenes, the whole page is fetched

5
00:00:30,960 --> 00:00:35,670
‫and only the row needed is filtered and sent to the user.

6
00:00:35,670 --> 00:00:36,480
‫That is correct.

7
00:00:36,480 --> 00:00:37,500
‫I have two questions.

8
00:00:37,500 --> 00:00:39,930
‫Why does it get the whole page?

9
00:00:39,930 --> 00:00:41,670
‫Why not the row?

10
00:00:41,670 --> 00:00:47,970
‫Since the DB already knows the row location, that is actually not a correct statement.

11
00:00:47,970 --> 00:00:51,360
‫The database does not know where the row is in the database.

12
00:00:51,360 --> 00:00:53,730
‫In the page it has to read it.

13
00:00:53,730 --> 00:00:54,930
‫And I'm going to explain why.

14
00:00:57,540 --> 00:01:03,420
‫The database knows that the row is in the page, but it doesn't know exactly where in the index.

15
00:01:03,420 --> 00:01:04,470
‫I'm going to explain that.

16
00:01:04,470 --> 00:01:05,010
‫Why?

17
00:01:06,720 --> 00:01:09,420
‫I mean, let's just explain then go to the question.

18
00:01:09,420 --> 00:01:14,970
‫So why is the actual the database doesn't really know where the row exactly is?

19
00:01:15,090 --> 00:01:20,100
‫Because let's say the page is stored on desk, which is that's what it is.

20
00:01:20,100 --> 00:01:20,570
‫Right?

21
00:01:20,760 --> 00:01:24,840
‫The rows are stored in row store.

22
00:01:24,840 --> 00:01:25,010
‫Right.

23
00:01:25,020 --> 00:01:29,310
‫So that means the row column one column to column three, column for column five.

24
00:01:29,370 --> 00:01:33,990
‫And then the second row immediately comes after that column one column to column three to another column

25
00:01:33,990 --> 00:01:34,720
‫one column through.

26
00:01:34,740 --> 00:01:36,930
‫So the rows are just one after the other.

27
00:01:37,350 --> 00:01:37,950
‫So.

28
00:01:40,050 --> 00:01:43,580
‫Your statement, right, Shiva?

29
00:01:43,590 --> 00:01:46,290
‫No show is correct.

30
00:01:46,320 --> 00:01:47,970
‫I hope I'm pronouncing your name right.

31
00:01:48,660 --> 00:01:50,700
‫You said this correct.

32
00:01:51,090 --> 00:01:57,540
‫If the row size is fixed, which is pretty much I never seen it anymore.

33
00:01:57,570 --> 00:02:05,070
‫The only time the roll size is fixed is in my sequel database engine called my SIM.

34
00:02:05,400 --> 00:02:06,630
‫And that's pretty much it.

35
00:02:06,780 --> 00:02:09,920
‫The row size is never fixed.

36
00:02:09,930 --> 00:02:13,500
‫It's variable because you're going to have a string that is long.

37
00:02:13,800 --> 00:02:16,520
‫Some columns will have a shorter values.

38
00:02:16,530 --> 00:02:21,570
‫Some columns will be gnarled, some will have values, some code will have a blob.

39
00:02:21,570 --> 00:02:25,020
‫So some rows will be fat, some rows will be thin.

40
00:02:25,020 --> 00:02:29,430
‫Even the column, even the number of columns are fixed, right?

41
00:02:29,460 --> 00:02:31,560
‫The row sizes are never fixed.

42
00:02:32,160 --> 00:02:33,890
‫You might say, why not?

43
00:02:33,900 --> 00:02:36,340
‫Why not always have them fixed?

44
00:02:36,360 --> 00:02:37,860
‫That's what my Isom did.

45
00:02:37,860 --> 00:02:44,220
‫But they quickly we quickly found that we're wasting a lot of space, because if a string is empty,

46
00:02:44,220 --> 00:02:49,260
‫like, let's say, if a string field is like two, five, five, it's really wasteful to put two, five,

47
00:02:49,260 --> 00:02:52,110
‫five blank spaces if there is a null in there.

48
00:02:52,110 --> 00:02:52,680
‫Right.

49
00:02:52,770 --> 00:02:54,930
‫Because that's the only way you're going to make it fixed.

50
00:02:55,350 --> 00:02:58,530
‫If the row size is fixed, then the problem, we don't have a problem.

51
00:02:58,530 --> 00:03:03,990
‫But unfortunately today we have unfixed row size.

52
00:03:04,020 --> 00:03:11,610
‫That means the number of rows in a page are not fixed either because you can have a row that is so big

53
00:03:11,610 --> 00:03:16,650
‫that a single page will have a single row because it's so big.

54
00:03:17,310 --> 00:03:20,490
‫Or that's why you have a limit in number of columns and databases.

55
00:03:20,490 --> 00:03:26,580
‫You can just put unlimited number of columns because there is a fixed page size and the rows inside

56
00:03:26,580 --> 00:03:28,380
‫it has to fit inside the page.

57
00:03:28,620 --> 00:03:31,560
‫That is another thing, that database constraint.

58
00:03:31,560 --> 00:03:39,030
‫You cannot just have a row so long with so many columns that expense are spanning multiple pages.

59
00:03:39,210 --> 00:03:41,790
‫It becomes very problematic for the database.

60
00:03:41,790 --> 00:03:43,390
‫So database is put constraints.

61
00:03:43,620 --> 00:03:47,700
‫You know what fixed page size rows can span pages.

62
00:03:48,870 --> 00:03:55,740
‫So when you think about when you start understanding this thing and you store this page in disk, you

63
00:03:55,740 --> 00:03:56,970
‫know where the page is.

64
00:03:56,970 --> 00:03:57,780
‫Sure.

65
00:03:57,840 --> 00:03:59,280
‫Because it's a fixed page.

66
00:03:59,280 --> 00:03:59,730
‫Right.

67
00:04:00,270 --> 00:04:04,320
‫So, you know, page seven is in this position in the data file.

68
00:04:04,350 --> 00:04:06,990
‫Go there and put pull page seven.

69
00:04:06,990 --> 00:04:07,590
‫Sure.

70
00:04:07,770 --> 00:04:15,480
‫And that will be transmitted into the file system blocks and in the PSD will be transferred from logical

71
00:04:15,480 --> 00:04:21,170
‫bob at block addressing to the physical block addressing and move me will take care from that right

72
00:04:21,240 --> 00:04:24,120
‫and then we will fetch exactly that page.

73
00:04:24,780 --> 00:04:25,650
‫But the row.

74
00:04:25,650 --> 00:04:27,180
‫Nope they don't know.

75
00:04:28,070 --> 00:04:28,610
‫Right.

76
00:04:28,850 --> 00:04:30,380
‫We don't know exactly where.

77
00:04:30,500 --> 00:04:31,880
‫Even if we did.

78
00:04:32,240 --> 00:04:44,990
‫Even if you did know where exactly that row is, there is no concept in hard drives that is called byte

79
00:04:44,990 --> 00:04:45,800
‫address ability.

80
00:04:45,800 --> 00:04:49,040
‫You can just go and fetch one byte or three bytes.

81
00:04:49,430 --> 00:04:52,370
‫You have to fetch a block, right.

82
00:04:53,080 --> 00:04:55,060
‫And in disk space.

83
00:04:55,060 --> 00:04:58,260
‫This is, I think for kilobytes, it depends on the SSD.

84
00:04:58,270 --> 00:05:00,850
‫So you have to fetch a block of data.

85
00:05:00,880 --> 00:05:05,500
‫That's the limitation we have today with hard drives and SSD.

86
00:05:05,530 --> 00:05:07,720
‫You got to fetch a page, right?

87
00:05:07,810 --> 00:05:10,000
‫And I'm very sorry.

88
00:05:10,010 --> 00:05:13,300
‫Page and block is a very overloaded terms.

89
00:05:13,420 --> 00:05:16,650
‫So I know I'm using I'm using them interchangeably.

90
00:05:16,660 --> 00:05:18,790
‫That's how the entire industry uses them.

91
00:05:18,790 --> 00:05:20,440
‫Nobody can agree on anything.

92
00:05:20,440 --> 00:05:22,030
‫So you get to have to deal with that.

93
00:05:22,240 --> 00:05:22,480
‫Right.

94
00:05:22,480 --> 00:05:24,340
‫But when I say page, I mean databases.

95
00:05:24,340 --> 00:05:27,820
‫When I say a block, I mean disk, physical space.

96
00:05:28,300 --> 00:05:30,280
‫And people use the word sector here.

97
00:05:30,310 --> 00:05:34,780
‫Nobody can agree on anything anymore, fortunately, but this is the way it is.

98
00:05:35,290 --> 00:05:41,750
‫So when I do that, a page will consist of multiple blocks, filesystem block, which will transfer

99
00:05:41,750 --> 00:05:44,190
‫it to an actual hard disk block.

100
00:05:44,200 --> 00:05:49,690
‫That's what makes this very hard and confusing to talk through, right when it comes to pages and blocks

101
00:05:49,690 --> 00:05:50,500
‫and stuff like that.

102
00:05:50,620 --> 00:05:58,120
‫But yeah, so we don't have like the ram where I can go, go and fetch that byte, that particular row

103
00:05:58,120 --> 00:05:58,900
‫and nothing else.

104
00:05:58,900 --> 00:06:00,460
‫I don't want to go over the whole page.

105
00:06:00,820 --> 00:06:02,470
‫So your question is actually valid.

106
00:06:02,470 --> 00:06:06,130
‫You're challenging the database system today.

107
00:06:06,340 --> 00:06:10,300
‫Maybe you're going to come up with a database that actually solve this problem.

108
00:06:10,300 --> 00:06:11,530
‫Today, we have a problem.

109
00:06:11,530 --> 00:06:17,350
‫We fetch a page that has so much stuff that we don't need.

110
00:06:17,440 --> 00:06:18,430
‫I just need the role.

111
00:06:18,430 --> 00:06:20,430
‫Why are you giving me the whole page?

112
00:06:20,440 --> 00:06:24,460
‫So there is a cost of fetching reading IO from disk.

113
00:06:24,490 --> 00:06:26,590
‫There is a cost of transmitting.

114
00:06:26,590 --> 00:06:29,380
‫That is a cost of passing the database level.

115
00:06:29,770 --> 00:06:34,960
‫Are we transmitting you can we don't really transmit stuff we don't really need unless the client is

116
00:06:35,080 --> 00:06:40,960
‫asking for stuff that it doesn't really need and you end up transmitting it in the network.

117
00:06:40,960 --> 00:06:42,700
‫So that's that's basically it.

118
00:06:42,700 --> 00:06:46,690
‫So in the RAM you have byte address ability.

119
00:06:46,690 --> 00:06:52,300
‫So you can actually do this trick, say, hey, go to that address, give me that byte.

120
00:06:52,870 --> 00:07:01,720
‫There are other systems that tries to do a disk and SSD that is actually byte addressable, which will

121
00:07:01,720 --> 00:07:11,110
‫allow us, if we know where the actual row is, to go to that file and fetch exactly how much we need.

122
00:07:11,710 --> 00:07:15,340
‫That is going to be so powerful in that particular case.

123
00:07:15,460 --> 00:07:22,420
‫So what we end up doing is we try to be clever and says, okay, when I fetch a page, I'm going to

124
00:07:22,420 --> 00:07:25,840
‫make sure that all the stuff that I need is in that page.

125
00:07:26,350 --> 00:07:31,030
‫That's where clustering comes in and the concept of primary index here.

126
00:07:31,450 --> 00:07:36,490
‫So I hope you hope that makes sense because we talked about most of this concept in this course.

127
00:07:37,270 --> 00:07:37,580
‫All right.

128
00:07:38,020 --> 00:07:40,150
‫How does this work in during an index?

129
00:07:40,150 --> 00:07:42,280
‫I mean, an index point you exactly.

130
00:07:42,280 --> 00:07:42,780
‫To the arrow.

131
00:07:42,790 --> 00:07:43,240
‫All right.

132
00:07:43,240 --> 00:07:44,290
‫So this is another.

133
00:07:45,460 --> 00:07:46,560
‫Good point.

134
00:07:47,020 --> 00:07:49,360
‫So that he's right.

135
00:07:49,450 --> 00:07:55,480
‫The index when I travel the index, I find the index and I find exactly the page.

136
00:07:55,480 --> 00:07:56,440
‫And the row.

137
00:07:56,500 --> 00:08:00,160
‫Row ID is not a row index or position.

138
00:08:00,160 --> 00:08:01,930
‫It gives you the row ID.

139
00:08:01,960 --> 00:08:03,250
‫It's an identifier.

140
00:08:03,250 --> 00:08:04,810
‫It's not a pointer.

141
00:08:05,050 --> 00:08:07,900
‫It's not an index in an array.

142
00:08:07,930 --> 00:08:10,960
‫Don't think it's an ID, it's a tuple ID.

143
00:08:11,440 --> 00:08:17,950
‫So when I get that ID, I'm responsible to go to the page where this ID lives.

144
00:08:17,950 --> 00:08:25,280
‫And again, I'm going to fetch that page from the heap where the heap was populated from the desk you

145
00:08:25,420 --> 00:08:34,930
‫read a page right from the desk we put it in the heap and in the B3 when we scan the B3, what is the

146
00:08:34,930 --> 00:08:35,410
‫B3?

147
00:08:35,410 --> 00:08:37,000
‫It's a bunch of files.

148
00:08:37,390 --> 00:08:44,200
‫It was at some point it was something it stored in the file and we fetched it from the disk, put it

149
00:08:44,200 --> 00:08:47,110
‫in memory, and then we built pages out of it.

150
00:08:47,110 --> 00:08:51,670
‫So when even when you read the beta, you're reading pages, right?

151
00:08:51,670 --> 00:08:53,380
‫The root is in a page.

152
00:08:53,380 --> 00:08:55,840
‫These intermediate nodes are in the page.

153
00:08:55,840 --> 00:09:02,770
‫So you happen to read one node, maybe one page happened to have three nodes, right?

154
00:09:02,920 --> 00:09:06,550
‫I believe the beta structure is one page equals one node.

155
00:09:06,550 --> 00:09:09,460
‫That's the that's the de facto.

156
00:09:10,210 --> 00:09:16,390
‫So when you read one page, you get one node and then node have n elements in it effectively.

157
00:09:16,390 --> 00:09:17,970
‫And we talked about that in the beta area.

158
00:09:18,130 --> 00:09:27,400
‫But once we find the root and then find the row, the row will live with many other stuff in a page,

159
00:09:27,400 --> 00:09:30,460
‫right in the B three leaf page.

160
00:09:30,880 --> 00:09:37,330
‫And you're going to have a lot of them, a lot of rows because you found you happen to read that leaf

161
00:09:37,330 --> 00:09:42,970
‫page and the leaf page will have money in three row IDs and then you have the royalty.

162
00:09:42,970 --> 00:09:49,690
‫Now you have to go to the actual heap, which will have the row ID and all the columns because remember

163
00:09:49,690 --> 00:09:54,610
‫the index only have one or whatever columns that you actually indexed.

164
00:09:55,210 --> 00:09:59,290
‫In this case you go back to the heap, fetch that.

165
00:10:00,140 --> 00:10:03,560
‫Find where the real ID you have to find it.

166
00:10:03,560 --> 00:10:05,450
‫You cannot unfortunately go to the.

167
00:10:06,370 --> 00:10:09,340
‫It's a it's not an indexed position.

168
00:10:09,730 --> 00:10:14,680
‫So you have to read all the rules in the page and find out exactly which one is yours.

169
00:10:15,850 --> 00:10:19,060
‫And you can't assume order either.

170
00:10:19,330 --> 00:10:22,210
‫You have to keep looking until you find things in there.

171
00:10:22,600 --> 00:10:25,580
‫The heap is not ordered unless the whole thing is clustered.

172
00:10:25,600 --> 00:10:26,740
‫That's a different story.

173
00:10:26,740 --> 00:10:28,540
‫So hopefully that clarified it.

174
00:10:28,540 --> 00:10:30,370
‫But that's a fantastic question.

175
00:10:30,700 --> 00:10:38,560
‫I like question like this because it sounds like students are actually challenging the what is existing

176
00:10:38,560 --> 00:10:38,800
‫today.

177
00:10:38,800 --> 00:10:40,570
‫And this is exactly what I want.

178
00:10:40,600 --> 00:10:43,420
‫I want you to not take anything for granted here.

179
00:10:43,870 --> 00:10:45,960
‫I'm not teaching you, per se.

180
00:10:47,560 --> 00:10:50,050
‫I'm just showing you the way, if that makes sense.

181
00:10:50,170 --> 00:10:58,540
‫I'm showing you how things are today, and you feel free to challenge anything because nothing is perfect.

182
00:10:58,960 --> 00:11:00,340
‫Hope you enjoyed this lecture.

183
00:11:00,340 --> 00:11:01,390
‫I'm going to see you on the next one.

184
00:11:01,420 --> 00:11:01,870
‫Goodbye.