Difference Between Full Index Scans and Fast Full Index Scans

A fast full index scan reads the entire index, unsorted, as it exists on disk. It is basically using the index as a “skinny” version of the table. The query in question would be accessing only attributes in the index. (We are not using the index as a way to get to the table—we are using the index instead of the table.) We use multiblock I/O and read all the leaf, branch, and root blocks. We ignore the branch and root blocks when executing the query and just process the (unordered) data in the leaf blocks.

A full index scan reads the index a block at a time, from start to finish. It reads the root block, navigates down the left-hand side of the index (or the right-hand side for a descending full scan), and then when it hits the leaf block, it reads across the entire bottom of the index—a block at a time—in sorted order. It uses single-block, not multiblock, I/O for this operation.

index fast full scan tips

Question: What is the fast full scan scan and how does an index fast full scan differ from an index range scan operation?

Answer: Index full scans are related to fast full-index scans, which were introduced in Oracle 7.3. There are some SQL queries that can be resolved by reading the index without touching the table data. For example, the following query does not need to access the table rows, and the index alone can satisfy the query.

select distinct
   color,
   count(*)
from
   automobiles
group by
   color;

Oracle enhanced the fast full-index scan to make it behave similar to a full-table scan. Just as Oracle has implemented the initialization parameterdb_file_multiblock_read_count for full-table scans (deprecated in 11g), Oracle allows this parameter to take effect when retrieving rows for a fast full-index scan. Since the whole index is accessed, Oracle allows multi-block reads.

There is a huge benefit to not reading the table rows, but there are some requirements for Oracle to invoke the fast full-index scan.

All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index.
The query returns more than 10 percent of the rows within the index. This 10 percent figure depends on the degree of multi-block reads and the degree of parallelism.
You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.

The cost-based optimizer will make the decision about whether to invoke the fast full-index scan in accordance with the table and index statistics. You can also force a fast full-index scan by specifying the index_ffs hint:

