INDEX SCANS IN ORACLE

The index scans are the table access paths used by the oracle optimizer to generate the best execution plan. There are different types of scans that are used by the optimizer, but it all depends upon the selectivity and the predicate we provide in our SQL queries.

By far the best way to access  a row is to get the data by ROWID which is stored in the block if we  know the exact row_id . But these ROWID are being changed as we perform some DML operations. So until we are sure that this is the exact ROWID we cant use the ROWID to fetch the data from the table.

There are other methods which are used by the optimizer to fetch the data from the table.

FULL TABLE SCAN in ORACLE

During a full table scan the optimizer scan/read all the blocks in a sequential manner and every row is examined whether its satisfy the where predicate in the query. The speed of the FTS depends upon the parameter .DB_FILE_MULTIBLOCK_READ_COUNT . Default its set to 1 but if we see that we are selecting the the whole data from the tables and we only need FTS we can increase it to 4 because the optimizer reads the data sequentially.Its not necessary that every time a FTS performed by the optimizer it’s a bad thing but it depends upon the amount of data where doing a FTS cost less while using a Index cost is more. There are other factors where Optimizer do  FTS like:

• Lack of index
• Small Table
• Stale Stats
• Selectivity and the predicate used in the where clause.

INDEX UNIQUE SCAN in Oracle

Index unique scan used by the optimizer when the output of the query is utmost is one row. For unique scan to work the index should be created on primary or foreign key and the index column must be used in where clause or in other words we can say that primary key constraint must be specified with the equality condition.

INDEX RANGE SCAN in ORACLE

Index range scan is used by the optimizer when the result consists of multiple rows. If the data we quried is bounded from one side or both side or the query consist of a group or ordered by Index scan can be used by the optimizer. The group by or order by clause should be used only on the indexing columns . When the optimizer uses  Index range scan it does not do any sorting because all the data is already available on the Index .

INDEX FULL SCAN in ORACLE

Index full scan is used by the optimizer when it reads all the data from the index and then it reads the data from the table . Optimizer uses the Index full scan when group by or order by is used only with the indexed column and if query requires a sort merge join. Optimzer used Index full scan only when the order by predicates and index column should match sequentially. Just like Index unique scan full scan does not do any sorting .

INDEX FAST FULL SCAN in ORACLE

If the query request only the columns existing in the index then the optimizer used the Fast full scan . Hints can be used to force the optimizer to choose the fast full scan . Index fast full scan reads multiple blocks simultaneously in unordered manner so sorting will be needing whereas Index full scan reads data sequentially so no sorting needed for Full scan . Index fast full scan does not read data from the table .

INDEX SKIP SCAN in ORACLE

When there are composite indexes and if any of the index except the first one is used in the where predicate then the Skip scan is used by the optimizer .

In the next post i will be explaining all these scans with the examples .