Udemy

Index Full Scan

A free video tutorial from Oracle Master Training | 175,000+ Students Worldwide
Oracle Architect & Best Selling Instructor
Rating: 4.5 out of 5Instructor rating
7 courses
177,679 students
Index Full Scan

Learn more from the full course

Oracle SQL Performance Tuning Masterclass (2022)

Become an Expert on Oracle SQL Tuning and Solve All The Performance Problems of Your SQL Queries and the Database!

19:28:29 of on-demand video • Updated December 2022

Learn How to Solve Critical Performance Problems with Step by Step Approach!
Learn Advanced Indexing Techniques for Ultimate Database Performance!
Learn How to Use Various Performance Tuning Tools for In-Depth Assistance!
Maximize the Query Performance by Using Advanced Tuning Tecniques!
Learn Oracle Database Architecture by "Tuning" aspects.
Identify and Optimize the Performance of poorly performing (bad) SQLs.
Understand SQL Tuning Terminologies
Learn How to Create "Better" Queries
Learn How to Prioritize your SQL Tuning Efforts.
Learn the Details of How an SQL Code is Interpreted
Learn Useful Hints to improve Performance of your Queries
Learn & Improve How to Use Oracle Optimizer and Execution Plans Well
Various Tips and Tricks to make the Oracle SQL queries run faster.
Oracle Performance Tuning on both Oracle11g and Oracle12c Environment.
Learn Efficient Schema Design
Lifetime Support from the Authors.
Weekly Quizzes & Assesment Tests
Working materials and always up to date!
English [Auto]
Hi. In this lecture, you will see the index full scan access path in details. We know that index is create index entries for all the rows. I mean, if our table has 1 million rows, our index will have entries for all the rows. Actually, this does not mean that if we have 1 million rows, we will have 1 million entries. An entry can store multiple row IDs. Basically in any index, index entry is includes all the raw IDs of the related table. So in the end, 1 million rows will be indexed somehow. So you may think that why the optimizer performs an index full scan. Then instead we can go to the table and read from the table directly because as you know, reading from the index also needs to read from the table in most cases. So we will perform two operations respectively and this will increase the cost. Actually, it is not true for all cases. Sometimes reading the whole index and then reading the whole table with the related row IDs will be faster than reading directly from the table. So how can that happen? If our query has an order bi with the indexed columns, even if we read all the data of a table reading by using the index will be faster than reading directly from the table because the old operation is a really costly operation, and if we eliminate that, our code will return the result faster. And since our indexes already have the keys in the sorted order, if you read by an index and read the rows by the index row IDs, it will be faster than reading the table and then sorting the rows. Second, if our query has a group by clause and that group by clause consists of only the columns of an index, it will perform an index full scan. It will be faster for most cases because since the columns are ordered in the index, it will group the rows much easier. So instead of reading a whole table and trying to group the mixed values, it will be easier to grow the ordered values. So it will perform index, full scan and group them easily. The last condition that the optimizer chooses to perform index full scan is if the query requires a sort merge join. In short merchants, the optimizer will consider the index full scan because using an already sorted record will ease its job. All right. So in all these conditions, the optimizer performs an index full scan to improve the performance. But in order by and submerged in conditions, the order of the index columns must match with the indexing order. I mean, for example, if you created your index on employee ID and first name sequentially, if you write first name first, it may not perform an index full scan, but will perform a full table scan. Or maybe it will perform an unnecessary sort operation with the index full scan. But in a group by clause, there is no need for that. I mean, the order of the columns in the group by clause is not so important. Great. Now let's open up the SQL developer and make some examples about that. In the first example, we query queried for all the rows and used an order by with department idea column. Dept. The idea column has an index on it. Now let's get its execution plan. As you can see, it's power from that index, full scan. So if we use order by clause with indexed columns, it may perform an index, full scan. In the next example, I used another index. In the employee stable. Last name and first name columns are indexed together. The order is last name and then first name. So in this example, I typed only the last name column in the order by clause. Let's get its execution plan and see what happens. As you can see, it did an index full scan. And since all the columns are already in the index, it did not go to the table for the rose. It ran from the index directly. So its cost is only one. In the second example, I type the first name, which is the second column of the index. So let's see, it's execution plan. As you can see, it also performed an index full scan, but made a solid operation after that because the index is sorted based on the last name and the first names are sorted within the same values of the last name values. You should already know that. And because of this whole operation, its cost is two. In the next example, I typed both columns of the index with the wrong order. I mean, I typed first name first, but in the index order it is the second column. Now let's see its execution plan. As you can see, it did an index full scan but sorted after that and its cost is two because it used the same execution plan of the previous query. In the next query, I type the order of the index correctly. Now let's get its execution plan. As you can see, it performed an index full scan like the second query. In the next query, I typed a different column than our index has. Let's see its execution plan. As you can see, it did a full table scan. So all the columns should be of one index for the index. Full scan. In the next example, I typed Star instead of indexed columns in the select clause. Now let's see its execution plan. As you can see this time it did a full table scan. But in the first example, when I used Select Star, it did an index, full scan. Now it did a full table scan. Yes. As you can see, sometimes the optimizer considers a full table scan as the best choice. Like the difference in here. Maybe in your database it can select a different execution plan. If such thing happens, do not worry. This is normal because cost changes based on the power of your hardware sometimes. All right. In the next example, I use the group by clause with a non indexed column. Let's see its execution plan now. As you can see, it is a full table scan. But in the next example, I use the department idea column in the department's table. It has an index on it. Now let's get its execution plan. As you can see, it is an index full scan since we used the indexed column in the group by close. In the next example, I added another column to the group I close. Both columns have indexes on them, but in different indexes. So let's see the execution plan of this query. As you can see, it is a full table scan. So even if all the columns have indexes on them, they must be of the same index. Otherwise it will not perform an index full scan. Great. The next example is about the sort more join. Let's get its execution plan and talk on that. As you can see, it performed an index full scan when the more joint needed. You haven't seen the more join yet, so only to explain that deeply. Just know that if a merge sort joint occurs, the optimizer will consider using an index full scan if the index column is used in the clause. Since we used the department idea of the departments table, it used its index with the index full scan access type. Great. In this lecture you saw when the optimizer considers the index full scan access type, if your query performs a full table scan and if that table has indexes, you can consider modifying your query to let the optimizer use an index full scan. If you don't have any better option, it will increase the performance in so many times. All right. This is the end of this lecture. See you in the next lectures.