select distinct /*+ index_ffs(c,pk_auto) /*
color,
count(*)
from
automobiles
group
by color;

It is not always intuitive whether a fast full-index scan is the fastest way to service a query, because of all of the variables involved. Hence, most expert SQL tuners will time any query that meets the fast full-index scan criteria and see if the response time improves.

n keeping with Oracle?s commitment to add intelligence to SQL query optimization, the full-index SQL execution plan has been enhanced to provide support for function-based indexes (FBIs). With Oracle 8 and beyond, intelligence was added to the SQL optimizer to determine if a query might be resolved exclusively within an existing index.

Also, see these important notes on using the index fast full scan (index_ffs) hint.The index fast full scans (full) Oracle metric is the number of fast full scans initiated for full segments.
For full-index scans, Oracle imposes some important restrictions:

All of the columns required by SQL must reside in the index tree; that is, all columns in the SELECT and WHERE clauses must exist in the index.
The query accesses a substantial number of rows. Depending on which expert you ask, this percentage varies from 10 percent to 25 percent, but this figure depends heavily on the settings for db_file_multiblock_read_count and the degree of parallelism for the query. Click here for more details on settingdb_file_multiblock_read_count.
Because the index nodes are not retrieved in index order, the rows will not be sequenced. Hence, an ORDER BY clause will require an additional sorting operation.

Oracle provides a SQL hint to force a full-index scan. You can also force a fast full-index scan by specifying the index_ffs hint, and this is commonly combined with theparallel_index hint to improve performance.
Oracle?s index-organized table (IOT) structure is an excellent example of how Oracle is able to bypass table access whenever an index exists. In an IOT structure, all table data is carried inside the b-tree structure of the index, making the table redundant.

Whenever the Oracle SQL optimizer detects that the query is serviceable without touching table rows, Oracle invokes a full-index scan and quickly reads every block of the index without touching the table itself. It is important to note that a full-index scan does not read the index nodes. Rather, a block-by-block scan is performed and all of the index nodes are quickly cached.

Best of all, Oracle invokes multiblock read capability, invoking multiple processes to read the table. This is the heart of the index fast full scan, a multiblock method for reading an Oracle index.
Oracle and multiblock reads
To speed table and index block access, Oracle uses thedb_file_multiblock_read_count parameter (which defaults to 8) to aid in getting full-table scan and full-index scan data blocks into the data buffer cache as fast as possible. However, this parameter is used only when a SQL query performs a full-table scan, and in most cases, a query uses an index to access the table.

For full-index scans, Oracle imposes some important restrictions:

All of the columns required by SQL must reside in the index tree; that is, all columns in the SELECT and WHERE clauses must exist in the index.
The query accesses a substantial number of rows. Depending on which expert you ask, this percentage varies from 10 percent to 25 percent, but this figure depends heavily on the settings for db_file_multiblock_read_count and the degree of parallelism for the query. Click here for more details on settingdb_file_multiblock_read_count.
Because the index nodes are not retrieved in index order, the rows will not be sequenced. Hence, an ORDER BY clause will require an additional sorting operation.

Basics of function-based indexes
Prior to Oracle9i, full-index scans were possible only when the index was created without any null values. In other words, the index had to be created with a NOT NULL clause for Oracle to be able to use the index. This has been greatly enhanced in Oracle9i with support for index-only scans using function-based indexes.  Click here to see how to use a function-based index to index on NULL values.

As a quick review, function-based indexes were an important enhancement in Oracle8, because they provided a mechanism for the virtual elimination of the unnecessary, long-table full scan. Because a function-based index can exactly replicate any column in the WHERE clause of a query, Oracle will always be able to match the WHERE clause of a SQL query with an index.

Here, I will use a simple example of a student table to illustrate how a full-index scan would work with a function-based index:

create table student
(student_name varchar2(40), date_of_birth date);

Using this table, create a concatenated function-based index of all columns of the table. In this example, the functions are initcap (i.e., capitalize the first letter of each word) and to_char (i.e., change a date to a character):

create index whole_student
on student
(
   (initcap(student_name),
   to_char(date_of_birth,?MM-DD-YY?)
);

With the function-based index defined, Oracle9i will recognize that any SQL statement that references these columns will be able to use the full-index scan. Here is an example of some SQL queries that match the function-based index:

select * from student
where initcap(student_name) = ?Jones?;

select * from student
where to_char(date_of_birth,?MM-DD=YY?) = ?04-07-85?;

Invoking the full-index scan with a function-based index
Oracle will always use the function-based index whenever possible and will invoke a full-index scan on the function-based index. It will do so when the cost-based SQL optimizer statistics indicate that the full-index scan will be faster than a b-tree access via the index.
Once a function-based index is created, you need to create CBO statistics, but beware that there are numerous bugs and issues when analyzing a function-based index. See these important notes on statistics and function-based indexes.

Troubleshooting tip! For testing, you can quickly test the effect of another optimizer parameter value at the query level without using an ?alter session? command, using the new opt_param SQL hint:

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .

Here are the criteria for invoking an index-only scan with a function-based index. All SQL predicates in the WHERE clause match those columns in the index; the query must return enough rows from the table for the cost-based optimizer to recognize that the full-index scan is faster than a traditional index access. The decision to invoke a full-index scan depends on several parameter settings:

Proper statistics for the cost-based optimizer?The schema should have been recently analyzed, and the optimizer_mode parameter must not be set to RULE.
The degree of parallelism on the index?Note that the parallel degree of the index is set independently; the index does not inherit the degree of parallelism of the table.
The setting for optimizer_index_cost_adj?This controls the relative cost of index access. The smaller the value, the less expensive index access becomes, relative to full scans.
The setting for db_file_multiblock_read_count?This parameter factors in the cost of the full-index scan. The higher the value, the ?cheaper? the full-index scan will appear.
The presence of histograms on the index?For skewed indexes, this helps the cost-based optimizer evaluate the number of rows returned by the query.

An important Oracle enhancement
The fast full-index scan on function-based indexes is another enhancement of Oracle9i. Many databases automatically begin to use this new execution plan when the database migrates to Oracle9i. However, there are several factors considered by the cost-based SQL optimizer when choosing to invoke a full-index scan. It's important that the Oracle professional have the appropriate parameter settings to ensure that the cost-based optimizer does not use a fast full-index scan in an inappropriate fashion.

=======================================================================

TOPIC:

Index Unique Scan

DEFINITION:

Till now, We have seen the different types of tables and indexes supported by Oracle. As mentioned earlier, Index is used to improve the performance of SQL. In this topic, we will discuss how these indexes are accessed (or referred) by Oracle optimizer (which decides how query is going to be executed) while executing the query.

Oracle accesses index by any one of these access methods,

1. Index Unique Scan
2. Index Range Scan
3. Index Range Scans Descending
4. Index Skip Scan
5. Index Full Scan
6. Index Fast Full Scan

The below mentioned scans are for the table access method. It can be either one of these,

1. Full Table Scan
2. Rowid Scan

Today, we will discuss about Index Unique Scan.

Index Unique Scan is one of the index access methods supported by Oracle.

Index Unique Scan means only one index record is going to be accessed from the index table while executing the query and as a resultant, only one ROWID is going to be returned. So, this scan returns, at most, a single ROWID.

LITTLE-KNOWN FACTS TO BE REMEMBERED:

· If Oracle has to follow Index Unique Scan, then in the SQL, equality operator (=) must be used. If any operator is used other than equality operator, then Oracle can’t impose this Index Unique Scan.
· Only on b*tree unique index, this Index Unique Scan is imposed if equality operator is used in the query. In all other types of index (like non-unique b*tree index), Oracle Can’t impose this Index Unique Scan though equality(=) operator is used and only one index record is going to be referred as per the dataset.

ADVANTAGE:

· Query retrieval will be fast if Index Unique Scan is followed since only one record is going to be returned.
· In this index access method, random search is made to look for the indexed values instead of sequential search. Performance gain is achieved since the random search (which is used while searching for the data in this unique index table) is much faster than the sequential search (which is used while searching for the data in data table directly)
· In some cases, if all the required columns to be displayed available in the index table itself, then Oracle don’t need to refer the actual data table at all.

DISADVANTAGE:

· If Oracle has to follow this Index Unique Scan, then index table has to be created in advance before executing this query. This index table consumes considerable amount of memory.

HOW TO VERIFY:

How to verify whether Oracle follows index unique scan or not while executing the sql query. If a query follows this, then you will find similar execution plan like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | |

| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | |

In the explain plan, whenever it follows the index unique scan against an unique index, it displays the keyword (UNIQUE SCAN) in the operation column against the index name.

EXAMPLE:

Create an employee table and inserts 10 records. The table will look like this,

DATA TABLE:

ROWID	empid	column2	column3	column4	column5
AAAAA1	5	..	..	..	..
AAAAA2	10	..	..	..	..
AAAAA3	9	..	..	..	..
AAAAA4	4	..	..	..	..
AAAAA5	6	..	..	..	..
AAAAA6	7	..	..	..	..
AAAAA7	1	..	..	..	..
AAAAA8	8	..	..	..	..
AAAAA9	2	..	..	..	..
AAAAA10	3	..	..	..	..

Fire this query against this table where the requirement is to display the all the attributes of an employee whose empid is “8”

Select * from emp where empid = 8;

Here, we are yet to create index. Now, to execute this query, Oracle will take 10 seconds (assume oracle takes 1 second for single data table record search).

Why it is taking 10 seconds? Reason is, Oracle follows the sequential search when it looks for the data in the data table. Here, we are looking for the empid, ‘8’. Since oracle is searching in the data table, it can’t directly go to 8^th record. Oracle starts from first record(AAAAA1) and traverse thro all the records till it reaches the last one(AAAAA10). So in b/w, whichever table record has this empid would be displayed in the output. Explain plan will look like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |

| 1 | TABLE ACCESS FULL | EMP | 10 |

Problem here is, oracle has unnecessarily referred the unwanted 9 table records which are not meant to be referred for this sql.

In order to overcome this issue, we have to create the index (especially unique index) on empid column.

Create an unique index on this table for empid column. (create unique index emp_no_indx on emp(empid)).

Index table will logically look like this,

UNIQUE INDEX TABLE:

INDEX	ROWID
1	AAAAA7
2	AAAAA9
3	AAAAA10
4	AAAAA4
5	AAAAA1
6	AAAAA5
7	AAAAA6
8	AAAAA8
9	AAAAA3
10	AAAAA2

First column (INDEX) : it stores all the values of empid column in the ascending order.

Second column (ROWID) : it stores the ROWID of the corresponding record.

After creating this index table, fire the same query again,

Select * from emp where empid = 8;

After the getting the required ROWID (AAAAA8), oracle directly refers the 8^th record of the data table since it knows the exact location (ROWID of 8^th record is AAAAA8). With this, it doesn’t refer the remaining 9 table records. Now, explain table will look like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |

| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | 1 |

| 2 | INDEX (UNIQUE SCAN) | EMP_NO_INDX (UNIQUE) | 1 |

Since this index table is used, oracle will take only 2 seconds (assume 1 second to get the ROWID of 8^th index record since it follows binary search + 1 second to retrieve the actual table data record since we know the exact ROWID which is retrieved in the previous step)

Explain tells us that the index (EMP_NO_INDX) which is of unique type, it is being accessed via “Index Unique Scan” method. Since the query follows “Index Unique Scan” on this index, only one ROWID (actually only one index record is going to be accessed[8^th index record here]) is going to be returned (“AAAAA8”). That’s why it is called as “Index Unique Scan” access method.

================================================================================

TOPIC:

Index Range Scan

DEFINITION:

Index Range Scan is one of the index access methods supported by Oracle.

Index Range Scan means the retrieval of one or more ROWIDs from an index. Indexed values are generally scanned in ascending order.

Index Range Scan is applicable to both B*Tree Unique Index and B*Tree Non-Unique Index unlike Index Unique Scan, where it is applicable only to B*Tree Unique Index.

LITTLE-KNOWN FACTS TO BE REMEMBERED:

· If Oracle has to follow Index Range Scan, if B*Tree Unique index is created on the table, then in the SQL, any non-equality operator must be used like <=, <, >, >=, IN, BETWEEN. If any of these operators is used, it means more than one index record is going to be referred in the index table and in turn which returns more than one ROWID (because in the unique index, single index value is mapped to single ROWID. So if 5 index records are accessed, it means 5 ROWIDs are retrieved). It is explained in EXAMPLE section.
· If Oracle has to follow Index Range Scan, if B*Tree Non-Unique index is created on the table, then in the SQL, it can have equality operator (=). If this is used, it means only one index record is going to be referred in the index table and in turn which returns more than one ROWID (because in the non-unique index, single index value is mapped to more than one ROWID). Non-Unique Index still goes for the index range scan even if you have used equality operator (=) in the SQL though single index value is mapped to single ROWID only in the non-unique index table . It is explained in EXAMPLE section.
· If Oracle has to follow Index Range Scan, if B*Tree Non-Unique index is created on the table, then in the SQL, any non-equality operator can be used like <=, <, >, >=, IN, BETWEEN. If any of these operators is used, it means more than one index record is going to be referred in the index table and in turn which returns more than one ROWID (because in the unique index, single index value is mapped to more than one ROWID. So if 5 index records are accessed, it means it can return more than 5 ROWIDs)
· Sometimes, Optimizer may not opt for the index range scan (you can verify this by seeing explain plan) though B*Tree Non-Unique index is created on the required columns and these columns are referred in the SQL. Reason is, Optimizer might have evaluated like the cost of accessing all the required data table records through index table are costlier than going for the full table scan directly. Possibility of this scenario is very high if SQL tries to retrieve more than 30% (approximately) of actual table records.

ADVANTAGE:

· Query retrieval will be fast if Index Range Scan is followed since the required ROWID can be accessed from the index table.
· In this index access method, random search is made to look for the indexed values instead of sequential search. Performance gain is achieved since the random search (which is used while searching for the data in this unique index table) is much faster than the sequential search (which is used while searching for the data in data table directly)
· In some cases, if all the required columns to be displayed available in the index table itself, then Oracle don’t need to refer the actual data table at all.

DISADVANTAGE:

· If Oracle has to follow this Index Range Scan, then index table has to be created in advance before executing this query. This index table consumes considerable amount of memory.

HOW TO VERIFY:

How to verify whether Oracle follows index range scan or not while executing the sql query. If a query follows this, then you will find similar execution plan like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | |

| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | |

In the explain plan, whenever it follows the index range scan against either an unique index or a non-unique index, it displays the keyword (RANGE SCAN) in the operation column against the index name in both the cases.

EXAMPLE:

Create an employee table and inserts 10 records. The table will look like this,

DATA TABLE:

ROWID	empid	column2	column3	column4	deptid
AAAAA1	5	..	..	..	SALES
AAAAA2	10	..	..	..	HR
AAAAA3	9	..	..	..	ADMIN
AAAAA4	4	..	..	..	SALES
AAAAA5	6	..	..	..	SALES
AAAAA6	7	..	..	..	HR
AAAAA7	1	..	..	..	HR
AAAAA8	8	..	..	..	SALES
AAAAA9	2	..	..	..	ADMIN
AAAAA10	3	..	..	..	ADMIN

TESTCASE-I: (for the unique index)

Fire this query against this table where the requirement is to display the all the attributes of an employee whose empid should be greater than or equal to “7” but less than or equal to“9”

Select * from emp where empid between 7 and 9;

Before execute this query, create an unique index on this table for empid column. (create unique index emp_no_indx on emp(empid)).

Index table will logically look like this,

UNIQUE INDEX TABLE:

INDEX	ROWID
1	AAAAA7
2	AAAAA9
3	AAAAA10
4	AAAAA4
5	AAAAA1
6	AAAAA5
7	AAAAA6
8	AAAAA8
9	AAAAA3
10	AAAAA2

First column (INDEX) : it stores all the values of empid column in the ascending order.

Second column (ROWID) : it stores the ROWID of the corresponding record.

When oracle executes this sql, first it looks for any index which has already been created on this “empid” column. It comes to know that the index, “emp_no_indx” has already been created on “empid” column. So, oracle refers the index table first before hitting the actual data table. Since we are looking for the range of empids:”7,8,9”, oracle hits index table first, starts with the index value, “7” and ends with the index value, “9” so it gets the corresponding ROWIDs (AAAAA6, AAAAA8, AAAAA3). When oracle searches for the value in this index table, it follows random search (i.e. binary search) to go to the corresponding first required index record (since we are looking for the range of index records), in this case it is 7^th index record. Using this binary search, Oracle is intelligent enough to go to the 7^th index record directly in the index table. So, it won’t touch the first 6 index records. After that, it does only the sequential search to retrieve the two remaining index records (8^th and 9^th index records) since index values are kept in the sorted order so it doesn’t need to go for the random search for these two . (because optimizer is very sure than 8 and 9 should come immediately after 7)

After the getting the required ROWIDs (AAAAA6, AAAAA8, AAAAA3), oracle directly refers 3^rd, 6^th and 8^th record of the data table since it knows the exact locations. With this, it doesn’t refer the remaining 7 table records. Now, explain table will look like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |

| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | 1 |

| 2 | INDEX (RANGE SCAN) | EMP_NO_INDX (UNIQUE) | 1 |

Explain tells us that the index (EMP_NO_INDX) which is of unique type, it is being accessed via “Index Unique Scan” method. If B*Tree unique index goes for Index Range Scan, then in the ROWS column of the explain plan, only the value, ‘1’ would be displayed because each index record returns only one ROWID but 3 index records are accessed. That’s why it is called as “Index Range Scan” access method.

TESTCASE-II: (for the non-unique index)

Fire this query against this table where the requirement is to display the all the attributes of an employee whose deptid is “HR”

Select * from emp where deptid = ‘HR’;

Before execute this query, create a non-unique index on this table for deptid column. (create index dept_id_indx on emp(deptid)).

Index table will logically look like this,

NON-UNIQUE INDEX TABLE:

INDEX	ROWID
ADMIN	AAAAA3
	AAAAA9
	AAAAA10
HR	AAAAA2
	AAAAA6
	AAAAA7
SALES	AAAAA1
	AAAAA4
	AAAAA5
	AAAAA8

First column (INDEX) : it stores all the distinct values of deptid column in the ascending order.

Second column (ROWID) : it stores the ROWID of the corresponding records but all the ROWIDs for a single index value are grouped together.

After the getting the required ROWIDs (AAAAA2,AAAAA6, AAAAA7), oracle directly refers the 2^nd, 6^thand 7^th records of the data table since it knows the exact locations. With this, it doesn’t refer the remaining 7 table records. Now, explain table will look like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 |

| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | 3 |

| 2 | INDEX (RANGE SCAN) | DEPT_ID_INDX (NON-UNIQUE) | 3 |

Explain tells us that the index (DEPT_ID_INDX) which is of non-unique type, it is being accessed via “Index Unique Scan” method. If B*Tree non-unique index goes for Index Range Scan, then in the ROWS column of the explain plan, only the value, ‘3’ would be displayed because the index value (‘HR”) returns 3 index records. That’s why it is called as “Index Range Scan” access method.

=================================================================================

TOPIC:

Index Range Scan Descending

DEFINITION:

Index Range Scan Descending is one of the index access methods supported by Oracle.

Index Range Scan Descending means the retrieval of one or more ROWIDs from an index. Index Range Scan Descending is applicable to both B*Tree Unique Index and B*Tree Non-Unique Index unlike Index Unique Scan, where it is applicable only to B*Tree Unique Index. This retrieves the index data in the descending order unlike Index Range Scan where it retrieves the data in the same order as of index table.

LITTLE-KNOWN FACTS TO BE REMEMBERED:

· By seeing this index scan name, we would think that there would be an another index scan called “Index Range Scan Ascending” but it is actually not. This index scan name might be little confusing but ideal name for this index scan should be “Index Range Scan Reverse”. Reason is, this index scan suffices both these scenarios. 1) if index is created in the ascending order, with this index scan, we can retrieve the index data in the descending order. 2) if index is created in the descending order, with this index scan, we can retrieve the data in the ascending order.
· If Oracle has to follow Index Range Scan Descending, if B*Tree Unique index is created on the table, then in the SQL, any non-equality operator must be used like <=, <, >, >=, IN, BETWEEN. In addition to this, in the ORDER BY clause, we should mention the reverser order (please check the first point).If any of these operators is used, it means more than one index record is going to be referred in the index table in the reverse order and in turn which returns more than one ROWID (because in the unique index, single index value is mapped to single ROWID. So if 5 index records are accessed, it means 5 ROWIDs are retrieved). It is explained in EXAMPLE section.
· If Oracle has to follow Index Range Scan Descending, if B*Tree Non-Unique index is created on the table, then in the SQL, any non-equality operator can be used like <=, <, >, >=, IN, BETWEEN. In addition to this, in the ORDER BY clause, we should mention the reverser order (please check the first point If any of these operators is used, it means more than one index record is going to be referred in the index table in the reverse order and in turn which returns more than one ROWID (because in the non-unique index, single index value is mapped to more than one ROWID. So if 5 index records are accessed, it means it can return more than 5 ROWIDs). It is explained in EXAMPLE section.

ADVANTAGE:

· Generally, TEMP tablespace is used for sorting of records. If Oracle follows Index Range Scan Descending, then it won’t consume any memory from TEMP tablespace. Because sorting is taken care by reading the index table in the reverse order itself.
· Query retrieval will be fast if Index Range Scan is followed since the required ROWID can be accessed from the index table.
· In this index access method, random search is made to look for the indexed values instead of sequential search. Performance gain is achieved since the random search (which is used while searching for the data in this unique index table) is much faster than the sequential search (which is used while searching for the data in data table directly)
· In some cases, if all the required columns to be displayed available in the index table itself, then Oracle don’t need to refer the actual data table at all.

DISADVANTAGE:

· If Oracle has to follow this Index Range Scan Descending, then index table has to be created in advance before executing this query. This index table consumes considerable amount of memory.

HOW TO VERIFY:

How to verify whether Oracle follows index range scan Descending or not while executing the sql query. If a query follows this, then you will find similar execution plan like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | |

| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | |

In the explain plan, whenever it follows the index range scan descending against either an unique index or a non-unique index, it displays the keyword (RANGE SCAN DESCENDING) in the operation column against the index name in both the cases.

EXAMPLE:

Create an employee table and inserts 10 records. The table will look like this,

DATA TABLE:

ROWID	empid	column2	column3	column4	deptid
AAAAA1	5	..	..	..	SALES
AAAAA2	10	..	..	..	HR
AAAAA3	9	..	..	..	ADMIN
AAAAA4	4	..	..	..	SALES
AAAAA5	6	..	..	..	FINANCE
AAAAA6	7	..	..	..	HR
AAAAA7	1	..	..	..	HR
AAAAA8	8	..	..	..	SALES
AAAAA9	2	..	..	..	FINANCE
AAAAA10	3	..	..	..	ADMIN

TESTCASE-I: (for the unique index)

Fire this query against this table where the requirement is to display all the attributes of the employees whose empid should be greater than or equal to “7” but less than or equal to“9”

and display the records in the descending order of employee ids.

Select * from emp where empid between 7 and 9 order by empid desc;

Before execute this query, create an unique index on this table for empid column. (create unique index emp_no_indx on emp(empid)).

Index table will logically look like this,

UNIQUE INDEX TABLE:

INDEX	ROWID
1	AAAAA7
2	AAAAA9
3	AAAAA10
4	AAAAA4
5	AAAAA1
6	AAAAA5
7	AAAAA6
8	AAAAA8
9	AAAAA3
10	AAAAA2

First column (INDEX) : it stores all the values of empid column in the ascending order.

Second column (ROWID) : it stores the ROWID of the corresponding record.

When oracle executes this sql, first it looks for any index which has already been created on this “empid” column. It comes to know that the index, “emp_no_indx” has already been created on “empid” column. So, oracle refers the index table first before hitting the actual data table. Since we have mentioned the descending order in the order by clause of the select statement, Oracle reads the index table in the bottom-up approach instead of normal top-down approach. Since we are looking for the range of empids:”7,8,9”, oracle hits index table first, starts with the index value, “9” and ends with the index value, “7” so it gets the corresponding ROWIDs (AAAAA3, AAAAA8, AAAAA6). When oracle searches for the value in this index table, it follows random search (i.e. binary search) to go to the corresponding first required index record (since we are looking for the range of index records in the descending order), in this case it is 9^th index record. Using this binary search, Oracle is intelligent enough to go to the 9^th index record directly in the index table. After that, it does only the sequential search to retrieve the two remaining index records (8^th and 7^th index records) since index values are kept in the sorted order so it doesn’t need to go for the random search for these two . (because optimizer is very sure than 8 and 7 should come immediately before 9)

After the getting the required ROWIDs (AAAAA3, AAAAA8, AAAAA6), oracle directly refers 3^rd, 8^th and 6^th records of the data table since it knows the exact locations. With this, it doesn’t refer the remaining 7 table records. Now, explain table will look like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |

| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | 1 |

| 2 | INDEX (RANGE SCAN DESCENDING)| EMP_NO_INDX (UNIQUE) | 1 |

Explain tells us that the index (EMP_NO_INDX) which is of unique type, it is being accessed via “Index Unique Scan Descending” method. If B*Tree unique index goes for Index Range Scan Descending, then in the ROWS column of the explain plan, only the value, ‘1’ would be displayed because each index record returns only one ROWID and 3 index records are accessed in the reverse order. That’s why it is called as “Index Range Scan Descending” access method.

TESTCASE-II: (for the non-unique index)

Fire this query against this table where the requirement is to display the all the attributes of an employee whose deptid is either “ADMIN” or “FINANCE” and display the records in the descending order of department id.

Select * from emp where deptid in (‘ADMIN’,’FINANCE’);

Before execute this query, create a non-unique index on this table for deptid column. (create index dept_id_indx on emp(deptid)).

Index table will logically look like this,

NON-UNIQUE INDEX TABLE:

INDEX	ROWID
ADMIN	AAAAA3
ADMIN	AAAAA10
FINANCE	AAAAA5
FINANCE	AAAAA9
HR	AAAAA2
	AAAAA6
	AAAAA7
SALES	AAAAA1
	AAAAA4
	AAAAA8

First column (INDEX) : it stores all the distinct values of deptid column in the ascending order.

Second column (ROWID) : it stores the ROWID of the corresponding records but all the ROWIDs for a single index value are grouped together.

When oracle executes this sql, first it looks for any index which has already been created on this “deptid” column. It comes to know that the index, “dept_id_indx” has already been created on “deptid” column. So, oracle refers the index table first before hitting the actual data table. Since we have mentioned the descending order in the order by clause of the select statement, Oracle reads the index table in the bottom-up approach instead of normal top-down approach. Since we are looking for the group of deptids, (’ADMIN’ and ‘FINANCE’), oracle hits index table first , starts with the index value, “FINANCE” and ends with the index value, “ADMIN” so it gets the corresponding ROWIDs (AAAAA5, AAAAA9, AAAAA3, AAAAA10). When oracle searches for the value in this index table, it follows random search (i.e. binary search) to go to the corresponding first required index record (since we are looking for the range of index records in the descending order), in this case it is 2^nd index record. Using this binary search, Oracle is intelligent enough to go to the 2^nd index record directly in the index table. After that, it does only the sequential search to retrieve the other remaining index record (1^st index record) since index values are kept in the sorted order so it doesn’t need to go for the random search for this . (because optimizer is very sure that “ADMIN” should come before “FINANCE”)

After the getting the required ROWIDs (AAAAA5,AAAAA9,AAAAA3,AAAAA10), oracle directly refers the 5^th, 9^th, 3^rd and 10^th records of the data table since it knows the exact locations. With this, it doesn’t refer the remaining 6 table records. Now, explain table will look like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 |

| 1 | TABLE ACCESS (BY INDEX ROWID) |EMP | 4 |

| 2 | INDEX (RANGE SCAN DESCENDING)| DEPT_ID_INDX (NON-UNIQUE) | 4 |

Explain tells us that the index (DEPT_ID_INDX) which is of non-unique type, it is being accessed via “Index Unique Scan Descending” method. If B*Tree non-unique index goes for Index Range Scan Descending, then in the ROWS column of the explain plan, only the value, ‘4’ would be displayed because the index table has returned 4 ROWIDs. That’s why it is called as “Index Range Scan Descending” access method.

================================================================================

TOPIC:

Index Skip Scan

DEFINITION:

Index Skip Scan is one of the index access methods supported by Oracle.

Index Skip Scan can be imposed on composite index in the certain scenarios only.Composite index is otherwise called as concatenated index or multi-column index. Composite (Concatenated) index means creating an index on more than one column.

Prior to Oracle 9i version, if the leading column of an index wasn’t specified in a predicate, the index was effectively ignored by the CBO (Cost Based Optimizer). A composite index could only be used if the first column, the leading edge, of the index was referenced in the WHERE clause of a statement.

However, if the leading column isn’t referenced now, Oracle can use the index anyways via an Index Skip Scan access path (This is applicable from Oracle 9i version onwards). So, Index Skip Scan means, if a composite index is created on 2 columns where the number of distinct values in the first column is very less and the number of distinct values in the second column is very high, and if only the second column is mentioned in the WHERE clause, then there is a high possibility that Oracle will follow Index Skip Scan on this index for that respective SQL.

Index Skip Scan is applicable to both B*Tree Unique Index and B*Tree Non-Unique Index.

LITTLE-KNOWN FACTS TO BE REMEMBERED:

· You can force an index skip scan with the /*+ index_ss */ hint if oracle doesn’t impose on its own.
· Oracle can follow Index Skip Scan only in this scenario, a composite index should be created on column-A and column-B, number of distinct values of column-A should be very less (eg., Sex column because it can have either M or F irrespective of number of records in the table), number of distinct values of column-B should be very high (eg., any normal columns like, street, pincode…), only the column-B should be mentioned in the WHERE clause of the SQL, statistics should be gathered for both index and table. Only if all the above mentioned points are satisfied, then Oracle will follow index skip scan.

