Reading Nested Loop Xplans
Date: Thu, 13 Sep 2012 10:26:09 -0400
Message-ID: <CADpeV5yggtQK4cqBMSF6nFYMwp_8=e8398LePq3TS_-XE5W40A_at_mail.gmail.com>
Trying to figure out why there are 2 'nested loops' and a 'hash unique' steps in this xplan. This is how am reading it. 1) access table_02 to get rowset matching time stamp (step 5) 2) For each row returned from step 1, get a matching row from table_01 where m.message_id = e.message_id using index PK_MSG_PERSON (step 6). 3) above 2 steps form nested loop 1 (step 3) From this point, I'm not clear on what happens in step 2 & 1. Any insights will be appreciated.
SELECT /*+ gather_plan_statistics */ distinct
m.MESSAGE_ID,m.SENDER_CODE,m.RECEIVER_CODE
,m.doc_type_name,m.receiver_doc_type_name,
m.sender_id,m.receiver_id,m.sender_channel_id,
m.receiver_channel_id,m.control_number,e.TYPE_ID,
nvl(e.INSTANCE_MESSAGE,'null'), m.RECEIVED_TIMESTAMP
FROM
table_01 m, table_02 e
WHERE
m.message_id = e.message_id
and
e.INSTANCE_TIMESTAMP >= dt_tm_01
AND
e.INSTANCE_TIMESTAMP <= dt_tm_02
AND
(m.DELIVERY_STATUS = 's'
or
e.type_id in(1,2,3))
Plan hash value: 469965103
| Id | Operation | Name | Starts
| E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem
| Used-Mem | Used-Tmp|
| 0 | SELECT STATEMENT | | 1
| | 37076 |00:01:17.84 | 264K| 24989 | 8130 | |
| | |
| 1 | HASH UNIQUE | | 1
| 1 | 37076 |00:01:17.84 | 264K| 24989 | 8130 | 67M|
7218K| 11M (1)| 69632 |
| 2 | NESTED LOOPS (2) | |
1 | | 50495 |00:01:01.65 | 264K| 16859 | 0 | | | | |
| 3 | NESTED LOOPS (1) | |
1 | 1 | 53162 |00:00:25.24 | 214K| 4005 | 0 | | | | | |* 4 | TABLE ACCESS BY INDEX ROWID| table_02 | 1 | 1 | 53162 |00:00:01.95 | 57665 | 102 | 0 | | | | | |* 5 | INDEX RANGE SCAN | EXP_INST_INST_TMSTMP_IDX | 1
| 1 | 59198 |00:00:00.07 | 348 | 1 | 0 | |
| | |
|* 6 | INDEX UNIQUE SCAN | PK_MSG_PERSON | 53162 | 1 | 53162 |00:00:17.99 | 157K| 3903 | 0 | | | | | |* 7 | TABLE ACCESS BY INDEX ROWID | table_01 | 53162 | 1 | 50495 |00:00:57.13 | 49705 | 12854 | 0 | | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
4 - filter("E"."MESSAGE_ID" IS NOT NULL) 5 - access("E"."INSTANCE_TIMESTAMP">=dt_tm_01 AND "E"."INSTANCE_TIMESTAMP"<=dt_tm_02)
6 - access("M"."MESSAGE_ID"="E"."MESSAGE_ID") 7 - filter(("M"."DELIVERY_STATUS"='s' OR INTERNAL_FUNCTION("E"."TYPE_ID")))
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 13 2012 - 09:26:09 CDT