There are a couple of more items of importance with indexes that I want to share with you. Let me show you what I'm talking about. Indexes go into effect or they are used when the Where clause is utilized in a DML statement, the Data Manipulation Language. If I create a table, for example, and I call the table userinfo and I place in there two columns, one called userID, one called username and I save it like that. There I've got my userinfo table. When I describe the userinfo table, it lists me the two columns. It's not until I use a Select statement and I specify that I want the userID and the username from the userinfo table. It's not until I really used that Where clause right there, when I say username equals and I specify what I want the username to be. This right here is when the index would go into effect. If I have an index on the username column, it would be utilized at this time. I want to give you an example of that on when it works and when it doesn't, right now. I do not have an index at all on this table, not on the ID and not on the name. I'm going to run a command called Explain Plan For, and Explain Plan For, allows me to see how Oracle will construct the Explain Plan or the strategy for finding this data inside of this table. Now, mind you, I don't have any data in this table. Explain Plan will just tell me the strategy the Oracle engine will use to do it. To determine how to find in this case, Adam and the username. When I press "Enter", it says it's explained. Once it's explained, I can use the Select star from table keyword specified dbms_xplan and then do a display. Right now when I do this, it shows me that it's doing a table access full. That means that it's going to look for the username in the entire table, the Select statement. Here's my filter, username equals Adam. The way this looks, the table access full means is going to run a scan in the entire table from top to bottom, looking for the username Adam, and this is a very slow index. Well, it's not an index because there are no indexes inside of this table that's why it has to do a full scan, which is what this is. Let me create an index. I'm going to call the index username and I'm going to create it on userinfo, username. I've got the index created as you can see. I've got a created on username column. For the userInfo table, I call the index username. Now that I've got this, let me run the Explain Plan again. I'm going to clear this screen to keep things nice and neat. When I do the Explain Plan For, I'm going to make Oracle run an Explain Plan or a strategy that it will use to look for the username Adam, in the userinfo table after I have created an index. I'm going to run this and I'm going to look for the Explain Plan. Now, when I run the same exact statement looking for the Explain Plan, it shows me that it's doing table access by row ID batched in the userinfo table and it's doing an index range scan for username. It recognize that I have an index called username. Here's my filter. See that access username equals Adam? This plan table execution right here tells me that in this particular instance, it, Oracle, will use the index for username doing an index scan to find the username equaling Adam. Notice, right now, I don't have an index on the userID column. When I do userinfo, I have a userID column. If I try to search for something by user ID, let me show you. If I do an Explain Plan For, and I say user ID equals 5 and then I run the Explain Plan. Notice it's doing a full table scan again because it is not using a userID equals 5 because there is no user ID column that has an index. See the filter, versus and access? This is doing a full table scan looking for userID equals 5. As soon as I create an index. Let me clear the screen again. As soon as I create an index, I'll call the index userID on userinfo userID, and then I run the Explain Plan and then I take a look at my table. Notice now, it's accessing based on the userID index, not doing a full table scan anymore. That's the difference on where indexes are used. Indexes are used exactly when you run the Where clause is when the indexes are search for by the Oracle environment by itself. If there is an index for the column you're doing a search for, it we'll find it and use it. You don't have to specify anything different on the Select statement. I run the Explain Plan because I wanted you to see the usage of an index versus not using an index as long as you specify the column that there's an index for.