ADVANTAGE:

· Only minimal number of indexes are required to be created since Oracle supports Index Skip Scan. Reason is, if Index Skip Scan was not supported, take an example where a composite index was created on column-A and column-B, only if column-B was mentioned in WHERE clause, then this index couldn’t be used by Oracle. To fix this, another index should be created on column-b alone. Since Oracle supports Index skip scan, we don’t need to create a separate index on column-B alone.
· If index skip scan is followed, then we can eliminate full table scan or full index scan thereby we can reduced the execution time of query, number of data blocks to be referred and I/O.

DISADVANTAGE:

· Query performance would go for a toss when Oracle imposed Index Skip Scan on this scenario, a composite index created on column-A and column-B, number of distinct values of column-A was very high, number of distinct values of column-B was also very high, and if only the column-B mentioned in the WHERE clause of the SQL, then query performance would be much worse than if Oracle opted for full table(or index) scan.

HOW TO VERIFY:

How to verify whether Oracle follows index skip scan or not while executing the sql query. If a query follows this, then you will find similar execution plan like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | |

| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | |

In the explain plan, whenever it follows the index skip scan, it displays the keyword (SKIP SCAN) in the operation column against the index name.

EXAMPLE:

Create an employee table and inserts 10 records. The table will look like this,

DATA TABLE:

