Explain the Explain Plan: Access Methods & Join Methods

 

 

 

 

 

 

Full Table Scan

·       A SELECT COUNT(*) query is issued, and an index exists, but the indexed column contains nulls.

·       The table statistics are stale.

For example, a table was small, but now has grown large. If the table statistics are stale and do not reflect the current size of the table, then the optimizer does not know that an index is now most efficient than a full table scan.

·       The table is small.

If a table contains fewer than n blocks under the high water mark, where n equals the setting for the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, then a full table scan may be cheaper than an index range scan.

·       No index exists.

If no index exists, then the optimizer uses a full table scan.

 

SELECT salary

FROM   hr.employees

WHERE  salary > 4000;

 

Table Access by Rowid

The database accesses a table by rowid after a scan of one or more indexes.

SELECT * 

FROM   employees 

WHERE  employee_id > 190;

 

Index Unique Scans

The database performs a unique scan when the following conditions apply:

• A query predicate references all of the columns in a unique index key using an equality operator, such as WHERE prod_id=10.
• A SQL statement contains an equality predicate on a column referenced in an index that was created with the CREATE UNIQUE INDEX statement.

SELECT * FROM t_table WHERE prod_id=10;

 

Index Range Scans

One or more leading columns of an index are specified in conditions

 

SELECT *

FROM   employees

WHERE  department_id = 20

AND    salary > 1000;

 

Index Full Scans

·       A predicate references a column in the index. This column need not be the leading column.

·       No predicate is specified, but all of the following conditions are met:

o   All columns in the table and in the query are in the index.

o   At least one indexed column is not null.

·       A query includes an ORDER BY on indexed non-nullable columns.

 

SELECT department_id, department_name

FROM   departments

ORDER BY department_id;

 

Index Fast Full Scans

SELECT /*+ INDEX_FFS(departments dept_id_pk) */ COUNT(*)

FROM   departments;

 

Index Skips Scans

when the initial column of a composite index is "skipped" or not specified in the query. For example, if the composite index key is (cust_gender,cust_email), then the query predicate does not reference the cust_gender column.

 

Index Join Scans

The optimizer considers an index join in the following circumstances:

·       A hash join of multiple indexes retrieves all data requested by the query, without requiring table access.

·       The cost of retrieving rows from the table is higher than reading the indexes without retrieving rows from the table. An index join is often expensive. For example, when scanning two indexes and joining them, it is often less costly to choose the most selective index, and then probe the table.

Scan the first index to retrieve rowids.

Scan the second index to retrieve rowids.

Perform a hash join by rowid to obtain the rows.

Bitmap Indexes

Bitmap indexes are suitable for low cardinality data that is infrequently modified. Data has low cardinality when the number of distinct values in a column is low in relation to the total number of rows.

SELECT *

FROM   customers

WHERE  cust_gender = 'M'

AND    cust_marital_status = 'single'

 

 

 

 

 

 

Nested Loops Join

Nested loops joins are useful when small subsets of data are being joined and if there is an efficient way of accessing the second table (for example an index lookup).

For every row in the first table (the outer table), Oracle accesses all the rows in the second table (the inner table) looking for a match. You can think of it as a set of embedded FOR loops. 

The optimizer uses nested loops joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.

Hash Joins

Hash joins are used for joining large data sets. The Optimizer uses the smaller of the two tables or data sources to build a hash table, based on the join key, in memory. It then scans the larger table and performs the same hashing algorithm on the join column(s). It then probes the previously built hash table for each value and if they match, it returns a row.

The optimizer uses a hash join to join two tables if they are joined using an equi-join and if either of the following conditions are true:

• A large amount of data must be joined.

• A large fraction of a small table must be joined.

Sort Merge Joins

Sort Merge joins are useful when the join condition between two tables is an in-equality condition such as, <, <=, >, or >=. Sort merge joins can perform better than nested loop joins for large data sets. The join consists of two steps:

1. Sort join operation: Both the inputs are sorted on the join key.
2. Merge join operation: The sorted lists are merged together.

A Sort Merge join is more likely to be chosen if there is an index on one of the tables that will eliminate one of the sorts.

In general, hash joins perform better than sort merge joins. However, sort merge joins can perform better than hash joins if both of the following conditions exist:

• The row sources are sorted.

• A sort operation is not required.