UNDERSTANDING EXPLAIN PLAN 6

 Miscellaneous Operations

FOR UPDATE

Locks returned rows as a result of the FOR UPDATE clause.

FOR UPDATE places a row-level lock on all the rows that can be retrieved from the SELECT statement.

Using FOR UPDATE allows you to use the WHERE CURRENT OF clause in INSERT, UPDATE, and DELETE commands. A COMMIT will invalidate the cursor, so you will need to reissue the SELECT FOR UPDATE after every commit.

Example

select Name, City, State from COMPANY where City > ‘Roanoke’ and Active_Flag = ‘Y’
for update of Name;

Execution Plan

FOR UPDATE
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

Interpreting the Execution Plan

The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY that satisfy the limiting condition on the City value (City > ‘Roanoke’). The ROWIDs from the index scan are used to query the COMPANY table for the Name and State values. The Active_Flag=‘Y’ criteria is implicitly applied during the TABLE ACCESS BY ROWID operation. The FOR UPDATE operation is then applied to give the user row-level locks on each row returned from the query.

FILTER

Eliminates rows from a result set not matching the selection criteria.

FILTER performs a WHERE clause condition when no index can be used to assist in the evaluation. Unfortunately, the FILTER operation is sometimes implicit. Any FILTER condition that is applied when performing a table access (such as during a TABLE ACCESS BY ROWID) does not show up in the plan. When FILTER shows up in an Explain plan, it usually the result of a missing index or the disabling of an existing index.

The FILTER operation was in a prior example—the CONNECT BY operation’s example. In the query shown in the following listing, the WHERE criteria on the State column is not applied until after the CONNECT BY hierarchy has completed; the resulting rows are filtered to determine which meet the specified State criteria.

Example

select Company_ID, Name from COMPANY where State = ‘VA’
connect by Parent_Company_ID = prior Company_ID start with Company_ID = 1;

Execution Plan

FILTER
CONNECT BY
INDEX UNIQUE SCAN COMPANY_PK
TABLE ACCESS BY ROWID COMPANY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$PARENT

Interpreting the Execution Plan

The plan shows that first the COMPANY_PK index is being used to find the root node (Company_ID = 1), then the index on the Parent_Company_ID column is used to provide values for queries against the Company_ID column in an iterative fashion. After the hierarchy of Company_IDs is complete, the FILTER operation—the WHERE clause related to the State value—is applied. The query does not use the index on the State column, although it is available and the column is used in the WHERE clause.

REMOTE

Accesses an external database through a database link.

REMOTE sends a SQL statement to be executed at a remote node via a database link. The syntax of the SQL statement sent to the remote node is shown in the Other column of PLAN_TABLE.

Example

Since REMOTE requires a remote database access, a database link will be created for this example. The database link connects to the Hobbes account in the database that is identified via the ‘test’ service name in the local tnsnames.ora file.

create database link REMOTE1 connect to hobbes identified by tiger using ‘test’;

In the example query, a local COMPANY is joined to a remote SALES via a NESTED LOOPS join.

Example

select COMPANY.Name from COMPANY, SALES@REMOTE1 where COMPANY.Company_ID = SALES.Company_ID and SALES.Period_ID = 3 and SALES.Sales_Total > 1000;

Execution Plan

NESTED LOOPS
REMOTE
TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK

For the step with the REMOTE operation, you can query PLAN_TABLE for the syntax of the query sent to the remote node:

select Other from PLAN_TABLE where Operation = ‘REMOTE’;

The value of the Other column for this example is:

SELECT "COMPANY_ID","PERIOD_ID","SALES_TOTAL"
FROM "SALES" SALES WHERE "SALES_TOTAL">1000 AND "PERIOD_ID"=3

Interpreting the Execution Plan

The Explain Plan shows that the remote SALES table is used as the driving table for the NESTED LOOPS join (see the NESTED LOOPS operation for a brief discussion of driving tables). The text in the PLAN_TABLE. The Other column shows the query that is executed in the remote database. For each Company_ID value returned by the query of the remote SALES table, the COMPANY_PK index will be checked to see if a matching Company_ID value exists in the COMPANY table. When a match exists, that row is returned to the user by using the NESTED LOOPS operation.

FIRST ROW

Retrieves the first row of a query.