ROWID	empid	column2	column3	Sex	deptid
AAAAA1	1	..	..	M	HR
AAAAA2	2	..	..	M	DELIVERY
AAAAA3	3	..	..	F	ADMIN
AAAAA4	4	..	..	F	SALES
AAAAA5	5	..	..	F	FINANCE
AAAAA6	6	..	..	M	ADMIN
AAAAA7	7	..	..	M	SALES
AAAAA8	8	..	..	F	HR
AAAAA9	9	..	..	F	DELIVERY
AAAAA10	10	..	..	M	FINANCE

Fire this query against this table where the requirement is to display the all the attributes of the employees whose deptid is “DELIVERY”

Select * from emp where deptid = ‘DELIVERY’;

Here, we are yet to create index. Now, to execute this query, Oracle will take 10 seconds (assume oracle takes 1 second for single data table record search).

Why it is taking 10 seconds? Reason is, Oracle follows the sequential search when it looks for the data in the data table. Here, we are looking for the deptid, ‘DELIVERY’. Since oracle is searching in the data table, it can’t directly go to 2^ndand 9^th records. Oracle starts from first record and traverse thro all the records till it reaches the last one. So in b/w, whichever table record has this deptid would be displayed in the output. Explain plan will look like this,

--------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 |

| 1 | TABLE ACCESS FULL | EMP | 10 |

