Execution Plan image:
Row or Set operation.
REMOTE .PLAN_TABLE column of Other 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
Since REMOTE requires a remote database access, a database link is 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.
select COMPANY.Name
from COMPANY, SALES@REMOTE1
where COMPANY.Company_ID = SALES.Company_ID
and SALES.Period_ID = 3
and SALES.Sales_Total > 1000;
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
The Execution 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 is 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.
Execution plan image:
The SELECT statement retrieves data from one or more tables, object tables, views, object views, or materialized views.
Note: When the result (or part of the result) of a SELECT statement is equivalent to an existing materialized view, Oracle may use the materialized view in place of one or more tables specified in the SELECT statement. This substitution is called query rewrite, and takes place only if cost optimization is enabled and the Oracle QUERY_REWRITE_ENABLED parameter is set to TRUE.
To select data from a table or materialized view, the table or materialized view must be included in your own schema or you must have the SELECT privilege on the table or materialized view.
To select rows from the base tables of a view
You must have the SELECT privilege on the view.
The owner of the schema containing the view must have the SELECT privilege on the base tables.
The SELECT ANY TABLE system privilege also allows you to select data from any table or any materialized view or any view's base table.
Execution Plan image: , , or
Join operation.
A SEMI JOIN is a join which returns rows from a table which have matching rows in a second table but which does not return multiple rows if there are multiple matches. This is usually expressed in Oracle using a WHERE EXISTS sub-query.
Execution Plan image:
Row operation.
SEQUENCE pseudo-columns. CurrVal and NextVal is used when accessing a database sequence via the
Since the SEQUENCE operation requires a sequence to exist, a sequence named COMPANY_ID_SEQ is 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;
SEQUENCE COMPANY_ID_SEQ
TABLE ACCESS FULL DUAL
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.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center