Question on an execution plan

From: bonminh lam <hansmayer1962_at_hotmail.com>
Date: 6 Dec 2001 02:31:59 -0800
Message-ID: <3c6b1bcf.0112060231.2efb1106_at_posting.google.com>

Hello,

in the Oracles reference for optimizer execution plan, one finds the following statement:

FILTER: An operation that accepts a set of rows, eliminates some of them, and
returns the rest.

So it means to mean FILTER accepts the result set of a single other operation, whatever it is. It is also clear that JOIN operations normally accept 2 sets of rows. I am just wondering how I should interpret the following plan:

 ID PAR_ID LEV OP OBJECT_TYPE

--- ------ ---- --------------------------------------------------
--------------------
  1      0    1 FILTER                        <= Parent of 3 sets or
rows! (id = 2, 9, 12)
  2      1    2   MERGE JOIN
  3      2    3     SORT JOIN
  4      3    4       HASH JOIN
  5      4    5         INDEX FAST FULL SCAN XCAFF_GRP            
NON-UNIQUE
  6      4    5         INDEX FAST FULL SCAN CUST_DSTMP_IX2       
NON-UNIQUE
  7      2    3     SORT JOIN
  8      7    4       INDEX FAST FULL SCAN SYS_C00593             
UNIQUE
  9      1    2   SORT AGGREGATE
 10      9    3     TABLE ACCESS BY INDEX ROWID XCAFF
 11     10    4       INDEX RANGE SCAN SYS_C00673                 
UNIQUE
 12      1    2   INDEX FULL SCAN SYS_C00593                      

UNIQUE It shows that the FILTER operation which is on level 1, has 3 children! How should I understand that? How are the 3 result sets combined?

Thanks in advance for any enlightment.

As additional info, this plan output is produced my customized SQLPLUS statement:

select

   id, parent_id par_id, level lev,
   lpad(' ', 2*(level-1)) || operation || ' '|| options || ' ' || object_name op,

   object_type
from plan_table
where statement_id = 'LAMBONMI:120601100501' connect by prior id = parent_id and prior statement_id = 'LAMBONMI:120601100501'
start with id = 1

And the actual query that is associated with the plan is such:

SELECT /*+ use_merge(cust,xgac) */

       xgac.xcgac_gac_id, xgac.xcgac_gat_id, xaff.xcaff_log_stamp,
       cust.ROWID rid
  FROM t3 xaff,
       xgac,
       cust
 WHERE xgac.xcgac_cst_id = xaff.xcaff_cst_id_org
   AND cust.cust_id = xaff.xcaff_cst_id

   AND xaff.xcaff_seq = 1
   AND xaff.xcaff_log_stamp = (SELECT MAX (c.xcaff_log_stamp)

                                 FROM t3 c
                                WHERE c.xcaff_cst_id =
xaff.xcaff_cst_id
                                  AND c.xcaff_seq = 1)
   AND NOT EXISTS ( SELECT NULL
                      FROM xgac d
                     WHERE d.xcgac_cst_id = xaff.xcaff_cst_id)

Bonminh Lam

PS: Please do not reply to me by email. Received on Thu Dec 06 2001 - 11:31:59 CET