Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    280

    Unanswered: Documentation on plan operator "attributes"?

    I'm looking for reference information on ">", "^" and "<" that is sometime associated with operators in plans. Example:

    Code:
                                               291749
                                              >^HSJOIN
                                               (   6)
                                               9687.91
                                               5156.26
                           /----------------------+------------------
                        291749
                        HSJOIN<
                        (   7)
                        9660.04
                        5155.26
    /-----------------------+------------------------\
    303905                                        62201.3
    ^HSJOIN                                       >HSJOIN
    (   8)                                         (  11)
    Any pointers to where this is described?
    --
    Lennart

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    hi,
    ">" means it is a outer join ( > means outer table left join inner table )
    "^" means it is a early out join ( early out means we can process the next row of outer table if we find the first row that matched or not matched in inner table )
    "x" means it is a anti join ( anti join means we need to find the rows that unmatched ( eg, not exists ))

    And i never see the "<" , can you provide more detaied info of the access plan,i guess maybe it means a right out join

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by fengsun2 View Post
    hi,
    ">" means it is a outer join ( > means outer table left join inner table )
    "^" means it is a early out join ( early out means we can process the next row of outer table if we find the first row that matched or not matched in inner table )
    "x" means it is a anti join ( anti join means we need to find the rows that unmatched ( eg, not exists ))

    And i never see the "<" , can you provide more detaied info of the access plan,i guess maybe it means a right out join

    Thanks for your answer, and sorry for the delay. I grabbed a plan out of thin air so I had some trouble locating it again, finally found it. From the section details it looks as if you are right about the right out join.

    You don't happen to have a link to where what you described above is located in the docs?

    Code:
                                               291749                                        
                                              HSJOIN<                                       
                                              (   7)                                        
                                              9660.04                                       
                                              5155.26
    
    
            7) HSJOIN: (Hash Join)
                    Cumulative Total Cost:          9660.04
                    Cumulative CPU Cost:            1.08339e+09
                    Cumulative I/O Cost:            5155.26
                    Cumulative Re-Total Cost:       9660.04
                    Cumulative Re-CPU Cost:         1.08339e+09
                    Cumulative Re-I/O Cost:         5155.26
                    Cumulative First Row Cost:      9660.04
                    Estimated Bufferpool Buffers:   3581
    
                    Arguments:
                    ---------
                    BITFLTR : (Hash Join Bit Filter used)
                            FALSE
                    EARLYOUT: (Early Out flag)
                            NONE
                    HASHCODE: (Hash Code Size)
                            24 BIT
                    HASHTBSZ: (Number of hash table entries)
                            62201
                    JN INPUT: (Join input leg)
                            OUTER
                    OUTERJN : (Outer Join type)
                            RIGHT
                    TEMPSIZE: (Temporary Table Page Size)
                            4096
    
                    Predicates:
                    ----------
                    3) Predicate used in Join
                            Comparison Operator:            Equal (=)
                            Subquery Input Required:        No
                            Filter Factor:                  1.54338e-05
    
                            Predicate Text:
                            --------------
                            (Q10.$C6 = Q3.$C2)
    
    
                    Input Streams:
                    -------------
                            5) From Operator #8
    
                                    Estimated number of rows:       303905
                                    Number of columns:              3
                                    Subquery predicate ID:          Not Applicable
    
                                    Column Names:
                                    ------------
                                    +Q3.FOREIGNDIPLOMAUPSEC_ID+Q3.ORIGIN_TAG
                                    +Q3.UPSEC_COURSE
    
                            16) From Operator #11
    
                                    Estimated number of rows:       62201.3
                                    Number of columns:              10
                                    Subquery predicate ID:          Not Applicable
    
                                    Column Names:
                                    ------------
                                    +Q10.UPSEC+Q10.COUNTRY+Q10.UPSEC_ID
                                    +Q10.FOREIGNDIPLOMAUPSEC_ID
                                    +Q10.KIND_OF_QUAL_RATING+Q10.PERSON_ID
                                    +Q10.LEAVE_DATE+Q10.ORIGINAL_QUAL_RATING
                                    +Q10.CONVERTED_QUAL_RATING
                                    +Q10.FOREIGN_UPSEC_CR_SCALE_ID
    
                    Output Streams:
                    --------------
                            17) To Operator #6
    
                                    Estimated number of rows:       291749
                                    Number of columns:              12
                                    Subquery predicate ID:          Not Applicable
    
                                    Column Names:
                                    ------------
                                    +Q11.UPSEC+Q11.COUNTRY
                                    +Q11.FOREIGN_UPSEC_CR_SCALE_ID
                                    +Q11.UPSEC_COURSE+Q11.UPSEC_ID
                                    +Q11.FOREIGNDIPLOMAUPSEC_ID
                                    +Q11.KIND_OF_QUAL_RATING+Q11.PERSON_ID
                                    +Q11.LEAVE_DATE+Q11.ORIGINAL_QUAL_RATING
                                    +Q11.CONVERTED_QUAL_RATING+Q11.ORIGIN_TAG
    --
    Lennart

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    hi,
    I didn't find any doc or link to describe these symbols.
    It is just my conclusion after i have analyzed hundreds of execution plans.

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by fengsun2 View Post
    hi,
    I didn't find any doc or link to describe these symbols.
    It is just my conclusion after i have analyzed hundreds of execution plans.
    Ok, thanks
    --
    Lennart

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by lelle12 View Post
    I'm looking for reference information on ">", "^" and "<" that is sometime associated with operators in plans.
    [...]
    Any pointers to where this is described?
    I noticed that in 10.5 there is a section in output of db2exfmt that describes this. Not sure when this was introduced, I have not seen it before. Example:

    Code:
    Operator Symbols :
    ------------------
    
       Symbol      Description
       ---------   ------------------------------------------
       >JOIN     : Left outer join
        JOIN<    : Right outer join
       >JOIN<    : Full outer join
       xJOIN     : Left antijoin
        JOINx    : Right antijoin
       ^JOIN     : Left early out
        JOIN^    : Right early out
    
    Extended Diagnostic Information:
    --------------------------------
    --
    Lennart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •