 where we left off, we saw how to insert and delete into and from B plus 3 and query B plus 3. The next topic is what is called B plus 3 file organization. So, if you look at the figures we saw till now, B plus 3 let us take the original B plus 3 figure a long way back. We had a file storing the data and the B plus 3 simply stored pointers to the data. But this is not necessarily the best way of doing things. There are many situations where we may want data to be stored in the leaf node of the B plus 3 itself. So, this is called a B plus 3 file organization. So, this is the figure which I was on just a second ago. So, if you see here the leaf this is a B plus 3 kind of drawn funny leaf because it was too wide to fit in one line. So, the leaf nodes had to go over two lines, but it is a plain B plus 3 there is nothing special about it. The only difference is at the leaf instead of storing a pointer to a record we are storing the actual record itself. So, a 4, b 8 and these are the actual records there is no pointer. So, the leaf level contains records. Now, there are a few issues that come up if you do this. If the leaf level contains records how many records fit in a node? If you look at the internal nodes it does not store a whole record it just stores the search key value and a pointer. So, an internal node may be able to store a lot more key values and pointers than the number of records which can be stored in a leaf. So, that is the first difference that the leaf node may have only a few records while internal nodes still have lots of killer. The next issue which comes up is what is the records are variable size? How many records will fit in a leaf and the answer in this context is this is true even with variable length records with normal B plus 3s where variable length keys normal B plus 3s. So, what we do is we require that things be half full with data. So, if there are variable length we may not say that it must have at least n by 2 records, but we will say that at least half of the space in the node must get used. Now, that space may get used for storing long keys it may get used for storing more pointers, but regardless half the space must get used that is the key thing and for a B plus 3 file organization at the leaf node the same thing holds at least half the leaf space must be used by records. Otherwise there is nothing special here in terms of insertion deletion algorithms it is standard B plus 3 algorithm, but what is this problem solved? It solved the problem that you know earlier we said we assume that the file is sorted on a key, but who is going to keep it sorted on the key? Somebody has to go periodically and sort the file and in fact even today PostgreSQL does not support B plus 3 file organization. You can ask it to sort a file called clustering a file, but after you do insertions deletions to the file the sort ordering can go for a toss may be approximately sorted, but once in a while you have to say cluster it again, but who is going to do that when are they going to do it? A B plus 3 file organization solves the problem because as in when deletions happen the sort order is maintained if you go across leaf the logical sort order is maintained. What is not guaranteed is that these nodes are physically sorted on this that cannot be guaranteed, but there are some tricks which try to keep these things more or less sorted with minimum number of disk seeks as you seek across the leaves of a B plus 3. There are tricks to make sure that the number of seeks you require is minimized. I do not have time to get into that, but that is used here that is one thing. The next issue here is that we did not care much about space utilization we said half full is fine because the index is pretty small. Supposing my data is 100 bytes the index space may be may require 10 bytes for the same amount of 12 bytes for that data. So, it is may be 10 percent of the space if it is half full the overhead is may be 5 to 10 percent of that it is actually B plus 3 is tend to be closer to two-thirds full. So, overhead may be only 3-4 percent of the total data size for one index. So, it may not be worth putting too much effort to save 3 or 4 percent, but if I am storing big records in the leaf if it is only half full I have a problem I may be wasting half the space of the file which can be very large if there are lots of records. So, there is a trick to improve the space utilization which is when you do splits and merge you do the following. If I need to split if I split a node into two it is going to become half full I do not want to do that. So, what I will do first is if a node is full and needs to be split I will first look at its sibling on the left and on the right and say look at least one of the sibling say sibling on the left is not full I will try to push some values from the node into the sibling. So, redistribution happens not only on deletion, but it can happen on insertion also. So, now when I insert if the node is full I redistribute, but there may be a situation where the left sibling I only focus on one sibling left or right one sibling I look at and it is full I am full I am over full and my sibling is also full what to do now. So, at this point I can take data which is two node fulls worth. So, I have two node fulls of data and I split it into three nodes I sort it and divide it among the three nodes. What can I say about the three nodes each will be at least two thirds full. So, what I have just ensured is when I split the minimum utilization is 2 by 3 it is not half. So, that is what splitting what about for deletion in deletion what did I do I try to borrow, but if I could not borrow because the sibling is half full I merge with the sibling, but now I do not want to go to even half full I want to stay at two thirds full. So, I am going just under two thirds full I do not want it to go under two thirds full. So, what do I do I try to borrow from a sibling and I will look at my sibling and it says no sure I have some data take a few keys and now both of us are two thirds full I am happy. If my sibling says no I do not have enough data to give you anything I am also at the border line of two thirds full what to do now and the trick is to ask one more sibling between the three of us can we redistribute data such that all of us stay at two thirds full then do it, but supposing both the siblings that I look at are at two thirds full and I am under two thirds full now I cannot borrow from either sibling, but here is what I do I have three siblings each two thirds full. So, what is the total amount of data it is two nodes worth three times two by three is two. So, the total data across the three nodes is as much as will fit in two nodes. So, it is very simple I take three nodes merge them into two nodes and what I ensure is that on deletion the utilization never goes under two by three. So, that is what I do. So, insertion and deletion both take care of this and we can get whatever utilization we want. We can look at three siblings instead of two four siblings instead of three and so forth and push the utilization from two thirds to three fourths to four fifths or whatever. There is a trade off there is more I O when I do insertion deletion and the benefit of more I O is better utilization, but at some point it is not worth it I will not involve too many more siblings. So, that was one part then next topic is if I use a B plus three file organization it causes a another different problem. If I have a secondary index earlier on I said it stores a record pointer. Now, look at this B plus three file organization the record is here and I have a secondary index pointing to this page, but now an insert happens this page is full it splits what happens half the records in this page move to a new page. Now, if I have a secondary index on the same relation this half of the page that record those records may be fifty to hundred records are moved. Now, one insertion here requires fifty to hundred updates on secondary indices all that is updated is the record pointer changes. So, the index structure does not change, but I have to update fifty to hundred records in index records in another index that may involve fifty to hundred I O's they may be scattered all over in that index that is very expensive I cannot afford that. So, instead so this is the problem this is the reason why post-criscule does not have B plus three file organization and neither did most other databases some years ago about ten fifteen years ago many databases one by one started adding B plus three file organization and the key trick which they used to solve this record relocation problem with secondary indices is the following. A secondary index no longer stores a pointer to the record you cannot afford to because records move very often you cannot update them. Instead what a secondary index source is the primary index search key. Now, assuming this is unique the search key is you know if it is not unique we will add some things to make it unique and not going to detail, but the key thing is that when I split the leaf of the B plus three file organization no update is required because there were no physical pointers they only had a search key in the secondary index that is a good new the overhead though is that when I search on the secondary index I will search all the way down to the leaf of the secondary index what I get is not a record pointer what I get is a primary index search key. Now, I come back to the primary index and search down when I get to the leaf of the primary index here it is a file organization I have found the record I want. So, if I had a secondary index on the second attribute nine I will search down that tree and at the leaf instead of saying point to this node it will say the primary key is C primary index search key is C sorry D and then when I come down and search for D I find this record. So, there are two traversals, but splits are now a couple more topics I will go over quickly. The first topic is called bulk loading and bottom up build I would not get into all the details of how this is done, but the key point I want you to notice what is the cost of doing an insert into a B plus tree assuming that tree is very large it does not fit in memory I may have to traverse down the various nodes of the tree and then land up at a leaf and insert a value at the leaf. If the tree is much bigger than memory and I have a series of random inserts into a B plus tree random order the probability that the leaf I am looking for is in the in memory buffer is small. So, most insertions will land up in requiring an IO to fetch the leaf and then an IO to write it back. So, two IO operations per insertion now if I am inserting a lot of records this becomes very expensive. So, this is one of the reasons if I told you if you use JDBC one of the reasons it is slow is that there are overheads to copying it to JDBC and so forth. The other overhead is to insert into the indices one record at a time. So, an alternative to this is what is called bulk loading and most databases support it and the trick is relatively simple what they do is when I am inserting a lot of data they will first sort that data and then insert it in sorted order. Now, if I insert in sorted order then the next record to be inserted will be in the same page for a while perhaps and after the some records are inserted may be the next record will move to a subsequent page and so forth. But I would not go back and forth between different leaf nodes I will just get the thing sorted. So, let me use the whiteboard let us say this was the leaf level of a B plus tree and it is divided into multiple blocks and there is an index above that those are the internal nodes. I have some new data which I want to insert which is a lot of data. So, the first step is sort new data by data I mean actually just the entries what is the entry it is a search key value and a record pointer. So, I sort that on the search key of this tree now the first thing few things may go to the first block the next few may go to the next block there may be a gap the next one may go there and so forth. So, I will go through the leaf levels of this tree exactly once and similarly for the internal nodes each node will be accessed may be multiple times consecutively one after the other and this is a lot more efficient than random inserts into the tree. Now, there are for this is for inserting into a tree if I am building a B plus tree fresh there is a further optimization that I can do what I will do is I will sort all the entries to be created and then build the upper layers of the tree in a special process which is called bottom of construction. I will skip the details and so there is this bulk loading and bottom of B plus tree construction I wanted to expose you to the term you can read it up later. The next topic which I will briefly mention is multiple key access. So, here is a small example I want to find instructors in the finance department with salary 80,000 this is a very a idiotic artificial query, but there are more realistic examples where I will look upon two attributes or more attributes. So, what can I do if I have an index on a department name I can use that and fetch those instructors and check if their salary is 80,000. If I have an index on salary I can find instructors with salary 80,000 fetch their records and then check if department name is finance. A third option is if I have an index on both of these separately I can use the department name index to find pointers to all records pertaining to the finance department. Use the index on salary to find all records with salary 80,000 and now take the intersection of these two sets of pointers to find the final set of instructors in the finance department with salary 80,000. This approach is what web search engines do when you give multiple keywords they will find a list of web pages containing the first keyword, a list of web pages containing the second keyword and then do an intersection of these lists to get your final answer, but there is one other possibility in our context which is to create an index on a composite key. So, the index could be on department name comma salary. Now, when you have a composite key you need a ordering. So, all of the B plus 3 operations say is it less than this key or is it greater than that key. If I have two attributes how do I do less than greater than comparison and that is lexicographic. So, I have had values a 1 a 2 department 1 salary 1 and another one department 2 salary 2 how do I decide on the short ordering. So, this is less than that if either a 1 is less than b 1 or a 1 is equal to b 1 and a 2 is less than b 2 that is the standard lexicographic short ordering. So, I just create an index treating this as one attribute and using lexicographic ordering otherwise the B plus 3 algorithms are exactly the same. So, now I can use a combined index on department name salary to find instructors where department name is finance salary is 80,000 this would be very efficient. If you contrast with using separate indices here there may be many people in the finance department there may be many people with 80,000. So, I may spend a lot of effort finding those record IDs and then intersect them and I may get very very few, but if I have a combined index I can very efficiently find just those which have finance department salary 80,000. The same index on a composite key can also handle some other queries efficiently take this query department name is finance salary less than 80,000 that can be handled very efficiently they are all consecutive records in this search index. However, I cannot handle efficiently department name less than finance and this should be salary not balance. So, I cannot handle this efficiently because the records that satisfy this condition name less than finance and balance 80,000 are going to be scattered all over that index. So, it is not possible to find them consecutively and there is a much higher cost to running this query. Whereas, this previous query department is finance salary less than 80,000 they are all consecutive. So, this is very efficient. So, that is how indices on multiple attributes work to wrap up this session I think this is there are two last slides left. The first slides is index definition in SQL the SQL standard really does not dig into index creation, but most databases have command like this create index index name on relation name list of attributes. So, here create index some name on this is old relation branch we do not use it anymore, but imagine there is a relation called branch with attribute called branch name. So, you could create an index on that what kind of index by default most databases would create a B plus tree index, but many will let you control that you can also drop indices. So, what are the other kinds of indices which exist there are many types, but the popular ones there is something called bitmap index which are used in decision support system. They are not very useful for online transaction processing systems, but they are very nice for decision support these are used for attributes with very few distinct values like gender may be the lot of marketing analysis once know the income level of people. They do not want to know your exact salary, but they want to know if you are in socio economic level 1 2 3 4 5. So, the only few different values then they may make a decision based on may be your age which again they may bucketize into a few categories not exact age, but age range. Each of these have only a few distinct value and a different kind of index is very useful when the number of distinct values is smaller. And they are particularly useful for aggregate queries tell me how many people are in socio economic level 1 in the age bracket 20 to 25 who are male. So, these kinds of queries or can be done efficiently using such indices. Then there are hash indices which are very widely used for in memory indexing in inside of databases, but these days they are not common for on disk indices. And lastly R trees somebody had asked a question already about R tree. These are used to index geographical or spatial data multi dimensional data. And as I said earlier you can efficiently find all shop within a specified region rectangular circle using R trees with a B plus tree index I could build an index on x and y the coordinates latitude longitude. With that if I say find me what is located at exactly this latitude longitude find a shop. Let us say I have lat long for shops find me a shop that is located at this lat long I can use a B plus tree, but if I say find me a shop that is located near this I cannot do it. If I say find me shops that are located in this rectangular circle I cannot do it efficiently using a B plus tree. So, that is the end of that chapter. There is a lot more stuff in the book and in the full version of the slides which have skipped. What I will do is we are at lunch time, but let me take a few questions may be 5, 10 minutes max worth of questions. And before we do that your afternoon session today is on web application development using servlets. So, I think all of you have the background and hopefully you know enough Java to manage this. Those of you who do not know Java might have struggled a bit in yesterday's assignment, but we have given templates for yesterday's and today's assignment. So, even if you do not know Java very well you can take the templates get a hang of how the language looks like and then modify it to get what you need. So, we can take a few live questions. Teherna Engineering College, Naveen Mumbai please go ahead. I have one question on indexing sir. How many indexes can we have create on a relation? Is there some maximum limit which could impact the performance of the database or is there a minimum limit or we can create as many indexes we want like please could you tell something along those lines. That is a good question. So, every index which you create is a kind of overhead. First there is a space overhead for storing the index. The second is every update insert delete which affects the attributes you know in updates which affect the index attribute. As well as any inserts or deletes all of them have to update all the indices which are relevant. Insert deletes have to update every single index. Updates have to index whichever indices are on the updated columns. So, there is a time overhead to keeping all the indices up to date every time an insert delete or update happens. So, that is this part which is performance related. So, you do not want to create indices indiscriminately. You only want to create it if it is useful to you. So, if you have queries which make use of the index you should create a index. That if you create an index nobody uses it, but still the price of keeping it up to date is paid by all the insert deletes and updates. So, that is a database tuning question. So, there are tools which are offered with commercial databases which help you analyze the workload. You have to somehow collect the workload which are what are the queries inserts deletes updates which are happening. Based on the workload which you observe let us say for an hour or half an hour or a day or whatever. Based on that workload the system can recommend what indices to create or what indices to drop. So, that is one part of it. The other part of your question is is there a limit which is imposed by the database. Many databases do have limits on the number of indices they will support. The reason is that they have some data structures which are more efficient if you assume maximum number of indices per relation. So, what is that limit depends on your database. Obviously, you cannot cross that limit, but that limit is an implementation decision. It has nothing to do with the overheads for insert deletes, but it may be related in the sense that if you have 30 indices you are probably paying a high price and they believe that it does not make sense to have more than 30 indices. So, they did not bother to support it. Usually you do not need all that many indices. Yeah, another question. Hello. Sir, I have one more question. What kind of storage structure is used to store audio and images files? As we have stored that images are having B-Lob data type, but what kind of storage structure is used? And one more question is there. What kind of indexing technique is used for semi-structured data? Okay. So, let me answer the first question. What kind of storage structure is used for large objects like images, videos and so on? Now, the first thing is most applications keep these outside of the database. So, they use the file system directly. Now, what if you tell the database to store this? There are several approaches which are possible. One approach is that the database directly uses the file system to store these things, but that could result in a lot of files. So, that may not be a great idea. Another approach which could be used is to use B plus tree to store blocks where the blocks contain data from these objects. So, what you are doing is you are storing these objects as blocks, but which all blocks are there in a particular object. You could link those from a B plus tree. So, there is an index on the ID of that object. And through that index, you can find what all blocks contain data for that object. And if the object is large, it may have multiple blocks. The index will tell you the key for that index will be object ID of the object followed by the block number. So, you can fetch the first block, second block and so forth. So, the database might manage itself without creating many separate large files. In fact, it turns out that if you create millions of files in the file system, every operation on that file system goes for a toss. We have had pretty bad experience even with new versions of Linux. If you create let us say 9 million files in a directory, you do LS on that directory, it hangs. It takes minutes for the LS command to even return. You cannot kill it. You cannot do anything. It is terrible. So, it is usually a bad idea to create millions of little files. And the database might choose to put them all in one large file, but keep an index to record which blocks of the file contain blocks of which object. So, that is how large objects would be stored inside of the database. There are issues in compaction and so on which we would not get into. Now, coming to the other question which is indexing for semi-structured data. Let us say XML and so forth. So, how do you index XML data? So, there are what are called path expressions on XML data and you can build an index to answer a path expression whose tail contains a specified value. So, there are other kinds of indices in that context. Again, we do not have time to get into all the details. There could be keyword indices on semi-structured data. There could be indices on paths along with selection on value which is at the tail of the path and there are other types of indices. So, I will leave it at that. Maybe I can take one or two questions from Cache. One of the questions was by default, an index is created on the primary key of the table. Should we use create index to create additional indices? How are they managed and so forth? So, first of all, an index on the primary key is usually essential because without that index, if you insert a record, how do you check if the primary key value has been duplicated? That is an integrity constraint violation which has to be checked and the most efficient way of checking it is to have an index on the primary key. So, these are created by default by every database. The second kind of index which is usually automatically created is if you declare a foreign key constraint from R to some other relation S. On relation S, you have declared primary key and index is created already. So, that is not an issue. But on the referencing relation R, there is an issue. Suppose I want to delete a tuple from S. I need to know if any tuple in R is referencing that tuple. If it is, maybe I should not allow the deletion on S or if on delete cascade or so on has been specified, I need to find which all records in R are affected and cascade the delete to those records. For all of this, I need an index on R on the foreign key attribute. So, most databases would also create automatically an index on all the foreign key. If you declare any attribute or attributes of R to be foreign key attributes referencing some other relation, an index will automatically be created on the, on each foreign key. For each foreign key, an index is created. So, these two are the default indices which are automatically created. Beyond this, anything you need is up to you. Somebody else has asked a question about what is the column store index in SQL server and where is it used? So, column stores are something which I did not get into, but let me write the word here so that people are familiar with this. What is a column store? The idea is if I have a relation with multiple tuples, each tuple has multiple columns. Now, all the structures which we saw till now store a row, a record is a row and that is stored in a block. Now, what people found is there are many cases where relation has many attributes which are rarely used. They might be quite big, but they are rarely used. Most of the accesses to some relations happen on only a few of the attributes and many of them access may be just one attribute or two attributes. So, if you store a whole record together in a page, if I want to access even one, just one column of that relation, I have to fetch the whole relation which means I am fetching a lot of columns which never get used. There is a lot of overhead to fetching the columns. So, they said when for decision support queries where this is common, what they did is they broke up the relation slightly differently. They broke it up into columns. Let us say this is columns A, B, C, D. I will store all the values for column A in one place, all the values for column B in another place, everything for column C in third place, column D in a fourth place. I am storing only the value in that column and I am storing it exactly the same order it was stored in the original relation. So, updates and so on can cause trouble. There are workarounds to deal with that. Let us not worry about those issues. I had a relation stored in this way here. This is called a row store. In contrast, in a column store, the data is stored for each column separately. So, there would be one file containing all the values for column A, one file for all the values from column B. Now, one of the key things is you have to be able to know which value from here corresponds to which value from here. Assuming the data sizes are different, this is the 73rd thing here. It will correspond to the 73rd thing here, to the 73rd thing there and so forth. It may be 73rd value is here. So, these together form one row, but they are physically stored in separate places. So, the first step is supposing I have a selection which says find me something where column C. So, let us take a select A from this relation R where C equal to 51. So, what I would do is first I need to index C to find where the value 51 occurs in C, where all it occurs and that index might tell me it occurs at position 93 and 27. Now, what I do is I go to the file for column A and go to position 93 and 27 and fetch those values and output them. That is all that that query required. So, I have avoided fetching most of the data for this relation. So, in this particular example with just C equal to 51, a row store would have done fine. It is actually not such a big benefit for queries which retrieved a small amount of data, but queries which retrieved a lot of data let us say aggregate on column A. Then reading just column A sequentially and aggregating it is more efficient than reading all the other unnecessary columns and then aggregating. So, the primary use is for decision support and the first column stores that were built I think it started with Sybase. They had a factor of 10 performance improvement over the earlier system and lot of people were really surprised you could get such huge performance improvements. Of course, it does not work does not give you a factor of 10 improvement for everything, but for certain applications you could get a lot of improvement. Couple more questions here. Which databases are using B tree? Now, if you look at the textbook we differentiate between B tree which is the original proposal and B plus tree which is the version we use. In industry nobody bothers to say B plus tree they just say B tree, but what everyone implements is B plus tree. The version we gave is what is implemented by everybody and it is called B tree in the industry they do not bother saying B plus tree. So, all databases supported that I know. There are some unrelated questions people are asking questions about the JDBC assignment. Maybe I should spend a minute on that because it is going to affect today's assignment also. So, one of the things which people have said is that error JDBC driver not found. So, if you are using eclipse as we suggested the JDBC driver has to be put in the search path for eclipse. So, if you look at the instructions for JDBC and read it carefully this is given in the instructions. We tell you that you should add the place where you have placed the post office driver that needs to be told to the eclipse ID. So, please follow those instructions carefully if you skip one of those steps it may not work. And what some people found is that in the JDBC part it worked, but when they came to servlets when they tried to deploy a servlet at that point it did not work because they had done the first steps somewhat differently. So, please follow the instructions fairly religiously to ensure that the eclipse system knows where the files JDBC driver files are otherwise you get these errors. Sometimes like I said in the plain JDBC assignment sometimes only in the servlet assignment which is today's assignment. So, with that we will stop in this session and I will see you tomorrow.