The Complete Course from ExamCollection industry leading experts to help you prepare and provides the full 360 solution for self prep including 1z0-062: Oracle Database 12c: Installation and Administration Certification Video Training Course, Practice Test Questions and Answers, Study Guide & Exam Dumps.

Module 4: Indexes

1. Indexes Part1A

So one of the things about Oracle is that it prides itself on being the fastest database system in the world. And just personally, I think that's probably true. Oracle is a very, very fast database system. If you just have a few tables, then it's not that big of a deal because any database system can be very fast with just a few tables. But once we get up into the terabyte level, and Oracle is one of those databases that can hold massive, massive amounts of data, then that's the real question of whether you're going to have the speed that you need. Especially when you're transferring speed across the Internet, transferring speed across the network, trying to move through several servers, and other such things, we really need to find a lot of speed. And Oracle accomplishes that. An Oracle has a lot of abilities to accomplish things quickly, especially moving a lot of data. And we've seen some of these; we've seen how the RAM on the server is carved up into a lot of different sections so that it can be used to place things in such a way that we can return data to our users very, very quickly. Not only that—and we don't really see that in this course—but we can tune Oracle in a way that's maybe more efficient than maybe any other database system. We have more capabilities for tuning than almost any other database system, maybe any other database system that's out there. We have ways of tuning Oracle in such a way that we can speed it up in a way that meets our system, depending on what type of server we're running, what type of network we're running, and whether we're running across the Internet. We have ways of tuning Oracle, moving memory around, and so forth, so that we can really make Oracle fast, especially with gigantic amounts of data. I mean, like I said, if you're just dealing with a few thousand rows in a few tables, no big deal. But if you start to deal with millions of rows and a couple of hundred tables, then we really need to decide how we can move data to our users very, very quickly. And yes, computers are fast these days. Computers have a lot of memory, and computers have very fast hard drives. But we don't want to rely on that. We do not want to rely on hardware. Hardware is great. Hardware will get us there. But hardware isn't the answer. We've got to rely on ourselves to tune our databases in such a way that we get the most performance out of them. And I think that Oracle allows you all kinds of different ways in which you can get performance. Remember, the Oracle database system goes back a long way. It goes back in such a way that there just wasn't a whole lot of hardware. I mean, the computers were very slow when this database was created, and it needed a lot of performance out of some very, very poor hardware, out of some very, very poor network transferability. And none of this has gone unnoticed all the way up to Oracle Twelve C. Oracle Twelve C has not just sat back on its laurels and said, "We're incredibly fast, but the hardware has now caught up to us, so we don't need to worry too much about being fast anymore." Oracle has continued to try and be the world's fastest database system. And I think many, maybe most people, would agree that it is the world's fastest database system and that it can transfer an awful lot of data across the network. But what's more interesting about it is that we, as DBAs, have incredible abilities to tune our database in such a way that we can make it even faster. It's incredible to me the number of things we can do to the database to make it faster—to make it faster in the area of certain tables, to make it faster in the area of transferring things. Like I said, across the network, there are just so many things that we can do in terms of tuning Oracle. So this isn't the performance tuning class, but all along our learning curve of learning about Oracle, we always need, as DBAs, to keep in mind that performance tuning is one of our major goals. Our goal is to transfer data to our users, and we need to keep it available. That's the term of art that we use: high availability. That means all of our data must be available to the users when they need it and as quickly as they need it. We don't want it, and we've all seen it: staring at a blank Web page, waiting for things to load. Users have a very short attention span when it comes to looking at Web pages. We need to set up the database in such a way that that webpage loads almost instantly, if not instantly. And Oracle is a system that allows us to do that, because Oracle is an incredibly fast database system.

2. Indexes Part1B