FIRST ROWS uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best response time (minimum resource use to return the first row of the result set).

The following statement changes the goal of the cost-based optimizer for your session to best response time:

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

SEQUENCE

Accesses an Oracle sequence generator to obtain a unique sequence number

SEQUENCE is used when accessing a database sequence via the NextVal and CurrVal pseudo-columns.

Example

Since the SEQUENCE operation requires a sequence to exist, a sequence named COMPANY_ID_SEQ will be created.

create sequence COMPANY_ID_SEQ
start with 1 increment by 1;

In the example query, the next value is selected from the sequence by selecting the NextVal pseudo-column from DUAL.

select COMPANY_ID_SEQ.NextVal  from DUAL;

Execution Plan

SEQUENCE COMPANY_ID_SEQ
TABLE ACCESS FULL DUAL

Interpreting the Execution Plan

The Execution Plan shows that the DUAL table (comprising 1 row, and owned by SYS) is scanned. The COMPANY_ID_SEQ sequence is used to generate the value of the NextVal pseudo-column for the returned row, using the SEQUENCE operation.

INLIST ITERATOR

Performs the next operation once for each value in an IN list predicate.

An INLIST ITERATOR operation appears in the Execution Plan output if an index implements an INLIST predicate.

Example

SELECT * FROM emp WHERE empno IN (7876, 7900, 7902);

Execution Plan

Operation	Options	Object_name
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS	BY ROWID	EMP
INDEX	RANGE SCAN	EMP_EMPNO

The INLIST ITERATOR operation iterates over the operation below it for each value in the IN-list predicate. For partitioned tables and indexes, the three possible types of INLIST columns are:

·         Index column

·         Index and partition column

·         Partition column

Index Column

When the INLIST column empno is an index column but not a partition column, then the plan is as follows (the INLIST operator appears above the table operation but below the partition operation):

Operation	Options	Object_name	Partition Start	Partition Stop
SELECT STATEMENT
PARTITION	INLIST		KEY(INLIST)	KEY(INLIST)
INLIST ITERATOR
TABLE ACCESS	BY ROWID	EMP	KEY(INLIST)	KEY(INLIST)
INDEX	RANGE SCAN	EMP_EMPNO	KEY(INLIST)	KEY(INLIST)

The KEY(INLIST) designation for the partition start and stop keys specifies that an INLIST predicate appears on the index start/stop keys.

Index and Partition Columns

When empno is an indexed and a partition column, the Explain plan contains an INLIST ITERATOR operation above the partition operation:

Operation	Options	Object_name	Partition Start	Partition Stop
SELECT STATEMENT
INLIST ITERATOR
PARTITION	ITERATOR		KEY(INLIST)	KEY(INLIST)
TABLE ACCESS	BY ROWID	EMP	KEY(INLIST)	KEY(INLIST)
INDEX	RANGE SCAN	EMP_EMPNO	KEY(INLIST)	KEY(INLIST)

Partition Column

When empno is a partition column and there are no indexes, then no INLIST ITERATOR operation is allocated:

Operation	Options	Object_name	Partition Start	Partition Stop
SELECT STATEMENT
PARTITION			KEY(INLIST)	KEY(INLIST)
TABLE ACCESS	BY ROWID	EMP	KEY(INLIST)	KEY(INLIST)
INDEX	RANGE SCAN	EMP_EMPNO	KEY(INLIST)	KEY(INLIST)

When emp_empno is a bitmap index, the Execution plan is as follows:

Operation	Options	Object_name
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS	BY INDEX ROWID	EMP
BITMAP CONVERSION		TO ROWIDS
BITMAP INDEX	SINGLE VALUE	EMP_EMPNO

LOAD AS SELECT

Denotes a direct path INSERT based on a SELECT statement.

LOAD AS SELECT bypasses the buffer cache when performing a direct path load based on a SELECT statement.

An Execution plan will denote the presence of a direct load operation with the LOAD AS SELECT operation:

	Rows	Explain Plan
	0	SELECT STATEMENT GOAL: CHOOSE
	0	LOAD AS SELECT
	0	TABLE ACCESS (FULL) OF 'CUSTOMERS'
FIXED TABLE			Accesses a "fixed" (X$) table.

Fixed tables are those not in Oracle’s data dictionary. FIXED TABLE is normally used to optimize V$ and X$ statements. However, join order for a V$ query will be entirely determined by the order of tables in the FROM clause. Be careful when using FIXED TABLE because:

·         Neither the cost-based optimizer nor the rule-based optimizer recognizes the presence of V$ indexes when determining join order or method.

·         There are never any optimizer statistics held against the V$ or X$ tables and consequently the cost-based optimizer has no information to use to determine the best join order.

FIXED INDEX

Accesses an "index" on "fixed" (X$) table.

Where an index exists on a V$ table, it will normally be used whenever the column is used for an exact lookup. The Explain Plan reveals that this is so through the special access path FIXED INDEX. For instance, the following query uses the SID index on V$SESSION:

select * from v$session where sid=171

Rows	Explain Plan
1	FIXED TABLE FIXED INDEX #1 X$KSUSE cpu=1 elapsed=1 logical=0 physical=0

Remembering that the "index" is not really an Oracle B-tree index and in has more in common with a hash cluster, it’s not surprising to see that the index is disabled if a range scan is attempted:

select * from v$session where sid<8

	Rows	Explain Plan
	700	FIXED TABLE FULL X$KSUSE cpu=19 elapsed=19 logical=0 physical=0
TEMP TABLE GENERATION			Creates a temporary dimension table for dimension tables joined by a Star Transformation.

The TEMP TABLE GENERATION operation creates a temporary dimension table for dimension tables that have been joined by a Star Transformation. For each dimension table in the joined set, TEMP TABLE GENERATION creates a temporary table to replace the table in the Execution Plan. A temporary table is created using two steps: create table and insert each. Since there are at least two tables in a join, this operation creates a minimum of four temporary tables.

COLLECTION ITERATOR

Returns certain values from a collection such as VARRAY and nested table.

The Collection Iterator operation returns certain values from a collection such as VARRAY and nested table.

UNDERSTANDING EXPLAIN PLAN 5

 Table Access Options

FULL	Reads every row in the table. Every block is read until the high water mark of the table is reached.
CLUSTER	Accesses data using an index cluster key. An INDEX CLUSTER stores rows from one or more tables in the same segment. Rows with common cluster key values are stored together. In a HASH CLUSTER, the location of rows in a single table is dependent on the hashed value of the table’s key.

TABLE ACCESS CLUSTER returns rows from a table that is stored within a cluster, when the cluster key is used.

The query from the NESTED LOOPS topic will be used here again as an example. For the purposes of this example, assume that the COMPANY table is stored in a cluster, named COMPANY_CLUSTER, and the cluster key is the Company_ID column. The name of the cluster key index (on Company_ID) is COMPANY_CLUSTER_NDX.

Example

select COMPANY.Name from COMPANY, SALES where COMPANY.Company_D = SALES.Company_ID and SALES.Period_ID = 3 and SALES.Sales_Total>1000;

Execution Plan

NESTED LOOPS
TABLE ACCESS FULL SALES
TABLE ACCESS CLUSTER COMPANY
INDEX UNIQUE SCAN COMPANY_CLUSTER_NDX

Interpreting the Execution Plan

The Execution Plan shows that the SALES table is used as the driving table for the query. For each Company_ID value in the SALES table there will be an access to the COMPANY table using the cluster index on the Company_ID column.

HASH	Accesses one or more rows in a table with a matching hash value.

TABLE ACCESS HASH is analogous to TABLE ACCESS BY ROWID; instead of accessing rows by ROWID, the optimizer calculates the location of the row inside the table using a hash formula on the key columns of the table and then uses that value to access the rows.

Example

Assume that the COMPANY table is stored in a hash cluster.  Execute the following SQL statement:

select Name from COMPANY where Company_ID = 12345 and Active_Flag = ‘Y’;

Execution Plan

TABLE ACCESS HASH COMPANY

Interpreting the Execution Plan

Since the Company_ID is the primary key for the COMPANY table, the hash formula uses the Company_ID value to determine the ROWID of the sought row. Since the ROWID will be determined by the hash formula, there is no need for an index access to retrieve the specified row.

BY ROWID

Accesses a single row in a table by specifying its ROWID. This is the fastest way to access a single row. Frequently, the ROWID is obtained by an associated index lookup.

TABLE ACCESS BY ROWID returns a single row from a table, based on the ROWID provided to the operation. This is the fastest way to return data from a table.

