Oracle sql Interview Questions | 11 New Index Qustions

There are different types of table scans in Oracle. Index scans are one of them there are different types of index scans doing faster table scans compared to full table scans. But it depends on the number of records in the table. Therefore, oracle uses statistics before executing a query. There are index scan types like below.

• Unique Scan ( -> Index Unique Scan)
• Range Scan (Index Range Scan)
• Full Scan (-> Index Full Scan )
• Fast Full Scan (Index Fast Full Scan)
• Skip Scan (Index Skip Scan)

ROWID is the identification of the row in a table. There are a lot of index varieties like B-tree, Domain, Bitmap, Function-based index, and so on. oracle needs ROWIDs to fetch data from a table.

Hearing the name you can imagine this is going to scan the range of the index. The index range scan starts from the root block. Then Travers leaf blocks until find the relevant data.

Oracle creates a B-tree index. B-Tree is a Balance Tree So values are placed in balanced order in Oracle. That is good on high cardinality columns. Then Oracle traverses branches to find the leaf block. Once found the correct leaf block contains the required ROWID for physical location.

Leaf nodes contain a doubly linked list, so traversing through left and right is easy. Therefore, there is no need to come from ROOT every time. If the query searches for a range of values in the where condition, what happens in the index range scan Oracle?

first looks for the highest value or lowest value leaf nod in the b-tree according to the where condition, then traverses using a linked list and stops once comes into the next condition.

EG: where y between 20 and 30;

Oracle comes into the ROOT block, moves to branches, and reads. Then, identify the first leaf not corresponding to value 20. Then read only the leaf node or another leaf node if needed until it encounters key value 30 and then stops. Now, you have required ROWIDs between 20 and 30.

Then go to the table and fetch required values for ROWIDs. That can happen onetime process or multiple times according to the dataset. it depends on the optimizer; it depends on the explained plan and the behavior of the query.

db_file_multiblock_read_count is the degree of parallelism on a given query. Therefore those results are not in sorted order. If you want to sort it database executes the sorting option as separate processes.

Once this parameter enables the database not to think about order therefore creates multiple processors and collects or scans the index in the multi-block method which is the concept of fast full scan in Oracle.

A database can scan only using the index to perform database operation without accessing the Table in Oracle index fast full scan. This is scanning without any order. Indexes are performed when retrieving a small number of rows from a table.

oracle index fast full scan reads the entire index because query attributes can be accessed from the index. Instead, use of the table. In this scan read all the indexes using multiblock IO

To execute the scan all the fields in the query should be available in the query including select and where clouses. That will enable the above db_file_multiblock_read_count parameter to execute the query.  Also, you can add the index_fss hint as well to force them to use it.

This is not the fastest way to execute the query because here all the variables involved to fetch the data therefore you have to check the response time for the queries.

If you have a composite index in the table, then this is skipping the leading column for fetching data.

As an example, if you have 3 columns index as column1, column2, column3 But where condition available column2 and column3 then skip scan used to fetch data by ignoring column1 in index.

Oracle generates internal queries for such a situation according to the number of values in column1. Then use index range scan or unique scan for each record and finally return the union of each output.

skip scan is used in such a case when it is better than full table scan in the table. Optimizer uses the database statistics to decide whether to use full table scan, skip scan, or any other scanning method.

If the table has primary keys then you can scan through the unique index. oracle index unique scan stops the scanning process once the first record is found.

A unique index is created for the primary or unique key in the table. So, there are no duplicates with the same key. So there is no need to look for another rowid because there is only one record for the full table.

Table Index contains rowid then retrieve the row by rowid. Unique scans perform only if predicate referencing columns in a unique index. Also, the Where condition in the query should be an equal operator

(Example: Once you want to fetch an employee document using primary keys in the query then execute a query like this. This is going to use a unique key index that is created using primary keys: WHERE emp_id=’900’ and emp_doc_no=’394829’)

How to find a record in a unique scan? I’m I thought the image could express this very nicely. Think about having a column called ‘Id’ as a primary key. That means you have a unique index for ‘Id’.

If you want to query with the condition ‘Id=469’. The below diagram shows how to find a record using an index scan. ROWID is available in Leaf Blocks. You have to start from the ROOT Block and move to the leaf blocks.