Problem here is, oracle has unnecessarily referred the unwanted 8 table records which are not meant to be referred for this sql. In order to overcome this issue, we have to create a composite index on sex & deptid columns.

Why are sex and deptid columns the ideal candidate to create the composite index and to impose index skip scan? Because sex column has very less number of distinct values (M & F), deptid column has very high number of distinct values (HR,DELIVERY, ADMIN, SALES & FINANCE), only deptid column is mentioned in WHERE clause of the select query, so these 2 are suitable columns to create composite index in order to impose index skip scan.

Create a composite index on this table for sex and deptid columns. (create index sex_dept_indx on emp(sex,deptid)).

Index table will logically look like this,

First column (INDEX) : it stores all the unique combination values of sex & deptid columns in the ascending order.

Second column (ROWID) : it stores the ROWID of the corresponding record.

After creating this index table, fire the same query again,

Select * from emp where deptid = ‘DELIVERY’;

When oracle executes this sql, first it looks for any index which has already been created on this “deptid” column alone. It comes to know that none of the indexes has been created on this column alone but if finds the index, “sex_dept_indx” has included this column. If oracle has to refer this index, then it must know the values of SEX column to be referred. Since SEX column is not mentioned in the query, we can take it for granted that all the distinct values of SEX column should be referred. So, Oracle implicitly converts the query into like this (without any manual intervention),

