Why are Indices needed ?
A free video tutorial from Siva Prasad
SAP Trainer and Consultant
4.4 instructor rating • 10 courses • 75,770 students
Learn more from the full courseSAP S/4HANA Training - in Plain English
Simple Logistics for SAP Functional Consultants in Logistics ( SD, MM )
06:57:44 of on-demand video • Updated August 2018
- Understand what is SAP HANA
- Understand the design philosophy of SAP S/4HANA
- Understand the future of SAP S/4HANA
English [Auto] So why need Indic steepens the basic answer is to speed up access to look at a traditional database Arab-Israel database table is typically rows and columns of data. So this is the order number. This is an example of a Sales Order table soon sort of people to be precise. And then we have agreed to date when the order was carried. And then we have a column for who created this data and then so on and so forth. This is the VBA code table. Now let's say an order has been created order number 1 create it on 0 1 0 1 such and such a date. And then somebody has created use user 1 Once data is being written to this table. Most of the time it's read operations. OK give me pull order number one. Pull up all orders created on the first of January by user 1 pull up all debit memos which is a type of an order created by this user on this date. Right. We could take one such query say a very simple query how does the B-B read that a car typical with short indices. So when you look at the goods like this this is like a good read Howard's defined in Excel rows and columns of freedom. That's how we tend to visualize it. And when you go to 16 that's so Sep shows it to us that a visual representation. Inside inside the system they are all store sequinned chivy. This is the first record. This is the second Richar. This is the third record in memory. There's total like that adjacent to one another. So if you find it quietly saying I need all orders created by user 1 assuming indexes are not where it goes step by step reads each of these Richart say goes here. Oh reach disregard and see who the user is or is it user 1. No this is user. OK. Skip this record go to the next read. Disregard. What does the user use or want. OK. Make a note of this and then go to the next one. Read this. Who is the user. User 1 or probably not user. Skip this record. Without indices this is how it as being trained from a database. So not rows and columns inside memory. They're all sequential. One after the other. That's why we have an index typically order that number has an index and when that is an index on a table a separate database is created. Just for order of and any time you say give me Ardern number one it doesn't go to all the rules one by one sequentially because that is an index. This index table basically gives you a quick pointer to the exact address where they can find that row. So if you look at are the number 7 0 0 0 0 1 2 3 this points to the address where exactly that is located. That's the beauty of Benedix lovely isn't it. When you have an index on a column it basically points up an exact location where that Dallasites So basically an index is used for random access on a column the size of this table would be 1 million going sequentially one million times for each record is a huge deal. And that's why indexes are created or indices are created on key columns. Order number. Sure you don't want to do them. Creation date right date is always so typically 200 maybe order type not even order. Maybe use something like that indices are created on key columns that are accessed randomly. Most of the time now why don't you create indices and all columns creating an index is easy. It just clears one more table but updating an index is an expensive operation because every time you create a new record for example a new record is created order number seven thousand seven million. Do you want a new order has come in. You update this rule along with updating the index. Ok so many and 21 and this points out some row in their memory. So that's an additional operation that has to be perform every time this table is updated so that if big columns in that table would be AK If you want to maintain indices on 250 columns apart from making an entry in the main table you have a bid 250 tables. That's crazy isn't it. So you only maintain indices on key columns. Most important ones that are accessed randomly. Most of the time. And then this is not just on the main table the AK all the other aggregate is also needs in nisus. So basically because of these aggregate tables it results in a huge database size in the large of the database the larger indexes or indices. So.