Note: TABLE ACCESS BY ROWID may also represent an implicit FILTER operation.

Example

select Name from COMPANY where Company_ID = 12345and Active_Flag = ‘Y’;

Execution Plan

TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK

Interpreting the Execution Plan

As shown in the Execution Plan, the use of the Company_ID column in the query’s WHERE clause allowed the COMPANY_PK index to be used. That index does not also contain the Name column, so Oracle must access the COMPANY table, using the ROWID returned by the index, to get the Name value. An implicit FILTER is then performed to return only the rows with Active_Flag = 'Y'.

BY USER ROWID

Accesses using a ROWID provided by a bind variable, literal or WHERE CURRENT OF CURSOR clause.

TABLE ACCESS BY USER ROWID is used to access records using records provided by a bind variable, literal or WHERE CURRENT OF CURSOR clause.

BY INDEX ROWID

Accesses using a ROWID obtained through an index lookup.

TABLE ACCESS BY INDEX ROWID is used to access records using a ROWID obtained through an index lookup.

BY GLOBAL INDEX ROWID

Accesses using a ROWID obtained from a globally partitioned index.

Indexes can also be partitioned. While it is possible to create a partitioned index on an unpartitioned table, partitioned indexes will more often be created on a table that is itself partitioned.

When an index on a partitioned table is unpartitioned, or is partitioned on different column range conditions than the source table, the index is known as a Global Index.

Note: In general, Local (partitioned) Indexes help release the maximum benefits of partitioning. Avoid using Global Indexes on partitioned tables.

BY LOCAL INDEX ROWID

Accesses using a ROWID obtained from a locally partitioned index.

Indexes can also be partitioned. To take advantage of partition maintenance facilities such as the ability to quickly drop a partition, you should normally create Local Indexes, which are partitioned in the same manner as their source table. When the leading columns of the index are also the columns upon which the index is partitioned then the index is known as a local prefixed index.

Local indexes have some significant advantages over global indexes. When a partitioned table with a global index has a partition split, merge, or move, the corresponding index partition will automatically have the same maintenance operation applied.

The parallel query option is invoked when the SQL statement includes a PARALLEL hint or when the PARALLEL clause has been associated with the table definition. In addition, the table to which the PARALLEL clause has been applied must be accessed by using a full table scan or must be a partitioned table accessed using a local index.

Note: In general, Local (partitioned) Indexes help release the maximum benefits of partitioning. Avoid Global Indexes on partitioned tables.

SAMPLE

Returns a subset of rows as a result of using of the SAMPLE clause

A sample table scan retrieves a random sample of data from a table. This access method is used when the statement's FROM clause includes the SAMPLE clause or the SAMPLE BLOCK clause. To perform a sample table scan when sampling by rows (the SAMPLE clause), Oracle reads a specified percentage of rows in the table and examines each of these rows to determine whether it satisfies the statement's WHERE clause. To perform a sample table scan when sampling by blocks (the SAMPLE BLOCK clause), Oracle reads a specified percentage of the table's blocks and examines each row in the sampled blocks to determine whether it satisfies the statement's WHERE clause.

Oracle does not support sample table scans when the query involves a join or a remote table. However, you can perform an equivalent operation by using a CREATE TABLE AS SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. Additional queries can be written to materialize samples for other tables. Sample table scans require the cost-based optimizer.

Example

The following statement uses a sample table scan to access 1% of the emp table, sampling by blocks:

SELECT * FROM emp SAMPLE BLOCK (1);

Execution Plan

Operation	Options	Object_name
SELECT STATEMENT TABLE ACCESS	SAMPLE	EMP

UNDERSTANDING EXPLAIN PLAN 4

Set Operations

CONCATENATION

Merges multiple result sets in the same way as in an explicit UNION statement. This typically occurs when an Or statement is used with indexed columns.

CONCATENATION performs a UNION ALL (a UNION without elimination of duplicate values) of result sets.

Example

select Name, City, State from COMPANY where State = ‘TX’ and City in (‘Houston’, ‘Austin’, ‘Dallas’);

This query selects from the COMPANY Table based on two criteria in the WHERE clause. The criteria can use the COMPANY$CITY index and the COMPANY$STATE index to obtain ROWIDs for the rows to be returned. Since the NAME column is required by the query, the COMPANY table will have to be accessed; the other column values are already available via the index searches.

