Full Table Scan (FTS)
FTS scans the entire table to fetch the results I.e reads the every row of the table to find all records meeting the given requirements
Even if the query selects just a few rows from the table, all rows in the entire table will be examined
Table scan on larger tables take excessive amount of time and cause performance problem
Following are the situations where optimizer chooses Full Table Scans:-
Lack of Index.
The query predicate applies a function to the indexed column.
Select COUNT(*) with B-Tree indexes & Nulls.
Large Amount of Data
Small Table
High Degree of Parallelism
FTS Hint
The table statistics are stale.
The query predicate does not use the leading edge of a B-tree index..
The query is unselective.
Pros:
The cost is predictable, as every time database system needs to scan full table row by row.
When table is less than 2 percent of database block buffer then full scan table is quicker.
Cons:
Full table scan occurs when there is no index or index is not being used by SQL. And the result of full scan table is usually slower that index table scan. The situation is that: the larger the table, the slower of the data returns.
Unnecessary full-table scan will lead to a huge amount of unnecessary I/O with a process burden on the entire database.
Index Scan
The Index Scans is a access path used by the query optimizer to produce the best Sql Engine - (Physical|Execution) Plan.
In this method, a row is retrieved by traversing the index, using the indexed column values specified by the statement
An index scan retrieves data from an index based on the value of one or more columns in the index.
To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement
If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.
The index contains :
not only the indexed value,
but also the rowids of rows in the table having that value.
Therefore, if the statement accesses other columns in addition to the indexed columns, then Oracle can find the rows in the table by using either a table access by rowid or a Cluster Scan/Access.
An index scan can be one of the following types:
Index Unique Scans
This scan returns, at most, a single rowid. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.
Example
An index scan is performed on the jobs and departments tables, using the job_id_pk and dept_id_pk indexes respectively.
Index Range Scans
An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.
If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.
Example
In this example, the order has been imported from a legacy system, and you are querying the order by the reference used in the legacy system. Assume this reference is the order_date.
This should be a highly selective query, and you should see the query using the index on the column to retrieve the desired rows. The data returned is sorted in ascending order by the rowids for the order_date. Because the index column order_date is identical for the selected rows here, the data is sorted by rowid.
Index Range Scans Descending
An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order. Usually, this scan is used when ordering data in a descending order to return the most recent data first, or when seeking a value less than a specified value.
Index Skip Scans
Index skip scans improve index scans by non-prefix columns. Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index be split logically into smaller sub-indexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
The number of logical sub-indexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the non-leading key of the index.
Consider, for example, a table employees (sex, employee_id, address) with a composite index on (sex, employee_id). Splitting this composite index would result in two logical sub-indexes, one for M and one for F.
For this example, suppose you have the following index data:
The index is split logically into the following two sub-indexes:
The first sub-index has the keys with the value F.
The second sub-index has the keys with the value M.
The column sex is skipped in the following query:
A complete scan of the index is not performed, but the sub-index with the value F is searched first, followed by a search of the sub-index with the value M.
Full Index Scan
A full index scan eliminates a sort operation, because the data is ordered by the index key. It reads the blocks singly. A full scan is used in any of the following situations:
An ORDER BY clause that meets the following requirements is present in the query:
All of the columns in the ORDER BY clause must be in the index.
The order of the columns in the ORDER BY clause must match the order of the leading index columns.
The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.
The query requires a sort merge join. A full index scan can be done instead of doing a full table scan followed by a sort if the query meets the following requirements:
All of the columns referenced in the query must be in the index.
The order of the columns referenced in the query must match the order of the leading index columns.
The query can contain all of the columns in the index or a subset of the columns in the index.
A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index. The columns do not need to be in the same order in the index and the GROUP BY clause. The GROUP BY clause can contain all of the columns in the index or a subset of the columns in the index.
Fast Full Index Scans
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can :
use multiblock I/O
and be parallelized just like a table scan.
Note: Setting PARALLEL for indexes will not impact the cost calculation.
You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint.
Index Joins
An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.
Bitmap Indexes
A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.
Partition Range Scan
Oracle optimizes partitioned queries differently than regular queries.
Oracle tries to pick the fastest elapsed time to the data, not the lowest cost.
The Optimizer typically scans partitions first
Scans only relevant partitions (called pruning)
Try to avoid functions on partitioning key values in WHERE clauses
Try to avoid subqueries on partitioning key values
Maybe avoid indexes on partitioning keys (Oracle uses efficient multi-block scans with fewer I/O than using indexes and single block reads)
Want to avoid “long ops”operations
Oracle Parallel Server and RAC instances can break a SQL up across processors and return the pieces for a much faster response time on larger objects.
Consider where your data is in the partitioned object. If the selected rows are across the partition, it might be considerably faster to do a full-table scan (using multi-block read-ahead of course) than to do single block reads, even on a local-partitioned index.
FTS scans the entire table to fetch the results I.e reads the every row of the table to find all records meeting the given requirements
Even if the query selects just a few rows from the table, all rows in the entire table will be examined
Table scan on larger tables take excessive amount of time and cause performance problem
Following are the situations where optimizer chooses Full Table Scans:-
Lack of Index.
The query predicate applies a function to the indexed column.
Select COUNT(*) with B-Tree indexes & Nulls.
Large Amount of Data
Small Table
High Degree of Parallelism
FTS Hint
The table statistics are stale.
The query predicate does not use the leading edge of a B-tree index..
The query is unselective.
Pros:
The cost is predictable, as every time database system needs to scan full table row by row.
When table is less than 2 percent of database block buffer then full scan table is quicker.
Cons:
Full table scan occurs when there is no index or index is not being used by SQL. And the result of full scan table is usually slower that index table scan. The situation is that: the larger the table, the slower of the data returns.
Unnecessary full-table scan will lead to a huge amount of unnecessary I/O with a process burden on the entire database.
Index Scan
The Index Scans is a access path used by the query optimizer to produce the best Sql Engine - (Physical|Execution) Plan.
In this method, a row is retrieved by traversing the index, using the indexed column values specified by the statement
An index scan retrieves data from an index based on the value of one or more columns in the index.
To perform an index scan, Oracle searches the index for the indexed column values accessed by the statement
If the statement accesses only columns of the index, then Oracle reads the indexed column values directly from the index, rather than from the table.
The index contains :
not only the indexed value,
but also the rowids of rows in the table having that value.
Therefore, if the statement accesses other columns in addition to the indexed columns, then Oracle can find the rows in the table by using either a table access by rowid or a Cluster Scan/Access.
An index scan can be one of the following types:
Index Unique Scans
This scan returns, at most, a single rowid. Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.
Example
An index scan is performed on the jobs and departments tables, using the job_id_pk and dept_id_pk indexes respectively.
Index Range Scans
An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.
If data must be sorted by order, then use the ORDER BY clause, and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort.
Example
In this example, the order has been imported from a legacy system, and you are querying the order by the reference used in the legacy system. Assume this reference is the order_date.
This should be a highly selective query, and you should see the query using the index on the column to retrieve the desired rows. The data returned is sorted in ascending order by the rowids for the order_date. Because the index column order_date is identical for the selected rows here, the data is sorted by rowid.
Index Range Scans Descending
An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order. Usually, this scan is used when ordering data in a descending order to return the most recent data first, or when seeking a value less than a specified value.
Index Skip Scans
Index skip scans improve index scans by non-prefix columns. Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index be split logically into smaller sub-indexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
The number of logical sub-indexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the non-leading key of the index.
Consider, for example, a table employees (sex, employee_id, address) with a composite index on (sex, employee_id). Splitting this composite index would result in two logical sub-indexes, one for M and one for F.
For this example, suppose you have the following index data:
The index is split logically into the following two sub-indexes:
The first sub-index has the keys with the value F.
The second sub-index has the keys with the value M.
The column sex is skipped in the following query:
A complete scan of the index is not performed, but the sub-index with the value F is searched first, followed by a search of the sub-index with the value M.
Full Index Scan
A full index scan eliminates a sort operation, because the data is ordered by the index key. It reads the blocks singly. A full scan is used in any of the following situations:
An ORDER BY clause that meets the following requirements is present in the query:
All of the columns in the ORDER BY clause must be in the index.
The order of the columns in the ORDER BY clause must match the order of the leading index columns.
The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.
The query requires a sort merge join. A full index scan can be done instead of doing a full table scan followed by a sort if the query meets the following requirements:
All of the columns referenced in the query must be in the index.
The order of the columns referenced in the query must match the order of the leading index columns.
The query can contain all of the columns in the index or a subset of the columns in the index.
A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index. The columns do not need to be in the same order in the index and the GROUP BY clause. The GROUP BY clause can contain all of the columns in the index or a subset of the columns in the index.
Fast Full Index Scans
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can :
use multiblock I/O
and be parallelized just like a table scan.
Note: Setting PARALLEL for indexes will not impact the cost calculation.
You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint.
Index Joins
An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.
Bitmap Indexes
A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.
Partition Range Scan
Oracle optimizes partitioned queries differently than regular queries.
Oracle tries to pick the fastest elapsed time to the data, not the lowest cost.
The Optimizer typically scans partitions first
Scans only relevant partitions (called pruning)
Try to avoid functions on partitioning key values in WHERE clauses
Try to avoid subqueries on partitioning key values
Maybe avoid indexes on partitioning keys (Oracle uses efficient multi-block scans with fewer I/O than using indexes and single block reads)
Want to avoid “long ops”operations
Oracle Parallel Server and RAC instances can break a SQL up across processors and return the pieces for a much faster response time on larger objects.
Consider where your data is in the partitioned object. If the selected rows are across the partition, it might be considerably faster to do a full-table scan (using multi-block read-ahead of course) than to do single block reads, even on a local-partitioned index.
No comments:
Post a Comment