One of the techniques that we're going to use is indices or indexes.I love to say the word "indices." I think originally it was supposed to be indices, but now everybody says indices. So the word "index" has crept its way into the English language. So I'll say indexes. I think that's what most people say. No? So indexes are purely a technique for speeding up the database. That's all they are. Like I said, we have a lot of methods within Oracle to speed up the database. Oracle is in many ways a hardware-agnostic system. Yes, add hardware if you want to do that. Add hardware to make things If you can add a faster processor—a quad processor or something like that—do so. Add more RAM—absolutely do that. All that stuff is fairly inexpensive these days, and it's a good investment and will make the database much faster. But there's nothing like tuning the database properly. And to me, that kind of stuff is fun. I like to try and make the database a lot faster, and there are lots of things we as DBAs can do and even lots of things that developers can do in order to make the database faster. One of the techniques we can use is to index tables and some of our other structures. So in this section, I want to look at indexes: how we can use them, when you should use them, when you shouldn't, and how they can be used to make our database faster. Now let's talk about indexes. Indexes are used to speed up data retrieval. That is their whole meaning in life. That's what they live for, and that's what they want to do. They want to make data retrieval faster. And there are a number of techniques that they can use in order to make data retrieval faster. In fact, there are a number of indexes that you can try to use and put on your columns in your tables that will allow data to be retrieved much more quickly. You put indexes on columns. In particular, you mostly want to put indexes on columns with large amounts of data with a wide range that aren't updated frequently. So here's what I mean. Obviously, if you've got a table with 150 rows, there's probably no point in putting an index on it because that table is probably small, and it's probably about as fast as Oracle can get it. You can always try putting an index on any column in any table and see what happens. And you need to decide, because Oracle can't decide for you; it just doesn't know, though there are tools that you can use to try and figure this out. But generally, you'll know where to put the indexes. Indexes go on columns, and you put indexes on the columns that are most often searched on.So you look at your users' where clauses, you look at your developer's where clauses, and if it's where employee number equals something, then you think, "Hey, if I'm seeing that in a lot of the where clauses, maybe I ought to consider putting an index on that particular column, say employee number." If the employee number is getting searched a lot of the time, then great; that's a good candidate for you to put in an index. But there are some other rules, and those are the ones that I just mentioned here. You just don't want to put an index on any column because there are a couple of other times when indexes will work very well and sometimes when they won't work very well. And I always use the example of a phone book. In a large city, we have lots and lots and lots of data that is different, including lots and lots of different last names. Now if you lived, say, in a small town, which still might have a fairly large phone book, you might still have 200 names—sort of a small town—and 20,000 names in the phone book. That's great. You may still want to put an index on that last name column because how are people looking things up? Maybe in your online phone book they're looking people up by last name. Fair enough. So let's say we have a whole bunch of different last names. That's a good point upon which you ought to consider putting an index. The only thing you can do with indexes is put It's not the only thing, but the best thing I think you can do with indexes is go ahead and put the index on the column, let the searches run, and see if things get sped up. And if things are actually sped up by what you've done, great. If there's no speed difference, or if you can't see any speed difference, or if things actually slowed down because of the index, which can happen, take the index off. Indexes use up RAM; indexes use up processor resources, so they have to be worth it. There is a cost for indexes. Put them on the column, see what happens, and see if they work. If they work, great. But you've got to use them in the right place, so it takes some know-how to use indexes. You can't just put them willy-nilly wherever you want to. Indexes will slow down the database. This is a great superpower; indexes are a great superpower, but you've got to use them for good and not for evil. So you've got to know what you're doing when it comes to indexes. There is an art to indexing, and people that don't take a course like this and just go in and throw indexes all over the place can really make things worse, so one of the things about Oracle is that it prides itself on being the fastest database system in the world. And just personally, I think that's probably true. Oracle is a very fast database system. If you just have a few tables, then it's not that big of a deal because any database system can be very, very fast with just a few tables. But once we get up to the terabyte level And Oracle is one of those databases that can hold massive amounts of data. massive amounts of data. Then that's the real question of whether you're going to have the speed that you need. Especially when it comes to Internet transfer speed. You're transferring your speed across the network. You're trying to move through several servers. Things of that nature. Then that's when we really need to try and find a lot of speed. And Oracle accomplishes that. And Oracle has a lot of abilities to accomplish things quickly, especially moving a lot of data. And we've seen some of these; we've seen how the RAM on the server is carved up into a lot of different sections so that it can be used to place things in such a way that we can return data to our users very, very quickly. Not only that—and we don't really see that in this course—but we can tune Oracle in a way that's maybe more efficient than maybe any other database system. We have more capabilities for tuning than almost any other database system, maybe any other database system that's out there. We have ways of tuning Oracle in such a way that we can speed it up in a way that meets our system, depending on what type of server we're running, what type of network we're running, and whether we're running across the Internet. We have ways of tuning Oracle, moving memory around, and so forth, so that we can really make Oracle fast, especially with gigantic amounts of data. I mean, like I said, if you're just dealing with a few thousand rows in a few tables, no big deal. But if you start to deal with millions of rows in a couple of hundred tables, then we really need to decide how we can move data to our users very, very quickly. And yes, computers are fast these days. Computers have a lot of memory. Computers have very, very fast hard drives. But we don't want to rely on that. We do not want to rely on hardware. Hardware is great. Hardware will get us there. But hardware isn't the answer. We've got to rely on ourselves to tune our databases in such a way that we get the most performance out of them. And I think that Oracle allows you all kinds of different ways in which you can get performance. Remember, the Oracle database system goes back a long way. It goes back in such a way that there just wasn't a whole lot of hardware. I mean, the computers were very, very slow when this database was created, and it needed a lot of performance out of some very, very poor hardware, out of some very, very poor network transferability. And none of this has gone unnoticed all the way up to Oracle Twelve C. Oracle Twelve C has not just sat back on its laurels and said, "We're incredibly fast, but the hardware has now caught up to us, so we don't need to worry too much about being fast anymore." Oracle has continued to try and be the world's fastest database system. And I think many, maybe most people, would agree that it is the world's fastest database system and that it can transfer an awful lot of data across the network. But what's more interesting about it is that we, as DBAs, have incredible abilities to tune our database in such a way that we can make it even faster. It's incredible to me the number of things we can do to the database to make it faster—to make it faster in the area of certain tables, to make it faster in the area of transferring things. Like I said, across the network, there are just so many things that we can do in terms of tuning Oracle. So this isn't the performance tuning class, but all along our learning curve of learning about Oracle, we always need, as DBAs, to keep in mind that performance tuning is one of our major goals. Our goal is to transfer data to our users, and we need to keep it available. That's the term of art that we use: high availability. That means all of our data must be available to the users when they need it and as quickly as they need it. We don't want it, and we've all seen it: staring at a blank web page, waiting for things to load. Users have a very short attention span when it comes to looking at Web pages. We need to set up the database in such a way that that webpage loads almost instantly, if not instantly. And Oracle is a system that allows us to do that. because Oracle is an incredibly fast database system. One of the techniques that we're going to use is indices or indexes.I love to say the word "indices." I think originally it was supposed to be indices, but now everybody says indices. So the word "index" has crept its way into the English language. So I'll say indexes. I think that's what most people say now. So indexes are purely a technique for speeding up the database. That's all they are. Like I said, we have a lot of methods within Oracle to speed up the database. Oracle is in many ways a hardware-agnostic system. Yes, add hardware if you want to do that. Add hardware to make things If you can add a faster processor—a quad processor or something like that—do so. Add more RAM. absolutely do that. All that stuff is fairly inexpensive these days, and it's a good investment and will make the database much faster. But there's nothing like tuning the database properly. And to me, that kind of stuff is fun. I like to try and make the database a lot faster, and there are lots of things we as DBAs can do and even lots of things that developers can do in order to make the database faster. One of the techniques we can use is to index tables and some of our other structures. So in this section, I want to look at indexes: how we can use them, when you should use them, when you shouldn't use them, and how they can be used to make our database faster. So now let's talk about indexes. Indexes are used to speed up data retrieval. That is their whole meaning in life. That's what they live for, and that's what they want to do. They want to make data retrieval faster. And there are a number of techniques that they can use in order to make data retrieval faster. In fact, there are a number of indexes that you can try and use, that you can try and put on your columns in your tables, that will allow data to be retrieved much more quickly. You put indexes on columns; in particular, you mostly want to put indexes on columns with large amounts of data with a wide range that aren't updated frequently. So here's what I mean. Obviously, if you've got a table with 150 rows, there's probably no point in putting an index on it because that table is probably small, and it's probably about as fast as Oracle can get it. You can always try putting an index on any column in any table and see what happens. And you need to decide, because Oracle can't decide for you; it just doesn't know, though there are tools that you can use to try and figure this out. But generally, you'll know where to put the indexes. Indexes go on columns, and you put indexes on the columns that are most often searched on.So you look at your user's where clauses, you look at your developer's where clauses, and if it's where employee number equals something, then you think, "Hey, if I'm seeing that in a lot of the where clauses, maybe I ought to consider putting an index."