The query of the CITY column uses an IN clause, which is functionally equivalent to an OR clause. The query could be rewritten as:

select Name, City, State  from COMPANY where State = ‘TX’ and (City = ‘Houston’
or City = ‘Austin’ or City = ‘Dallas’);

Taking this expansion one step further, the STATE portion of the query can be placed into each section of the OR clause. The revised query would now read:

select Name, City, State from COMPANY where (State = ‘TX’ and City = ‘Houston’)
or (State = ‘TX’ and City = ‘Austin’) or (State = ‘TX’ and City = ‘Dallas’);

This query helps with understanding the plan that is generated.

Execution Plan

CONCATENATION
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE

Interpreting the Execution Plan

The Execution Plan shows that the query is executed as if the IN clause is rewritten as an OR clause, and the other criteria are placed within the OR clauses. Within each OR clause, an AND-EQUAL operation is performed to merge the lists of ROWIDs returned from the index scans. The ROWIDs returned by the AND-EQUAL operations are then used to select the requested columns from the COMPANY table via a TABLE ACCESS BY ROWID operation. The resulting records from each part of the query are then concatenated to carry out the OR clause.

Note: When the query is very complex, the Optimizer may decide not to use the CONCATENATION operation. Instead, it will use partial index range scans. If you want to force the use of the CONCATENATION operation, you may need to use the format shown in the last example query.

INTERSECTION

Compares two result sets and returns only rows common to both. This operation usually only takes place as a result of an explicit use of the INTERSECTION clause.

Set operation.

INTERSECTION is used to merge sets of records returned by multiple queries; in this sense, it is analogous to the index record merge performed by AND-EQUAL. INTERSECTION is used when the INTERSECT clause is used in a query.

Most INTERSECT queries should be rewritten to use joins instead, to improve their ability to use row operations instead of set operations. The following example could be rewritten to use a join, but for purposes of illustration is shown using an INTERSECT clause.

Example

select Company_ID from COMPANY where State = ‘AZ’ and Parent_Company_ID is null
INTERSECT
select Company_ID from COMPETITOR;

The query in the previous listing will select all COMPANY_IDs from the COMPANY table whose state value is ‘AZ’, whose Parent_Company_ID value is null, and whose Company_ID value also is present in the COMPETITOR table.

Execution Plan

PROJECTION
INTERSECTION
SORT UNIQUE
TABLE ACCESS BY ROWID COMPANY
 INDEX RANGE SCAN COMPANY$STATE
SORT UNIQUE
TABLE ACCESS FULL COMPETITOR

Interpreting the Explain Plan

The Execution Plan shows the INTERSECTION operation, along with two operations described later: SORT UNIQUE and PROJECTION.

The Explain Plan shows that each of the queries is executed separately, and the results of the two queries are returned to the INTERSECTION operation. In the first (top) query, the index on the State column is used, and the ROWID values returned from that index scan are used to select records from the COMPANY table. During the TABLE ACCESS BY ROWID on the COMPANY table, the Parent_Company_ID is null criteria is applied (via an implicit FILTER operation).

The second (bottom) query does not have any WHERE clause criteria, so a TABLE ACCESS FULL (full table scan) of the COMPETITOR table is performed. The results of each query are then sorted separately via the SORT UNIQUE operations—only unique Company_ID values will be passed on to the INTERSECTION operation.

The INTERSECTION operation takes the sorted rows from the two queries and returns to the user the rows that are common to both queries via the PROJECTION operation (which makes the two result sets appear as one).

Advanced INTERSECTION Example

A second example of INTERSECTION involves the CONNECT BY operation. Assume that you want to add a row into the COMPANY table, with a Company_ID =10 and Parent_Company_ID =5. How can you be sure that there will be no loops in the hierarchical structure of the data in the COMPANY table? The query in the following listing checks for the intersection of two searches - one up the tree, and one down the tree. If the intersection of these two queries returns a row, then there is a loop in the hierarchy of the Company_ID values.

Example

select Company_ID, Name from COMPANY where State = ‘VA’
connect by Parent_Company_ID = prior Company_ID /*down the tree*/
start with Company_ID = 10 INTERSECTION
select Company_ID, Name from COMPANY where State = ‘VA’
connect by Company_ID = prior Parent_Company_ID /*up the tree*/
start with Company_ID = 5;