Select * from emp where sex = ‘F’ and deptid = ‘DELIVERY’

UNION ALL

Select * from emp where sex = ‘M’ and deptid = ‘DELIVERY’;

Since this converted query contains both SEX and DEPTID columns in the WHERE clause, Oracle will refer this index, “sex_dept_indx” now. So, oracle refers the index table first before hitting the actual data table. Since we are looking for the deptid:’DELIVERY’, oracle hits index table first and get the corresponding ROWIDs (AAAAA9,AAAAA2). When oracle searches for the value in this index table, it follows random search (i.e. binary search) to go to the corresponding index records. Using this binary search, Oracle is intelligent enough to go to the 2^nd and 7^th index records directly in the index table. So, it won’t touch the remaining 8 index records.

After the getting the required ROWIDs (AAAAA9,AAAAA2), oracle directly refers both 9^thand 2^nd records of the data table since it knows the exact location (ROWID of 9^th table record is AAAAA9 and ROWID of 2^nd table record is AAAAA2). With this, it doesn’t refer the remaining 8 table records. Now, explain table will look like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 |

| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | 2 |

| 2 | INDEX (SKIP SCAN) | SEX_DEPT_INDX | 2 |

Since this index table is used, oracle will take only 2 seconds (assume 1 second to get the ROWID of 2^nd and 7^th index records since it follows binary search + 1 second to retrieve the actual table data records since it knows the exact ROWIDs those are retrieved in the previous step)

