Example Explanation of an Oracle Statement
You can use the SQL Trace facility to view explanations of specific Oracle statements. From within a trace file display, you use the Explain SQL function to display more information about a specific database request. The Explain function is available only for PREPARE and REOPEN operations. To explain a request:
The Explain screen shows you the database's strategy for carrying out the selected operation.
For example, if you are working with an ORACLE database, you can show the explanation for the following statement:
select * from fllog where flcode = '00000123'.
The system provides the following explanation:
Operation |
Options: |
Object Name |
ID |
PAID |
POS | |||||
SELECT STATEMENT |
0 |
|||||||||
TABLE ACCESS BY ROWID |
FLLOG |
1 |
0 |
1 | ||||||
INDEX UNIQUE SCAN |
FLLOG___0 |
2 |
1 |
1 | ||||||
QUERY PLAN
SELECT STATEMENT |
||
|
TABLE ACCESS BY ROWID |
FLLOG | |
|
INDEX UNIQUE SCAN |
FLLOG___0(UNIQUE) |
The fields in the explanation have the following meanings:
OPERATION |
Identifies the operation name. |
OPTIONS |
Operation attributes. |
OBJECT NAME |
Identifies the object involved in the operation. |
ID |
Specifies the operation's ID number. |
PAID |
Specifies the ID number that the current operation transfers its results to. This is important if nested accesses on various hierarchy levels are involved. |
Position |
Identifies the next number for operations working on the same hierarchy level. |
In the example above, the key is fully qualified. The database can use the primary key index FLLOG__0 to access the table records. Every transparent table in the ABAP Dictionary has a primary key. The system automatically creates an index for this key. The primary key index is also unique, meaning that there is only one index entry for every line in the table. As a result, the system uses the UNIQUE SCAN operation.
The UNIQUE SCAN has the ID 2 and parent ID 1. This means that the operation passes its results to the operation with ID 1. ID 1 belongs to the TABLE ACCESS operation. TABLE ACCESS can directly access one record because of the uniqueness of the BY ROWID index. Once the system chooses an access strategy, it sends the SELECT statement with ID 0 to the database.
If the SELECT statement does not specify a fully qualified key, the database could be forced to read the records using a FULL TABLE SCAN. In this case, no index is available and the database reads the entire table in packages.
If the index is ambiguous, the database uses a RANGE SCAN. The RANGE SCAN scans over an index area that might contain several sets of retrieved data.
The NESTED LOOP operation exists for nested reads where several indexes are joined together within one database access.