3. Indexes Part2A

So one way that we might speed things up is by ordering by last name instead of by natural order. That's a no brainer.And if we do that, how about this? If we're looking for the last name Wayne, why do we have to go through ABC? We don't have to do that. We don't want to do that. It's slow to look through all those rows. Let's go straight to the W and eliminate all the other letters. And then let's go straight to the WWE and then eliminate all the whatever other WWE.Let's eliminate all that. So we don't even have to look at those rows. We can narrow it down very quickly, eliminating a whole bunch of rows. And that's the real superpower of the index. It's not that the index can search any faster. I mean, the CPU still works at the same speed; the hard drive still spins at the same speed. The index has the ability to throw away data. That's the magic of it. It just throws away data. I found the W's; now I don't have to look at 25 other letters. I can just eliminate those and ignore them. Now I'm looking for A's last name in the waves. Now I can eliminate we and whatever else. Now I've got W-A-Y. Now I really narrowed it down. And if you get a name, the last name will have some unusual spelling. It may be the only one that can be incredibly fast. Imagine if you have a 10 million-row table and you're looking for Zozinsky or some unusual last name. If we put an index on the last name column, Zozenski may be the first row that we find. Imagine how quick that is, as opposed to looking at all 1 million rows or all 10 million rows. So that's how indexes work. So you can see if you had, let's say, just three things in an index—we just had, say, Atlanta, Orlando, and Miami—that's really not going to be a very fast index. If the user is searching for Miami, one-third of those million rows all say Miami. It doesn't help us a lot, but it might help us in the fact that it knocks out two thirds of all the rows. That's something; it'll speed things up a little, but it's not as good as an incredibly large number of very unique rows. When putting an index on a column, you really want to look for very unique data. So just bear that in mind. And you really want to look for large amounts of data. You don't want to put There's no sense in putting an index on a 150-row table because a 150-row table is a quick search anyway. The other thing that happens—I can't remember if I mentioned this or not—but the other thing that happens with indexes is that, to the extent available, that newly ordered table will move into RAM. So we're not worried about searching on the hard drive, we're just searching through RAM. And of course, that's a really, really fast thing to do. And there are a lot of other ways. We've talked about a couple of them. All recent searches go into Ramanya, so that makes them faster. We know that that always happens within the Oracle database system. And the most recently used searches will stay in RAM. The last recent used search stays in Ram underthe theory that it's going to get used again. You're going to continue to search for the same things. But another way to force a search into RAM is to use the index. Okay, let's take a look at some other characteristics of the indexes. Index, indexes. Here's a warning about indexes. Now, indexes speed up queries because we're now narrowing down what we want to search for, but they may slow down DML. So if we're inserting, updating, or deleting, especially if we're inserting or updating, the index may need to get rebuilt. Because if we're inserting a new row, let's say we've got all the cities or all the last names in alphabetical order, and we insert a new row, now we have to find out where it goes in alphabetical order. It just doesn't get stuck on to the end in natural order, which is what normally happens when you insert a row. Now this new row has to find out where it fits, and it's possible that the whole index needs to be rebuilt. So if you're inserting on a table for a significant amount of time, rethink the index; it may be a bad idea. Updating may be a bad idea. Deleting is not so much of a problem because deletes are always going to be fairly quick, especially if you're deleting based on, say, a last name and your index is in last name order. But if not, if you're deleting based on, say, the first name and you're in last name order, you may slow things down a little bit, because now you're not in natural order anymore. So what happened? I mentioned this a few minutes ago. What happens is that your data will go into RAM. If there is enough RAM and it is in the reordered format, it should be reordered on the index column. So if you're ordering by last name, then you're going to get everything in order by last name. And here's another clue, another idea—I shouldn't say clue. It's another tip. Put your indexes in a different table space so thatthe main table space where all these other sorts andqueries and things are going on, put the index offin its own table space so that the processor isnot working as much on that table space. All these things aren't happening like crazy. That way, it's sort of by itself. The processor can concentrate on that particular index without worrying about all these other things also happening. Indexes should always go into their own table space. You may even create a table space called,let's say you're in the sales table space. You may want to create the sales index tablespace, and it's going to contain nothing but indexes. So those are just some important things to remember about indexes. Let's talk about how we actually create them. Or before that, let's talk about the different types of indexes that are available. Oracle will do this for you. Oracle will decide what type of index is going to be best for your type of table. So you don't need to worry too much about this when you build the indexes. But here are some of the types that are available. These are the main types that are available to you. There are others. And like I said, you don't have to designate the type of index when you're creating them. Oracle can do this, but let's take a look at what types are out there and available within the Oracle system. The most common type is the bee tree. And this is what I've been talking about. This is where we know what column we're going to be indexing on, which is what column we're typically searching on. By the way, just as an aside, it is possible to put indexes on more than one column. You could try searching by last name or comma-separated first name. So two indexes on a huge database may be a great idea. So a B-tree index will do just what I was talking about. That is, it will hone in on the row that you're looking for. So if you're looking for a last name, the last name that you're looking for is Cogburn. It will go to that branch of the tree, the Cogburn branch, and it will ignore all the other branches. That's how the Beatri Index works. the reverse key. Okay, so what happens with the reverse key is that this is when you're doing a primary key search, which is a very, very common search. Why would you be doing a primary key search? Well, that's when you're going to do a join, right? That's when you're taking the primary key, where there's only one primary key, a unique primary key, probably just an integer, and matching it to foreign keys, a group of a bunch of rows in another table. So what the reverse key does is attack the table from two sides. One, it starts searching for the key that you're looking for from the top, which is going to be in order. Primary keys generally go in order, and then it also attacks them from the reverse direction. As a result, it searches for the key in reverse. So it's looking for the key twice as fast. If you're looking for a primary key number, 1231 search is looking for one, two, and three. The other is searching for three, two, one. So you get twice the search. That's basically how the reverse key search works. When you double-search on the table, you're looking for things from both ends. And then lastly, the bitmap index, which is kind of like zipping the data up just like a bit map zip, So the data, the table itself, gets zipped up, placed into RAM, and it's smaller, so it's easier to find because it's just smaller because it just basically zips up the table. It also zips up your query. So if you have a lot of different queries, you're constantly searching for, say, a book in your 10 million-book library, and it's a different book each time. This will not help you as much because it's still searching through all the zipped-up records. However, if you frequently search for the same book, it will recognise the zip code; remember, we got a lot of zip-up data, so the data does not appear to be searching for Hamlet right now. It looks like a bunch of code—a bunch of bits and bytes. So the problem is, your search has to convert the word Hamlet to a zip code. That takes a fraction of a second, and a fraction of a second is a long time. But if it already knows what the word "Hamlet" looks like in bits and bytes, or really in zeros and ones, then it can search through the zipped file very, very quickly. So if you've got a lot of unique data and this data is searched through very often, then you might want to use the bitmap index. Those are the three most common indexes. The default is the BTE index, and that's the one that you'll use almost all the time. And the BTE is the one that eliminates branches. That should be the fastest index because it is literally knocking out the data that you don't need as it searches. And if you can do it without searching for data, that's going to make things much, much more quick.