Query needs data in an Order defined by ORDER BY clause also if there are no specific data like in an Index Unique Scan. Also, there is not any RANGE according to one column like in an Index RANGE scan. But we have Some orders therefore we can use Index Fast Full Scan. Then Oracle Optimizer Chooses Oracle index full scan and returns all the data in an Order.

An Oracle index full scan is Oracle start read block from ROOT and then navigating to the down branch until find the leaf block on the left side. Move to the left side of the scan doing the ascending.
Otherwise scanning on descending then it is starts from the right side. In this scan, the oracle reads all the blocks one by one in a sequential manner. Once meet the first leaf block then read the entire bottom blocks horizontally one by one.

As we discussed in Oracle fast full scan reads the block using a Multiblock IO operation. Because in that case query does not look for an order. So parrel process happens when collecting rows. But in this case, Oracle doing SINGLE BLOCK IO. For collecting rowids, that means returning data in an order.

Scan one block at a time using SINGLE BLOCK IO. In this example, the diagram returns Order by Column 1.

Simply it reads all the rows in the table. If you have a Where condition, then filter records according to that.  Normally oracle chooses a full table scan when there is no alternate access path. Also, if statistics show a full scan faster than other access paths, then execute the full table to fetch the required rows from a table.

Compared to the total data count if the subset count is high then Oracle uses a full table scan or if there is a small amount of data in the table oracle uses a full table scan. But it changes according to the statistics and a lot of other parameters.

IMPORTANT: If Oracle needs to fetch data using a full table scan oracle does not scan block by block. It uses batch reading and fetches thousands of data in a single IO. But when it is done with INDEX SCAN oracle has to read block by block and get the ROWID. Then there are a lot of IO’s to get final data.

This full table scan does sequential reads. In other words, many blocks simultaneously from the disk. Sequential read is the fastest IO since it takes many blocks at once by cutting a lot of IO just like index scan and random IO.

This image shows how to read blocks and What is Full Table Scan in Oracle. Sequential read fetches many blocks at once

Now you know What is Full Table Scan in Oracle? but do you know why Explain Plan shows a full table scan? here are a few of them but here are the most of cases refer to the full table scan.

Normally oracle chooses a full table scan when there is no alternate access path. Also, if statistics show a full scan faster than other access paths then also execute the full table to fetch the required rows from a table.

• High Parallelism.
• There is NO Index.
• The table is too SMALL.
• The index column contains NULL.
• Queries must read MOST of the blocks in a table.
• Optimizer statistics STALE
• Full table scan HINT.

Every table contains rows, and those rows need some identifier. Therefore, oracle uses ROWID as a unique identifier and it is used to find the physical address of the ROW. ROWID is a 10 BYTE physical address of a row.  All the rowid pointing with a specific file, block, and row number.

A ROWID (Extended ROWID) contains below 4 components:

• –    RowNumber inside the DataBlock
• –    DataObject Number
• –    Datafile Number Relative to the Tablespace
• –    DataBlock Number (inside the DataFile)   

I repeat Rowid is the fastest way of retrieving a single row. rowid specifies the data file and the data block contains the row including the location of the row inside the block. The index also contains the rowid and those rowid maps with table records.

When fetching a small number of records using a query Rowid’s in an index is fast And a full table scan performs well on fetching a large amount of records.

Let’s take an example rowid like AAAT1+ACOAAAKs7AAL. AAL is the row number.

In most cases table access using rowid once after scanning a index. Sometimes index contains all the information that the query needs. In such cases, there is no need to access a table using rowid.

Oracle has an index scan method. All the types take rowids or one rowid according to the scan type. rowid is used to fetch records from a table with a single IO.

If I summarize how Oracle table access by index rowid there are multiple steps. In most cases, Oracle does the following

• Fetch the location and retrieve the record.
• Obtain index rowids that match with the WHERE condition.
• If there are multiple rowids then batch or multiple OI.
• Find the location using ROWID.

If you are wondering how to use the Oracle index_desc hint, then the Oracle index_desc hint is used when descending the existing index on a range scan

--
select /*+ index_desc(employee_table, salary_idx) */
   *
from
   employee_table
where salary = 3000
--

it is oracle will use the existing index in descending order. So, we can force Oracle to use our index in descending order by using the Oracle index_desc hint. Therefore, we can use the existing same keyword for descending order as well.