Database Reference
In-Depth Information
Query Blocks and Object Identifiers
This section lists all the query blocks along with the tables in each. Since the query executed did
not contain any subselects, there is but a single query block:
Registered qb: SEL$1 0x4aea6b4 (PARSER)
signature (): qb_name=SEL$1 nbfros=5 flg=0
fro(0): flg=4 objn=51905 hint_alias="D"@"SEL$1"
fro(1): flg=4 objn=51910 hint_alias="EMP"@"SEL$1"
fro(2): flg=4 objn=51908 hint_alias="J"@"SEL$1"
fro(3): flg=4 objn=51900 hint_alias="L"@"SEL$1"
fro(4): flg=4 objn=51910 hint_alias="MGR"@"SEL$1"
The object identifiers (
objn
) may be used to determine the owner(s) of the tables.
SQL> SELECT owner, object_name, object_type
FROM dba_objects WHERE object_id IN (51905, 51910);
OWNER OBJECT_NAME OBJECT_TYPE
----- ----------- -----------
HR DEPARTMENTS TABLE
HR EMPLOYEES TABLE
Query Transformations Considered
The optimizer considers several query transformations. Note the SQL identifier “2ck90xfmsza4u”
in the unparsed query subsection, which may be used to retrieve past execution plans for the
statement with the packaged procedure
DBMS_XPLAN.DISPLAY_AWR
or Statspack (see Chapter 25).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
CBQT: Validity checks failed for 2ck90xfmsza4u.
CVM: Considering view merge in query block SEL$1 (#0)
Query block (04AEA6B4) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "EMP"."LAST_NAME" "LAST_NAME","EMP"."FIRST_NAME" "FIRST_NAME",
"J"."JOB_TITLE" "JOB_TITLE","D"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
"L"."CITY" "CITY","L"."STATE_PROVINCE" "STATE_PROVINCE"
,"L"."POSTAL_CODE" "POSTAL_CODE","L"."STREET_ADDRESS" "STREET_ADDRESS",
"EMP"."EMAIL" "EMAIL","EMP"."PHONE_NUMBER" "PHONE_NUMBER",
"EMP"."HIRE_DATE" "HIRE_DATE","EMP"."SALARY" "SALARY",
"MGR"."LAST_NAME" "LAST_NAME"
FROM "HR"."EMPLOYEES" "EMP","HR"."EMPLOYEES" "MGR",
"HR"."DEPARTMENTS" "D", "HR"."LOCATIONS" "L","HR"."JOBS" "J"
WHERE "L"."CITY"=:B1 AND "EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID"