4. Indexes Part2B

Let's talk very quickly here about the B tree index. Obviously, it's best for a large number of unique values. So if you have 10 million books stored in your database, but they all have a different title, you're searching on the title column of that screen for a B-tree index because it's very good at knocking out the data it knows not to search for; it doesn't search on the data; it eliminates, and it eliminates very, very quickly. Here's how we would create the beatry index: Create a unique index and give it a name. Index my table by last name. We're putting an index on my table. That's the name of the table, whose last name column has that name. So that's how I named it. And here we are putting the index on the column itself. So we'll say paren's last name on my table. So now I've created the index, put it on the table, and specifically on the column. in the table. Remember, indexes don't go on tables, they go on columns. because we do our searches by the column. We're looking for the data within that particular column. Indexes are all about searches. We search by the column. If all of your searches are quick and you're happy with them, your data is being returned in a fraction of a millisecond. Good. Don't worry about indexes. Unless you're taking the test, you may never use an index in your database. If you're a small bookstore on Main Street and you've got an inventory of 1000 books and 50 or 60 customers, you'll probably never use the index. Just get past the exam and then never worry about indexes until you go work for some giant bookstore chain, in which case you'll start using the index. It's a great thing to have. It really speeds up your searches, and it's not that difficult. You can see this is one line of code, and that's it. One and done. That index is there. And what's your next step? Are your searches faster? If the searches are faster, great job. Well done. Call it a day. Buy some cake to reward yourself. If you find out that the searches arenot faster, or if you find that thesearches are actually slower, big deal. Drop the index, try something else, or live with it. There are many, many performance-tuning tools that you can use in Oracle. We don't cover them here in this class, but there are lots of performance tuning tools you can use to make the database faster. But from a DBA perspective, this is a great tool to use. if you want to take a look at the indexes that you've created in your database. Of course, we have data dictionary views available for that. Here's one of them. User index columns are user ID columns that will tell you the indexes that have been put on different columns. If you want to take a look at a specific table, like what indexes I put on my table, I know I put some on there. I can't remember what they are. Here's the query. Choose stars from user-end columns where the table name is my table or whatever your table's name is. It's an easy data dictionary view that you can use to find out what indexes you put on a particular table. Want to change something on an index? You probably won't do this very often because there are such simple structures to create. There are not a whole lot of parameters here to change. But anyway, it's easy to do. Alter index I my table, I being index. You don't like that because that doesn't meet naming conventions. That just tells you what table it's on, not what column it's on. And indexes always go on columns, so let's fix that. Alter index. I renamed my table to I am MyTable's last name. A much better and more descriptive name But then we discover it's not working so well. It's not speeding up our searches. We've gone through some performance tuning; we've looked at the graphs and what's happening. Yeah, that query is just not any faster. There's no sense in moving it into RAM. Let's not take up RAM space if we don't have to. We don't have that much RAM on this particular server where this table space sits. We also have this extra table space because we want to add indexes and other table spaces. not the same as the rest of the database. It got its own table space. That's a waste of resources. We don't like it. Let's just get rid of that index. easy to do. Drop index. I my table last name, justlike we drop almost anything sometimes. Now, remember, let's go back for a moment. I'll review this, and if I'm over reviewing this, I'm sorry, but I want to make sure that you understand how indexes work. So an index is a reordering of a particular column, or maybe columns, if we use multiple indexes. So we have reset the entire ordering of a particular column. Say it's the last name column we're going to get. The whole table is going to be reordered. It will proceed from natural order, from Ms. Arbor all the way down to Mr. Zebra, and it will settle in Ram. Now remember, on indexes, we don't want to do a whole lot of DML, or we don't want tables that have a whole lot of DML. And why don't we? Because it's hard to insert, update, and delete when we've taken the whole table out of order and have to insert, update, and delete into things in the middle. We usually insert at the end of the natural order, insert, insert, insert, and just add those things to the bottom. Well, now we've got to insert them in alphabetical order. Sometimes Oracle won't do that. Sometimes it just doesn't have time. It'll just insert itself at the bottom. Yeah, yeah, I'm not kidding. It'll just insert at the bottom, which is out of alphabetical order, if alphabetical order is what we're indexing on. So you may have a million rows, and the next day you add another 500 rows. They're all stuck at the bottom, and you're thinking to yourself, Hey, wait a minute. The whole idea was that we wanted a column in alphabetical order, and now we've got all these inserted rows. They're at the end; they're not in alphabetical order. What's the dang deal, guys? Okay, that can happen. If that happens, you'll want to rebuild the index. We know that as a general rule, don't put indexes on frequently used DML tables. But you know what? Break rules if you have to. It may be that this is a super DML table, but it is also a super query table, and it runs awfully slow if you don't add an index in there for those queries. So sometimes, it's just like you need that index even though there's a lot of DML happening in the table. Well, we have an answer to that. It's not the greatest answer in the world, but hey, it works. Especially if your database system gets slow at some point of the day, maybe at 300 in the morning or something, we can rebuild the index. And when we rebuild, we're just restarting the whole thing. We're just basically rerunning the index, recreating the index, which is going to restore the entire table. So how do we do that? We're going to alter the index at the bottom of the slide here. You can see we're rebuilding the index. We say alter index MyTable I don't know where this naming convention came from, but anyway, here's what it got called. Alter index. MyTable MyTable ID index That's what that means there. Change the index on my table; rebuild the index on MyTable ID. We're going to put it in the table space. Index table space, okay, so see what we're doing here—we're altering the index. What's the name of the index? MyTable MyTable ID index to rebuild it. So the whole index is going to get destroyed and put back together once again to alphabetize it, probably. That's probably what you're doing. You're also doing something else here, though. You're moving it to a different table space. In this case, you're moving it to the index and table spaces. index table space, sorry. So we're doing two things with our rebuild: with our Alter command, we're rebuilding it and we're moving it to a different table e moving itDropping things in Oracle is always easy. There's almost no exception to this rule. Dropping works the same. Drop the index for my table and my table ID index. We just drop it like anything else. Sometimes you may even want to. every single night, every single hour. I don't know what the schedule will be with your database. It might be common for you to drop the index and just rebuild it. Maybe every night, your database system closes at 05:00 and reopens at 08:00 a.m. the next day. One of the chores you might want to do every night is just to drop it completely and rebuild. It's kind of the same thing as running our alter command. Maybe that's just part of what happens during database maintenance, which happens every single night. You wouldn't want to do this by hand. It's one of the things you'd want to automate. Maybe that's just something that you do, but it's easy enough. Drop it and then recreate it. So indexes can be very critical. One of your biggest jobs as a DBA is not just to make sure that the database is working. Just about any DBA who knows the minimum about Oracle can keep the database working. Maybe it's not an emergency. And there are emergencies with the Oracle database system from time to time because users can do crazy things to your beautiful database, believe me. The difference between being a DBA and being a great DBA is not just keeping the database running, but keeping it running correctly. and indexes are one of the ways that you can do that. Good DBAs know how to put indexes on their columns, but more importantly, they know when to put indexes on their columns. Sometimes it's almost a supernatural skill. Sometimes it's almost like you can just feel from the database when it's time to add an index, and when it's time to drop an index, there are lots of tools that will help you with that. Sometimes it's just a supernatural feeling, and you will get that. Trust me, as you do it, you will get that. And when you do, it's fun, I think. And maybe I'm crazy, but I think being an Oracle DBA is a fun job. I think being an Oracle developer is a fun job too. I like them both. I love those jobs, and I love telling you about them. So when we look at indexes, we want to look at large tables with lots of different data. We don't want to see a lot of data in a column. We want to look at large, large tables, maybe thousands of rows maybe.And those rows have lots of different data—lots of non-repeating data. And probably these are tables that are accessed not so much for DML but for searches. Very often, when you see that those little psychic powers ought to go off in you and you ought to think, "That's the time for an index," So if you follow the rules that I've shown you, you will get this right. You will know when to put the indexes on, and you will know exactly where to put them. Your database will come.