‫Welcome to another Q&A session for the Fundamentals of Database Engineering Course.
‫Today we have a question from Shiva Ashu Switching a page versus a row in an index high in lecture 1616
‫where you explain how indexes and tables are stored.
‫You mentioned that we that when we execute a SQL query behind the scenes, the whole page is fetched
‫and only the row needed is filtered and sent to the user.
‫That is correct.
‫I have two questions.
‫Why does it get the whole page?
‫Why not the row?
‫Since the DB already knows the row location, that is actually not a correct statement.
‫The database does not know where the row is in the database.
‫In the page it has to read it.
‫And I'm going to explain why.
‫The database knows that the row is in the page, but it doesn't know exactly where in the index.
‫I'm going to explain that.
‫Why?
‫I mean, let's just explain then go to the question.
‫So why is the actual the database doesn't really know where the row exactly is?
‫Because let's say the page is stored on desk, which is that's what it is.
‫Right?
‫The rows are stored in row store.
‫Right.
‫So that means the row column one column to column three, column for column five.
‫And then the second row immediately comes after that column one column to column three to another column
‫one column through.
‫So the rows are just one after the other.
‫So.
‫Your statement, right, Shiva?
‫No show is correct.
‫I hope I'm pronouncing your name right.
‫You said this correct.
‫If the row size is fixed, which is pretty much I never seen it anymore.
‫The only time the roll size is fixed is in my sequel database engine called my SIM.
‫And that's pretty much it.
‫The row size is never fixed.
‫It's variable because you're going to have a string that is long.
‫Some columns will have a shorter values.
‫Some columns will be gnarled, some will have values, some code will have a blob.
‫So some rows will be fat, some rows will be thin.
‫Even the column, even the number of columns are fixed, right?
‫The row sizes are never fixed.
‫You might say, why not?
‫Why not always have them fixed?
‫That's what my Isom did.
‫But they quickly we quickly found that we're wasting a lot of space, because if a string is empty,
‫like, let's say, if a string field is like two, five, five, it's really wasteful to put two, five,
‫five blank spaces if there is a null in there.
‫Right.
‫Because that's the only way you're going to make it fixed.
‫If the row size is fixed, then the problem, we don't have a problem.
‫But unfortunately today we have unfixed row size.
‫That means the number of rows in a page are not fixed either because you can have a row that is so big
‫that a single page will have a single row because it's so big.
‫Or that's why you have a limit in number of columns and databases.
‫You can just put unlimited number of columns because there is a fixed page size and the rows inside
‫it has to fit inside the page.
‫That is another thing, that database constraint.
‫You cannot just have a row so long with so many columns that expense are spanning multiple pages.
‫It becomes very problematic for the database.
‫So database is put constraints.
‫You know what fixed page size rows can span pages.
‫So when you think about when you start understanding this thing and you store this page in disk, you
‫know where the page is.
‫Sure.
‫Because it's a fixed page.
‫Right.
‫So, you know, page seven is in this position in the data file.
‫Go there and put pull page seven.
‫Sure.
‫And that will be transmitted into the file system blocks and in the PSD will be transferred from logical
‫bob at block addressing to the physical block addressing and move me will take care from that right
‫and then we will fetch exactly that page.
‫But the row.
‫Nope they don't know.
‫Right.
‫We don't know exactly where.
‫Even if we did.
‫Even if you did know where exactly that row is, there is no concept in hard drives that is called byte
‫address ability.
‫You can just go and fetch one byte or three bytes.
‫You have to fetch a block, right.
‫And in disk space.
‫This is, I think for kilobytes, it depends on the SSD.
‫So you have to fetch a block of data.
‫That's the limitation we have today with hard drives and SSD.
‫You got to fetch a page, right?
‫And I'm very sorry.
‫Page and block is a very overloaded terms.
‫So I know I'm using I'm using them interchangeably.
‫That's how the entire industry uses them.
‫Nobody can agree on anything.
‫So you get to have to deal with that.
‫Right.
‫But when I say page, I mean databases.
‫When I say a block, I mean disk, physical space.
‫And people use the word sector here.
‫Nobody can agree on anything anymore, fortunately, but this is the way it is.
‫So when I do that, a page will consist of multiple blocks, filesystem block, which will transfer
‫it to an actual hard disk block.
‫That's what makes this very hard and confusing to talk through, right when it comes to pages and blocks
‫and stuff like that.
‫But yeah, so we don't have like the ram where I can go, go and fetch that byte, that particular row
‫and nothing else.
‫I don't want to go over the whole page.
‫So your question is actually valid.
‫You're challenging the database system today.
‫Maybe you're going to come up with a database that actually solve this problem.
‫Today, we have a problem.
‫We fetch a page that has so much stuff that we don't need.
‫I just need the role.
‫Why are you giving me the whole page?
‫So there is a cost of fetching reading IO from disk.
‫There is a cost of transmitting.
‫That is a cost of passing the database level.
‫Are we transmitting you can we don't really transmit stuff we don't really need unless the client is
‫asking for stuff that it doesn't really need and you end up transmitting it in the network.
‫So that's that's basically it.
‫So in the RAM you have byte address ability.
‫So you can actually do this trick, say, hey, go to that address, give me that byte.
‫There are other systems that tries to do a disk and SSD that is actually byte addressable, which will
‫allow us, if we know where the actual row is, to go to that file and fetch exactly how much we need.
‫That is going to be so powerful in that particular case.
‫So what we end up doing is we try to be clever and says, okay, when I fetch a page, I'm going to
‫make sure that all the stuff that I need is in that page.
‫That's where clustering comes in and the concept of primary index here.
‫So I hope you hope that makes sense because we talked about most of this concept in this course.
‫All right.
‫How does this work in during an index?
‫I mean, an index point you exactly.
‫To the arrow.
‫All right.
‫So this is another.
‫Good point.
‫So that he's right.
‫The index when I travel the index, I find the index and I find exactly the page.
‫And the row.
‫Row ID is not a row index or position.
‫It gives you the row ID.
‫It's an identifier.
‫It's not a pointer.
‫It's not an index in an array.
‫Don't think it's an ID, it's a tuple ID.
‫So when I get that ID, I'm responsible to go to the page where this ID lives.
‫And again, I'm going to fetch that page from the heap where the heap was populated from the desk you
‫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
‫B3?
‫It's a bunch of files.
‫It was at some point it was something it stored in the file and we fetched it from the disk, put it
‫in memory, and then we built pages out of it.
‫So when even when you read the beta, you're reading pages, right?
‫The root is in a page.
‫These intermediate nodes are in the page.
‫So you happen to read one node, maybe one page happened to have three nodes, right?
‫I believe the beta structure is one page equals one node.
‫That's the that's the de facto.
‫So when you read one page, you get one node and then node have n elements in it effectively.
‫And we talked about that in the beta area.
‫But once we find the root and then find the row, the row will live with many other stuff in a page,
‫right in the B three leaf page.
‫And you're going to have a lot of them, a lot of rows because you found you happen to read that leaf
‫page and the leaf page will have money in three row IDs and then you have the royalty.
‫Now you have to go to the actual heap, which will have the row ID and all the columns because remember
‫the index only have one or whatever columns that you actually indexed.
‫In this case you go back to the heap, fetch that.
‫Find where the real ID you have to find it.
‫You cannot unfortunately go to the.
‫It's a it's not an indexed position.
‫So you have to read all the rules in the page and find out exactly which one is yours.
‫And you can't assume order either.
‫You have to keep looking until you find things in there.
‫The heap is not ordered unless the whole thing is clustered.
‫That's a different story.
‫So hopefully that clarified it.
‫But that's a fantastic question.
‫I like question like this because it sounds like students are actually challenging the what is existing
‫today.
‫And this is exactly what I want.
‫I want you to not take anything for granted here.
‫I'm not teaching you, per se.
‫I'm just showing you the way, if that makes sense.
‫I'm showing you how things are today, and you feel free to challenge anything because nothing is perfect.
‫Hope you enjoyed this lecture.
‫I'm going to see you on the next one.
‫Goodbye.