Reason for this scan to be called as “Index Skip Scan” is, this is the only type of scan where consecutive index records are not retrieved. In this scan, whenever oracle refers the next required index record, it jumps from the first required index record to the second required index record. In this example, it jumps from 2^nd index record to 7^th index record thereby it skips 3^rd, 4^th, 5^th and 6^th index records which come between 2^nd and 7^th index records (pls refer blue colored arrow mark in the index table). So, whenever oracle refers multiple index records from the index table through this scan, it jumps through the required index records alone thereby skipping the reference of unwanted index records. Due to this in-built mechanism, it eliminates both full table scan and full index scan thereby increasing the speed of the query. These are the reasons this index to be called as “Index Skip Scan”.

===============================================================================

TOPIC:

Index Full Scan

DEFINITION:

Index Full Scan is one of the index access methods supported by Oracle.

Index Full Scan is otherwise called as Full Index Scan.

Index Full Scan can be imposed by Oracle Optimizer only in some scenarios.

One scenario would be, Whenever the Oracle SQL optimizer detects that the query is serviceable without touching table rows, Oracle invokes a full-index scan and quickly reads every block of the index without touching the table itself.

Another scenario would be, though the table rows have to be referred, Oracle would still opt for this Index Full Scan only if the column(for which index is created) is mentioned in Order by clause of the query. Advantage over here is, no explicit sorting needs to be done as rows from the index would already be read in the sorting order.

LITTLE-KNOWN FACTS TO BE REMEMBERED:

· Index Full Scan is totally different from Fast Full Index Scan and the latter will be explained in the next mail.
· The first scenario I explained earlier wherein, if all the columns mentioned in SELECT, WHERE, GROUP BY, HAVING & ORDER BY clauses are part of the index, then Oracle would prefer to opt for Index full scan instead of full table scan. So with this scan, it completely avoids the table scan.
· The second scenario I explained earlier wherein if Oracle opts for Index full scan and then refers the table rows, the you should see this step “SORT ORDER BY (NO SORT)” in the explain plan which confirms that Oracle doesn’t put any efforts for sorting since the data already comes in a sorted order from the index table.

ADVANTAGE:

· Usage of the temporary tablespace will be drastically reduced with this type of scan. As you aware, if oracle opts for this scan, then the data which is being read from index table would already be in the sorted order. Temporary tablespace is used only when oracle does the sorting on its own. So, Oracle doesn’t require any space in temporary tablespace in this scenario.
· If index full scan is followed, then we can eliminate full table scan completely thereby we can reduce the execution time of query, number of data blocks to be referred and I/O.

DISADVANTAGE:

· Index Full Scan can be achieved only by reading block-by-block in the index table since data has to come out in the sorted order. So, it can read single index block at a time.
· It can’t read the multi index blocks at a time which is a performance hit but the same multi index blocks read is achievable in that other index scan, Fast Full Index Scan.

HOW TO VERIFY:

How to verify whether Oracle follows index full scan or not while executing the sql query. If a query follows this, then you will find similar execution plan like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | |

(Or)

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | |

| 1 | SORT ORDER BY (NO SORT) | | |

| 2 | TABLE ACCESS (BY INDEX ROWID) | EMP | |

For the 1^st scenario I explained earlier, you will find a similar explain plan to that of 1^stone. Since it doesn’t refer the table, you don’t find the step, “TABLE ACCESS BY INDEX ROWID” in the explain plan.

For the 2^nd scenario I explained earlier, you will find a similar explain plan to that of 2^ndone. Since it refers the table, you find this step, “TABLE ACCESS BY INDEX ROWID” here. In addition to that, you also find the other step, “SORT ORDER BY (NO SORT)” which means oracle doesn’t need to do any explicitly sorting since the data which is being read from the index table (before hitting the table rows) is already coming in a sorted order.

In the explain plans, whenever it follows the index full scan, it displays the keyword (FULL SCAN) in the operation column against the index name.

EXAMPLE#1:

Create an employee table and inserts 10 records. The table will look like this,

DATA TABLE:

ROWID	empid	column2	column3	Sex	deptid
AAAAA1	1	..	..	M	HR
AAAAA2	2	..	..	M	DELIVERY
AAAAA3	3	..	..	F	ADMIN
AAAAA4	4	..	..	F	SALES
AAAAA5	5	..	..	F	FINANCE
AAAAA6	6	..	..	M	ADMIN
AAAAA7	7	..	..	M	SALES
AAAAA8	8	..	..	F	HR
AAAAA9	9	..	..	F	DELIVERY
AAAAA10	10	..	..	M	FINANCE

Fire this query against this table where the requirement is to display the all the departments where a female employee works in the sorted order by deptid,

