I have a question on order of execution of the rowsource operations while having column level subqueries
As per explain/execution plan the subquery is getting executed first then the outer table.
I thought that the outer table(oe_order_lines_all) should execute first then the subqueries becoz the subqueries are driven by the values of the outer table. Below are details of the query
Can anyone explain the reason and why this is happening? and what the :B1 imply in predicate information
oracle db version - 10.2.0.5.0
SELECT (SELECT head.order_number
FROM ont.oe_order_headers_all head
WHERE head.header_id = line.header_id) order_no,
(SELECT cust.account_name
FROM ar.hz_cust_accounts cust
WHERE cust.cust_account_id = line.sold_to_org_id) cust_name,
line.line_number
FROM ont.oe_order_lines_all line
WHERE creation_date >= '01-FEB-2011'
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5181 (100)| | 10 |00:00:08.06 | 55367 | 7626 |
| 1 | TABLE ACCESS BY INDEX ROWID| OE_ORDER_HEADERS_ALL | 9 | 1 | 10 | 2 (0)| 00:00:01 | 9 |00:00:00.01 | 20 | 0 |
|* 2 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_U1 | 9 | 1 | | 1 (0)| 00:00:01 | 9 |00:00:00.01 | 11 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID| HZ_CUST_ACCOUNTS | 2 | 1 | 12 | 2 (0)| 00:00:01 | 2 |00:00:00.03 | 6 | 4 |
|* 4 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 2 | 1 | | 1 (0)| 00:00:01 | 2 |00:00:00.02 | 4 | 2 |
|* 5 | TABLE ACCESS FULL | OE_ORDER_LINES_ALL | 1 | 2 | 40 | 5181 (2)| 00:01:03 | 10 |00:00:08.06 | 55367 | 7626 |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HEAD"."HEADER_ID"=:B1)
4 - access("CUST"."CUST_ACCOUNT_ID"=:B1)
5 - filter("CREATION_DATE">=TO_DATE(' 2011-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Edited by: BluShadow on 14-Mar-2011 11:54
added {noformat}
{noformat} tags as per the SQL and PL/SQL FAQ