Index Full Scan

Oracle Master Training • 100,000+ Students Worldwide
A free video tutorial from Oracle Master Training • 100,000+ Students Worldwide
Oracle Architect & Best Selling Instructor
4.5 instructor rating • 7 courses • 109,442 students

Learn more from the full course

Oracle SQL Performance Tuning Masterclass 2021

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

19:27:35 of on-demand video • Updated September 2021

  • 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] High in this lecture us here, the index fools can access part in details. We know that indexes create index entries for all the rules. I mean, if our table has one million rows, our index will how entries for old rows. Actually, this does not mean that if we have one million rows, we will have one million entries and entries can store multiple row IDs. Basically, in an index index, entries include all the raw ideas of the real leader table. So in the end, one million rows will be indexed somehow. So you may think that why the optimizer performs an index full scan then is that 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, ideas will be passed than reading directly from the table. So how can that happen? If our query has an order by 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 this whole operation is a really costly operation. And if we eliminate that, our code will return the result faster. And since our index is already have the cheese in the sorted order, if you read by an index and read to Rose by the index Royds, it will be faster than reading the table and then sorting the rose. Second, if al Qaeda has a group by claws and that group claws consist of only the columns of an index, it will perform an index foolscap. It will be faster for most cases because since the columns are ordered in the index, it will group the rules much easier. So instead of reading the 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 us to join. In joins, the optimizer will consider the index full scan because using an already saw 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 to try and sort out join conditions, the order of the index columns must match with the index in 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 pool table scan. Or maybe it will perform an unnecessary sort operation with the index will scan. But in a group, because there is no need for that, I mean, the order of the columns in the group clause is not so important. Great. Now, let's open up this developer and make some examples about that. In the first example, we queried for all the rules and used an order by the State Department idea column. Department, did you call him, has an index on it now let's get its execution plan. As you can see, it's power from that index foolscap, so if we use autobio closed with indexed columns, it may perform an index, falls can. In the next example, I used another index. In the employees table, 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 because let's get its execution plan and see what happens. As you can see, it did an index foolscap. And since all the columns are already in the index, it did not go to the table for the rose, it read from the index directly, so its cost is only one. In the second example, I typed the first name, which is the second column of the index, so let's see its execution plan. As you can see, it also performed an index full scan, but made a solid operation after that because the index is so 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 to. In the next example, I typed both columns of the index with the wrong order, I mean, I typed first name first, but 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 folded after that and its cost is two because it used the same execution plan of the previous query. In the next query, I typed 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 pool table scan, so all the columns should be of one index for the index falls can. In the next example, I type star instead of indexed columns in the select class. 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 start, it did an index full scan. No, 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 a thing happens, do not worry. This is normal because cause changes based on the power of your hardware sometimes. All right. In the next example, I use the group like laws with an unindexed column. Let's see its execution plan now. As you can see it, the pool tables can. But in the next example, I used 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 did an index full scan since we used the indexed column in the group Klaus. In the next example, I wrote 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 pool 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 scale. Great, the next example is about the sort Mauduit, let's get its execution plan and talk on that. As you can see, it performed an index full scan when the mercury needed, you haven't seen the mercury yet, so it only takes one that deeply. Just know that if a moonsault joint occurs, the optimizer will consider using an index full scan if the indexed column is used in the workplace. Since we used the department idea of the department's table, it used its index with the index full scan access type. Great, in this picture, you saw when the optimizer considers the index full can 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 other option, it will increase the performance in so many times. All right, this is the end of this lecture. See you in the next lecture's.