Select deptid from emp where sex = ‘F’ order by deptid;

Here, we are yet to create index. Now, to execute this query, Oracle will take 15 seconds.

First 10 seconds will be taken to retrieve all the table record by assuming that Oracle takes 1 second for single data table record retrieval.

Why it is taking another 5 seconds? Reason is, Oracle has to sort all the deptids (since this column is mentioned in the select clause) where a female employee works. We have 5 table records where the sex is ‘F’. So, Oracle will consume these 5 seconds for this sorting purpose. Explain plan will look like this,

--------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5 |

| 1 | TABLE ACCESS FULL | EMP | 10 |

Problem here is, oracle has unnecessarily referred the unwanted 5 table records which are not meant to be referred for this sql and the explicit sorting is also done by Oracle. In order to overcome this issue, we have to create a composite index on deptid & sex columns.

Why are deptid and sex columns the ideal candidate to create the composite index and to impose index full scan?

Because if you closely watch this query, the distinct columns used here are only sex & deptid columns. So if an index is created for these 2 columns, then Oracle doesn’t need to refer the table records at all since all the required data can be retrieved from the index table itself and even explicit sorting is also not required if index full scan is opted.

Create a composite index on this table for deptid and sex columns. (create index sex_dept_indx on emp(deptid,sex)).

Index table will logically look like this,

INDEX TABLE:

INDEX VALUE		ROWID
DEPTID	SEX	ROWID
ADMIN	F	AAAAA3
ADMIN	M	AAAAA6
DELIVERY	F	AAAAA9
DELIVERY	M	AAAAA2
FINANCE	F	AAAAA5
FINANCE	M	AAAAA10
HR	F	AAAAA8
HR	M	AAAAA1
SALES	F	AAAAA4
SALES	M	AAAAA7

First column (INDEX) : it stores all the unique combination values of deptid & sex columns in the ascending order.

Second column (ROWID) : it stores the ROWID of the corresponding records.

After creating this index table, fire the same query again,

Select deptid from emp where sex = ‘F’ order by deptid;

When oracle executes this sql, first it looks for any index which has already been created on this “deptid & sex” columns alone. It comes to know that “sex_dept_indx” index has already been created on these columns so it refers this index. To filter the unwanted records, Oracle has to execute WHERE clause of the query. Since, we mention ‘F’ as sex, and this column is not the leading column in the index, Oracle has to go for the “full index scan” means refer all the records in the index table before the filtration. After traversing through all the index records in the index table, Oracle filters the unwanted 5 records and then retrieves other 5 required index records (ADMIN,DELIVERY,FINANCE,HR,SALES) alone in the same order as appear in the index table.

With this, it doesn’t refer the table records at all since all the required columns(deptid & sex) are already available in the index table itself. Now, explain table will look like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5 |

| 1 | INDEX (FULL SCAN) | SEX_DEPT_INDX | 10 |

Since this index table is used and index full scan is opted by Oracle, oracle will take only 2 seconds (assume 1 second to traverse through all the index records + 1 second to retrieve only those 5 required index records in the same order as appear in the index table so that explicit sorting is not required)

Output for this query will be like this as expected,

DEPTID

ADMIN

DELIVERY

FINANCE

SALES

EXAMPLE#2:

For this example also, pls refer the same table structure, data and the index as of previous example.

Fire this query against this table where the requirement is to display the all the employee ids & departments where a female employee works in the sorted order of department id,

Select empid,deptid from emp where sex = ‘F’ order by deptid;

Without the index, Oracle will take same 15 seconds(as explained in the previous example) to display the required output.

After creating this index table(refer this index table in the previous example), fire the same query again,

Select empid,deptid from emp where sex = ‘F’ order by deptid;

When oracle executes this sql, first it looks for any index which has already been created on this “sex” column since this is the filtering criteria here. It comes to know that “sex_dept_indx” index has already been created on this column so it refers this index. To filter the unwanted records, Oracle has to execute WHERE clause of the query. Since, we have mentioned ‘F’ as sex, and this column is not the leading column in the index, Oracle has to go for the “full index scan” means refer all the records in the index table before the filtration. After traversing through all the index records in the index table, Oracle filters the unwanted 5 records and gets the rowids of the required 5 records (AAAAA3, AAAAA9, AAAAA5, AAAAA8 & AAAAA4) alone in the same order as appear in the index table.

After the getting the required ROWIDs, oracle directly refers 3^rd,9^th,5^th,8^th & 4^th records of the data table since it knows the exact location. With this, it doesn’t refer the remaining 5 table records. Now, explain table will look like this,

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5 |

| 1 | SORT ORDER BY (NO SORT) | | 5 |

| 2 | TABLE ACCESS (BY INDEX ROWID) | EMP | 5 |

| 3 | INDEX (FULL SCAN) | SEX_DEPT_INDX | 10 |

Since this index table is used and index full scan is opted by Oracle, oracle will take only 6 seconds (assume 1 second to get the ROWID of all the required 5 records from the index table in the same order+ 1 second to retrieve the actual table data records since it knows the exact ROWIDs those are retrieved in the previous step each)

Reason for this scan to be called as “Index Full Scan” is, Oracle has to traverse through all the records in the index table and that’s why you see value as 10 in “Rows” column of the explain plan. After that, it filters 5 unwanted records and only fetches the ROWID for the required 5 records alone and only for these 5 records, it hits the table and get the corresponding empid. In this scenario, Oracle must hit the table since empid column is mentioned in the SELECT clause of the query and the empid column is not part of that created index, “sex_dept_indx”. You also find “SORT ORDER BY (NO SORT) in the explain plan since no sorting is explicitly done by Oracle as data is being retrieved from the index table in the sorted order itself.

Output for this query will be like this as expected,

EMPID	DEPTID
3	ADMIN
9	DELIVERY
5	FINANCE
8	HR
4	SALES

=======================================================================

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf

Oracle DBA Information at one place

Search This Blog

Oracle Performance Tuning: Index Access Methods

Difference Between Full Index Scans and Fast Full Index Scans

index fast full scan tips

Comments

Post a Comment