Execution Plan image:
Table Access operation.
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.
select Name
from COMPANY
where Company_ID = 12345
and Active_Flag = 'Y';
TABLE ACCESS BY ROWID COMPANY
INDEX UNIQUE SCAN COMPANY_PK
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'.
Execution Plan image:
Table Access operation.
TABLE ACCESS BY USER ROWID is used to access records using records provided by a bind variable, literal or WHERE CURRENT OF CURSOR clause.
Execution Plan image:
Table Access operation.
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 is 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.
select COMPANY.Name
from COMPANY, SALES
where COMPANY.Company_D = SALES.Company_ID
and SALES.Period_ID = 3
and SALES.Sales_Total>1000;
NESTED LOOPS
TABLE ACCESS FULL SALES
TABLE ACCESS CLUSTER COMPANY
INDEX UNIQUE SCAN COMPANY_CLUSTER_NDX
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 is an access to the COMPANY table using the cluster index on the Company_ID column.
Execution Plan image:
Table Access operation.
TABLE ACCESS FULL returns rows from a table when the ROWID is not available for the row search. Oracle scans each block in the given table until all rows are read.
select *
from COMPANY;
TABLE ACCESS FULL COMPANY
Since no restraining conditions are placed on the query, all records and all columns are returned. Oracle sequentially reads through all of the Company table blocks and returns them to the user as output.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center