Hi. In this lesson, we understand what is an index? What is its importance? How do we create it, and some details related to index. To start with an index is a schema object. It is use it to make searching faster.

While we are not strangers to index because we have been using index in the context of a textbook, and this index is not different from that. If you see this diagram, you can understand how indexes actually used if this is the table called jobs, if I have an index created on that index is always created on a column. You may have multiple columns but in this case, I have index created on job ID. This is how you're index looks like the keys. That means the job IDs are arranged in the ascending order. Along with that we store the row ID, which is nothing but a unique ID given to each row of the table.

When user is saying he wants to look for a job Id like days, first or racquel will go to index will look for this value in the key as keys are in the sorted order. This can be done very quickly, we can look for this very quickly. And internally, Oracle uses something like a binary tree to store the keys. So we can very quickly locate the key and then find out the row ID related to that and jump to the table and look for this. Row ID, which is like you go to index in the textbook, locate that topic, pick up the page number and jump to that page, you know, that's going to be pretty fast. That's exactly what we do when we talk about indexing.

So, do we need indexes of course very badly. For two reasons we need indexes number one, index is also used to enforce uniqueness. Oracle is using index to enforce uniqueness. And also index is use it to make your searches faster. So it improves performance. It is also improving integrating and Aurora kale automatically creates indexes for every primary key and unique key because both of them are supposed to be unique.

It is achieved by creating index. But in addition to those pre created indexes, the indexes created by Oracle automatically, we can always create as many indexes as we want. And while you're creating index, you have to decide on which column you want to create index. So here is the command to create an index, create index followed by the index name, always give a meaningful name, followed by the columns of the table. And of course, the table name. Unique keyword is saying the index must be unique means the values in the index must be unique.

But if you just create an index without unique, then index can have duplicate value in the key now I'm creating a unique index on phone number column of employees table. This creates a new index with this name. And we don't have to do anything, or racquel will automatically use index if it fails, index is needed. That's very important. You don't need to ask Oracle to use index, or racquel automatically decides whether index is needed or not. And it will pick up the index.

Depending on the query, we will understand how exactly it works. So if you ever want to know what are the indexes available on a table, you can check it out by using user indexes. If you want to know about the columns related to the indexes, we can use user ind columns now We would like to create an index. So let's go to demo part of it. And I want to create an index here. So I'm saying create unique index.

And this is the column on which I want to create index. Let's go and create it. So it says index is created. But if I want to know what are the other indexes already available, I can say user indexes. And then you're going to get all the indexes, but I'm interested only in table employs This is the command. So we are getting all the rows related to employee table.

Apart from our index, which we created right now, these are other indexes already present. You can see there is a unique index and then a primary key index Then a few other indexes on name, job ID and so on. Some of them are unique indexes, some of them are non unique. So you have to find out on which column you want to such more frequently and create an index on that column so that searching gets faster. Also be aware index occupies extra space. So if you are not interested in searching on a column very frequently, creating an index on the column will be unnecessary burden, because it occupies space and not just a space whenever you update the table, like adding rows or deleting rows index is to be automatically updated.

And that's going to put more burden on database. So don't create indexes unless you really need to Such on the column relatively frequently. Okay, I created the index then is it used by Oracle? That brings us to a very interesting question when a racquel a does and does not use index or racquel does not use index in three different scenarios. Number one, when you don't use where class no index is used, because there is no point in using an index when I want to take everything from the table. Also, when you are not using a column on which index is created, you are using salary but there is no index on salary.

So you are not using any column on which index is present. So racquel does not use index in this case. The last one is where you are using In a column on which index is created, but you are not using the column as the T's, you are modifying the column in the query like you are converting that to uppercase in this case. So, can we know whether Oracle is using an index or not? Let us see that. So, I say select star from employees and when I say select star from employees, if I go run this I get all the rows.

Now, there is an option you see here there is an option and that is saying explain plan or racquel can explain the plan of its execution. Because every command is first parsed, parsing is where it will just check whether everything about the command is fine then racquel Welcome Find out what is the best way to execute the command. And it finds out execution plan. And it even stores the execution plan in memory, so that the same plan can be applied when the command is executed again, that saves a lot of time. So the execution plan is what I want to know. So if you press F 10, it is telling you this is the execution plan for this command.

And there you can see it says it is going to access the table full. All that you need to focus on is the table and then what kind of access we have full access. But if I say I want to access only one imply with id 120. Now let's go and ask for plan There you see a very important difference, it is bringing an index into picture and it is going to apply what is called as unique scan, because it knows it is a unique index. So, it will look for a value in the index and it is going to take much less time compared with full table access. And then if you are talking about salary greater than 5000 we don't have any index on salary.