If the query in the preceding listing does not return any rows, then there will be no loop in the Company_ID hierarchy if you insert a row with a Company_ID value of 10 and a Parent_Company_ID value of 5.

PROJECTION

Returns a single set of records from the results of multiple queries. PROJECTION is used by the INTERSECTION, MINUS, and UNION operations.

PROJECTION is used by the INTERSECTION, MINUS, and UNION operations to return a single set of records from the results of multiple queries.

Example

select Company_ID from COMPANY MINUS select Company_ID  from COMPETITOR;

Execution Plan

PROJECTION
MINUS
SORT UNIQUE
TABLE ACCESS FULL COMPANY
SORT UNIQUE
TABLE ACCESS FULL COMPETITOR

Interpreting the Execution Plan

The Execution Plan shows that after the MINUS operation is performed, the PROJECTION operation resolves the output into a single set of data for output to the user. PROJECTION is always used in conjunction with the MINUS, INTERSECTION, and UNION operations.

MINUS	Returns all result sets in the first result set, except those appearing in the second result set. This occurs as a result of the MINUS set operator.
UNION ALL	Combines two result sets and returns rows from both are returned.

When a query contains a WHERE clause with multiple conditions combined with OR operators, the optimizer transforms the query into an equivalent compound query that uses the UNION ALL set operator. The optimizer determines whether this modified query will execute more efficiently in the following manner:

·         When each condition individually makes an index access path available, then the optimizer can make the transformation. The optimizer then chooses an Explain Plan for the resulting statement that accesses the table multiple times using the different indexes, and then puts the results together.

·         When any condition requires a full table scan because it does not make an index available, then the optimizer does not transform the statement. The optimizer chooses a full table scan to execute the statement, and Oracle tests each row in the table to determine whether it satisfies any of the conditions.

·         For statements that use the cost-based optimizer, the optimizer may use statistics to determine whether to make the transformation by estimating and then comparing the costs of executing the original statement versus the resulting statement.

·         The cost-based optimizer does not use the OR transformation for IN-lists or ORs on the same column; instead, it uses the INLIST ITERATOR operator.

Example

In the following query, the WHERE clause contains two conditions combined with an OR operator:

SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10;

When there are indexes on both the job and deptno columns, the optimizer may transform this query into the equivalent query as follows:

SELECT * FROM emp WHERE job = 'CLERK' UNION ALL
SELECT * FROM emp WHERE deptno = 10 AND job <> 'CLERK';

When the cost-based optimizer is deciding whether to make a transformation, the optimizer compares the cost of executing the original query using a full table scan with that of executing the resulting query.

With the rule-based optimizer, the optimizer makes this UNION ALL transformation, because each component query of the resulting compound query can be executed using an index. The rule-based optimizer assumes that executing the compound query using two index scans is faster than executing the original query using a full table scan.

VIEW	Accesses a view definition or creates a temporary table to store a result set.

VIEW resolves any query nested deeper than the VIEW operation into a temporary area. The use of VIEW may be caused by correlated queries or by the inability of Oracle to pull a view’s query into the rest of a larger query, forcing it to resolve the view separately.

The example is in two parts: a view is created and then queried. The plan that follows applies only to the query of the view, not to the view creation itself.

Example

create view COMPANY_COUNT as select Zip, COUNT(*) Company_Count
from COMPANY group by Zip;

select Zip, Company_Count from COMPANY_COUNT where Company_Count BETWEEN 10 and 20;

Execution Plan

VIEW COMPANY_COUNT
FILTER
SORT GROUP BY
TABLE ACCESS FULL COMPANY

Interpreting the Explain Plan

Because there is a set operation (SORT GROUP BY) within the view syntax, the optimizer must resolve the view before executing the conditions specified in the query. All the rows are fetched from COMPANY table using a full table scan, then they are sorted and counted by Zip during the SORT GROUP BY operation. The WHERE clause condition in the query is applied during the FILTER operation on the result of the view.

WINDOW

Consists of a physical or logical set of rows used for calculating the result of an analytical function.

The WINDOW operation consists of a physical or logical set of rows used for calculating the result of an analytical function. The function is then applied to all the rows in the window. The window "slides" through the query result set or partition from top-to-bottom.