‫So I want to spend a few moments to talk about the differences between a primary key and secondary key
‫or something called a primary index or a secondary index.
‫They are almost analogous.
‫You might think you know the differences, but they are very subtle and I want to talk about those two
‫concepts and database system within the context of actual database platforms out there, Posterous,
‫MySQL, Oracle, and how these how and how when we actually say a primary key, you get so much knowledge
‫out of that word that will actually make you ask questions.
‫It's not just
‫that the fact that the primary key is unique, that is true.
‫But it also gives you other kind of implicit information that will give you the necessary understanding
‫to optimize your queries to to know what you're doing when you actually executing queries against a
‫primary key or a secondary key.
‫How about we jump into it in order to explain what a primary key is?
‫We really need to first explain the concept of the table spaces or the heap.
‫When we're talking about a store where you have rows in a table, we store, we dedicate area and desk.
‫We call it usually the heap in both squares or even in database as well, which is usually a slow access
‫space.
‫Not much these days, but where where all the data expensive data, large it is, sits there and the
‫table is organized.
‫Basically, Roblero, there is no.
‫Order to a table.
‫We don't enforce ordering by default, assuming no primaries or anything like that, you're just inserting
‫data and that rose come after each other.
‫So if you insert the value of seven goes top, if you add to the value of one goes to the bottom.
‫And then if you add that another value of.
‫Two is still goes to the bottom, it gets keeps spending.
‫So there is no order that is maintained by default.
‫Now, when we add the concept of a primary key, what we do to the table is we do something called clustering.
‫And clustering is the idea of organizing the table around that key so beautiful we have to maintain
‫order.
‫And by default, the rows that you insert must fit that order.
‫That means there is an additional cost associated with this ordering.
‫The good thing we get because of this is like.
‫It's an almost like an index in the table itself, if you think about it, but it's organized that way.
‫That is why in Oracle, they call it Iot or Iot index organized table that I'm not to be confused with
‫the Internet of Things that other Iot and in in Postgres.
‫Or other data, because they call it a clustered index, or when you say a primary key in general,
‫that's what we understand.
‫We there is there is an index and then in the heap.
‫Right.
‫And the heap is organized around that index.
‫Let's take an example.
‫Let's say Ion's of the value of one.
‫Right.
‫And that value of one is my unique primary key.
‫So there will be the value of one and all the the rest of the row, like you have a first name and last
‫name.
‫It just goes there as well.
‫It will be organized in that manner.
‫So now if I insert the value of eight right, it will go right under it.
‫Right.
‫But if you now and so the value of to that value of two has to to sit right after the value of one.
‫Right.
‫So.
‫You you have to kind of push the value of eight below so you can sneak in the value of two because they
‫have to be clustered together, that you have to maintain order.
‫And as a result, if you obviously databases don't shift and left like this.
‫Right.
‫They are smart.
‫They do.
‫They have the page that they inserted.
‫They know, OK, value for in value of eight.
‫I'm not going to put it in the same page.
‫I'm going to leave some space for the value of two, three, four or five, six and seven because there
‫are some values that need to be there.
‫So the databases are smart.
‫They're not.
‫This is I'm thinking about the naive implementation of things.
‫And then when you do that, you will get that.
‫You will get that extra cost, but the best thing that you can do with clustered indexes or primary
‫keys is speed because especially with the range.
‫So let's say if I if I asked you to give me all the values from one to nine.
‫Right.
‫Especially range, that is almost like a one single eye.
‫Or you go to the heap, which is the in this case, the index, and it's beautifully organized.
‫So if you ask for these values, you get one Io, you get all these calls because they are nicely talk
‫together.
‫However, in a configuration where you have a secondary index, that means the table is a jumbled mess,
‫the value of one and then seven and an eighth.
‫And then seven.
‫Seven hundred and three.
‫And then the better value one.
‫Then they are not organized.
‫Right.
‫Again, I'm talking about a primary key that is random in nature here.
‫Not all primary keys are random.
‫That's why I get is a bad idea to have as a primary key, especially in as a primary key.
‫Right.
‫Because the randomness nature of things, you will start jumping through the heap left and right.
‫You will not benefit of the memory caching that the database is doing to answer the Suros, which is
‫which is something I talked about some of the limitations of my school, which is, by the way, a primary
‫based primary care database.
‫You have by default, you have to have a primary key in my school and you get one for free, I believe,
‫which is sequential.
‫But you have to have one and you have to have that means you have to have your table clustered in that
‫way.
‫Get Oracle, give you that option.
‫To have the index organize table, but you don't have to write SQL Server again, you have the option
‫to have the index organize table or they call the clustered index, in that case, a primary clustered
‫index.
‫You can have that.
‫So a clustered index that is sequential is not that bad, but.
‫You can get into other things that I don't want to spend more time in this video, make it we can we
‫can leave it to the lecture.
‫So that's that's a primary loss to the second or the second.
‫The key is having the table as a jumbled mess, but having an additional outside structure.
‫That is your key.
‫That is your index as your tree.
‫It's not in the table.
‫That table is a jumbled mess.
‫So you have the value of one seven and then three hundred and then seven and then eight, assuming there
‫are no order.
‫And now all the indexes or the index that is pointing to your column has no order in itself in the table,
‫but it is ordering another structure.
‫So there is you're maintaining a separate structure for your indexes.
‫OK, that's called a secondary index.
‫Now, if you want to search, you have to jump to this index first.
‫Do the search, collect the tuples or the pages that you want to query, because guess what?
‫You're not going to find everything in the index.
‫Yes, you're going to find the things that you're looking for.
‫But not all the information is located in the index.
‫You're going to find the row IDs.
‫You're going to find that rows wrong.
‫Number A is the value.
‫Look at a lower number BNZ are the values that you're looking for.
‫But you still have to go to the HEB, to the table to actually fetch the row itself, especially if
‫you drink select star on things.
‫So you have to go that do that jump.
‫That's the that's the disadvantage of secondary indexes.
‫Posterous all indexes are postgres are secondary indexes.
‫You don't have primary indexes.
‫And in pulseless can you have one.
‫I'm not sure.
‫Can you create an index that is a clustered index.
‫Maybe.
‫I need to check.
‫I think there's an option to to call to cluster your table so that they are organized around that index.
‫I like I prefer the Oracle term better than the index, better than than the clustered index naming
‫or the primary key for that matter.
‫Index organize table is is its self descriptive and I love it.
‫Right.
‫As opposed to the opposite heap organized table.
‫A hot heap organize table is when when you have the heap organize the tables is just random.
‫There is no index that organizes the table, there's no primary queue that fits that.
‫And yeah, that's what I wanted to talk.
‫I want to keep this lecture short and sweet.
‫I hope you enjoyed this lecture.
‫I see you in the next one and say awesome, enjoy the course.