So, when you go for explained plan, again the whole table will be considered and it is also telling you how many rows are getting selected. But if you are using a column on which index is present, for example, we have just created an index on phone number. If I say four numbers something it doesn't matter. Now go ahead and try this. It is saying it will use the index related to phone number. It's a it's a nice feature, isn't it?

Oracle decides which index to use, whether it is a unique index or a range index. If I go and then say, I would like to get something on last name, or job ID for example, if I say job ID, is it a programmer, now go and ask for it. It is saying it will use an another index, that is range scan. It is not talking about unique scan, it's called range scan. But the problem is if I change this to upper, if I try to convert the job ID to upper and then try to look for it. Again, the index is not used when index is not used, you're accessing the whole table, that's going to be very costly.

So, always make sure your queries are efficient and they can use the existing indexes. So that performance is improved. So you have to remember these three points, no index these use if there is no where class. Now index is used, if you refer to column on which index is not present, if you use a column on which index is present, but if you modify the column, then indexes again not used. So, try to write queries in such a way they can use existing index that makes the whole process faster. And then, if you fail very frequently, you are searching by converting last name to uppercase, then it makes sense to create an ad index based on the function.

This is called as a function based index. So I'm creating an index based on uppercase of last name. And then whenever you refer to upper of last name racquel, we'll consider that index. And then you can anytime drop an index. If index is no longer needed, you can drop it. whatever name you gave to your index give the same name here, you need to know the names or you can get it from user index system.

So this is how you can make your queries faster by using index. Just create index, forget it, or racquel will automatically decide whether to use index or not. Also, indexes are automatically updated whenever you make changes. to the table, because a racquel is taking care of updating index. Another important schema object is sequence sequence is used to generate numbers in a sequence. We want to assign numbers to implies departments, invoices, customers, and so on.

The process of generating numbers in a sequence is better accomplished using a sequence. Normally you try to find out the highest available customer ID add one to it that becomes the next customer ID. That can be done but it would have some issues in highly concurrent system where there are too many users trying to find out the largest ID and adding one there is a problem ability of before you get to insert the next ID, somebody else has already inserted it. So a better way to generate numbers in a sequence is to create a sequence. And a sequence is created using create sequence command. Very simple.

Just mention the name and specify from where you want to start it. If you don't mention anything about start, it starts with one. And then you can specify what is the maximum value, what is the increment value. And also you can mention whether you want to cache the values of the sake. caching values of the sequence can make accessing sequence faster, because what Oracle does, it'll bring a set of numbers from sequence and put them in the memory So that you don't need to access sequence again and again, in a very busy system caching numbers from sequence can improve performance. So this is the way you create a sequence.

I'm creating a sequence called department ID sequence. I want to start with the 10 and incremented by one, and I don't want to cache it, it's up to you. If you want to cache you can mention how many numbers you want to cache. Then, every time you want to insert a row into department's table, you have to give the name of the sequence followed by next to Val. This will bring the next value from the sake fence. In case you ever want to use the current value, let us say the next Val is 10 already used, but do you want to reuse the same value then you should use current Val.

This is what we use to access the value that was taken by the previous next to Val. So, you can take the value from the sequence using next value, and that can be used as the department ID like this, it is possible to create a sequence. And if you ever need to, you can even slightly alter the sequence. You can for example, change the increment value and a few other details. The sequence can be dropped anytime, but it does not affect the values which were already taken and used by our tables. And also another common misconception by beginners is they think a sequence is associated and bounded to a single table.

That's not the case. sequence is not specific to any table, you can take the values from a sequence and use it with any table you like. So if you jump to this and try to create a sequence, you can create a sequence. So we say create a sequence, just a demo one, so I say SC q one, and I'm not giving anything. So it starts with one, and it is going to increment the value by one, then I can say select sequence, one dot next Val from duel, and that's going to give me the next value, which is one. Again, if you execute, you get two and so on, like this.

And anytime if you want to get the same value again, you can go for sequence dot current value, and this time, you're going to get the next value and that will be taken Again using current Val. So that's how you can take the values from a sequence in a racquel. Thrall see, we have an option to use sequence as the default value for a column. So I can use sequence as the default value. So I say this is going to be the default value for ID. So if I don't give any value for ID in insert command, automatically the next value from sequence will be used, but if I gave a value that value is used.

So it could be a useful option in some cases, when nothing is given value should come from sequence, but if something is given that will be used, so this is the result of default value of a column, and we are using a sequence as default bye For a column. So these are the things you learned in this lesson, what is an index and how to create date. And when Oracle uses index. And when it doesn't, that's important, because you should try to create queries that use indexes to make things faster. And a sequence is used to generate numbers in a sequence, so that we can just get the value from sequence instead of following other techniques. And then we can drop a sequence anytime we want without affecting what was taken from the sequence.

And you can also use sequence in the context of a default value. Well, that's all for now about indexes and sequences. I'll catch you in the next lesson.