Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    4

    Unanswered: Need help reading Explain plan output

    Hello,

    one sql statement is causing severe performance issue. The problem occurs only in UDB environment, the same statemnt on the mainframe is running fine. I have an explain output from the sql. The statement itself is not that complicated, it is 3 selects and union all. Explain output is pretty big, but I could not find anything unusual. I'm new to db2 and I could be missing stuff. I am posting the sql and the explain output below and I really appreciate your response.

    Thank you

    SQL Statement:

    SELECT DISTINCT CUS_CID_CUST_ID , 'AGENT', CUS_NME_SHORT_NAME
    FROM S2USER.VLS_CUSTOMER , S2USER.VLS_DEAL_ADMIN ,
    S2USER.VLS_FAM_GLOBAL2 , S2USER.VLS_CUST_CONTACT,
    S2USER.VLS_CUST_CON_PURP, S2USER.VLS_CONT_PURP
    WHERE DAD_PID_DEAL ='-Q6V797H'AND DAD_CID_CUST_ID =CUS_CID_CUST_ID AND CON_CID_CUST_ID =CUS_CID_CUST_ID AND CCP_RID_CONTACT = CON_RID_CONTACT AND CCP_CDE_PURPOSE =GB2_CDE_CODE AND CPU_CDE_CONT_PURP =CON_CDE_PURPOSE AND GB2_TID_TABLE_ID ='CPU' AND GB2_CDE_FKEY1 ='CRDT'
    UNION ALL
    SELECT DISTINCT CUS_CID_CUST_ID , 'LENDR', CUS_NME_SHORT_NAME
    FROM S2USER.VLS_CUSTOMER , S2USER.VLS_FAC_COMMIT_TRN,
    S2USER.VLS_FAC_TR_SG_SHR , S2USER.VLS_FACILITY ,
    S2USER.VLS_FAM_GLOBAL2 , S2USER.VLS_CUST_CONTACT ,
    S2USER.VLS_CUST_CON_PURP, S2USER.VLS_CONT_PURP
    WHERE FAC_PID_DEAL ='-Q6V797H'AND FTR_PID_FACILITY =
    FAC_PID_FACILITY AND FTS_CID_LENDER =CUS_CID_CUST_ID AND
    FTS_RID_FAC_TRAN =FTR_RID_COMMIT_TRN AND CON_CID_CUST_ID = FTS_CID_LENDER AND CCP_RID_CONTACT =CON_RID_CONTACT AND
    CCP_CDE_PURPOSE =GB2_CDE_CODE AND CPU_CDE_CONT_PURP =
    CON_CDE_PURPOSE AND GB2_TID_TABLE_ID ='CPU'AND
    GB2_CDE_FKEY1 ='CRDT'
    UNION ALL
    SELECT DISTINCT CUS_CID_CUST_ID , 'BORR', CUS_NME_SHORT_NAME
    FROM S2USER.VLS_CUSTOMER , S2USER.VLS_DEAL_BORROWER,
    S2USER.VLS_FAM_GLOBAL2 , S2USER.VLS_CUST_CONTACT,
    S2USER.VLS_CUST_CON_PURP, S2USER.VLS_CONT_PURP
    WHERE DBR_PID_DEAL ='-Q6V797H'AND DBR_CID_CUST_ID =CUS_CID_CUST_ID
    AND CON_CID_CUST_ID =CUS_CID_CUST_ID AND CCP_RID_CONTACT = CON_RID_CONTACT AND CCP_CDE_PURPOSE =GB2_CDE_CODE AND
    CPU_CDE_CONT_PURP =CON_CDE_PURPOSE AND GB2_TID_TABLE_ID ='CPU'AND GB2_CDE_FKEY1 ='CRDT'
    FOR
    FETCH ONLY


    Please look at the the explain plan output below, in my next post. It is posted below because the text was too long and didn't fit in this window.

  2. #2
    Join Date
    Nov 2008
    Posts
    4
    Please see below... Thanks!

  3. #3
    Join Date
    Nov 2008
    Posts
    4
    Intra-Partition Parallelism Degree = 2
    Section Code Page = 819
    Estimated Cost = 6585.949219
    Estimated Cardinality = 0.000003

    (
    | Process Using 2 Subagents
    | | Access Table Name = S2USER.TLS_DEAL_ADMIN ID = 3,534
    | | | Index Scan: Name = S2USER.XDADUC01 ID = 1
    | | | | Regular Index (Clustered)
    | | | | Index Columns:
    | | | | | 1: DAD_PID_DEAL (Ascending)
    | | | #Columns = 1
    | | | Parallel Scan
    | | | Single Record
    | | | Fully Qualified Unique Key
    | | | Skip Inserted Rows
    | | | Skip Deleted Rows
    | | | #Key Columns = 1
    | | | | Start Key: Inclusive Value
    | | | | | | 1: '-Q6V797H'
    | | | | Stop Key: Inclusive Value
    | | | | | | 1: '-Q6V797H'
    | | | Data Prefetch: None
    | | | Index Prefetch: None
    | | | Lock Intents
    | | | | Table: Intent Share
    | | | | Row : Next Key Share
    | | Nested Loop Join
    | | | Access Table Name = S2USER.TLS_CUST_CONTACT ID = 3,12
    | | | | Index Scan: Name = S2USER.XCONNC02 ID = 4
    | | | | | Regular Index (Clustered)
    | | | | | Index Columns:
    | | | | | | 1: CON_CID_CUST_ID (Ascending)
    | | | | #Columns = 3
    | | | | Skip Inserted Rows
    | | | | Skip Deleted Rows
    | | | | #Key Columns = 1
    | | | | | Start Key: Inclusive Value
    | | | | | | | 1: ?
    | | | | | Stop Key: Inclusive Value
    | | | | | | | 1: ?
    | | | | Data Prefetch: None
    | | | | Index Prefetch: None
    | | | | Lock Intents
    | | | | | Table: Intent Share
    | | | | | Row : Next Key Share
    | | | | Sargable Predicate(s)
    | | | | | Process Build Table for Hash Join
    | | Hash Join
    | | | Early Out: Single Match Per Inner Row
    | | | Estimated Build Size: 4391
    | | | Estimated Probe Size: 4407
    | | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
    | | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
    | | | | | Regular Index (Clustered)
    | | | | | Index Columns:
    | | | | | | 1: GB2_TID_TABLE_ID (Ascending)
    | | | | | | 2: GB2_CDE_CODE (Ascending)
    | | | | #Columns = 2
    | | | | Parallel Scan
    | | | | Skip Inserted Rows
    | | | | Skip Deleted Rows
    | | | | #Key Columns = 1
    | | | | | Start Key: Inclusive Value
    | | | | | | | 1: 'CPU'
    | | | | | Stop Key: Inclusive Value
    | | | | | | | 1: 'CPU'
    | | | | Index-Only Access
    | | | | Index Prefetch: None
    | | | | Lock Intents
    | | | | | Table: Intent Share
    | | | | | Row : Next Key Share
    | | | | Sargable Index Predicate(s)
    | | | | | Process Probe Table for Hash Join
    | | Nested Loop Join
    | | | Access Table Name = S2USER.TLS_CUSTOMER ID = 3,3083
    | | | | Index Scan: Name = S2USER.XCUSUN01 ID = 4
    | | | | | Regular Index (Not Clustered)
    | | | | | Index Columns:
    | | | | | | 1: CUS_CID_CUST_ID (Ascending)
    | | | | #Columns = 2
    | | | | Single Record
    | | | | Fully Qualified Unique Key
    | | | | Skip Inserted Rows
    | | | | Skip Deleted Rows
    | | | | #Key Columns = 1
    | | | | | Start Key: Inclusive Value
    | | | | | | | 1: ?
    | | | | | Stop Key: Inclusive Value
    | | | | | | | 1: ?
    | | | | Data Prefetch: None
    | | | | Index Prefetch: None
    | | | | Lock Intents
    | | | | | Table: Intent Share
    | | | | | Row : Next Key Share
    | | Nested Loop Join
    | | | Access Table Name = S2USER.TLS_CUST_CON_PURP ID = 3,19
    | | | | Index Scan: Name = S2USER.XCCPUC01 ID = 1
    | | | | | Regular Index (Clustered)
    | | | | | Index Columns:
    | | | | | | 1: CCP_RID_CONTACT (Ascending)
    | | | | | | 2: CCP_CDE_PURPOSE (Ascending)
    | | | | #Columns = 1
    | | | | Skip Inserted Rows
    | | | | Skip Deleted Rows
    | | | | #Key Columns = 1
    | | | | | Start Key: Inclusive Value
    | | | | | | | 1: ?
    | | | | | Stop Key: Inclusive Value
    | | | | | | | 1: ?
    | | | | Index-Only Access
    | | | | Index Prefetch: None
    | | | | Lock Intents
    | | | | | Table: Intent Share
    | | | | | Row : Next Key Share
    | | Nested Loop Join
    | | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
    | | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
    | | | | | Regular Index (Clustered)
    | | | | | Index Columns:
    | | | | | | 1: GB2_TID_TABLE_ID (Ascending)
    | | | | | | 2: GB2_CDE_CODE (Ascending)
    | | | | #Columns = 1
    | | | | Single Record
    | | | | Fully Qualified Unique Key
    | | | | Skip Inserted Rows
    | | | | Skip Deleted Rows
    | | | | #Key Columns = 2
    | | | | | Start Key: Inclusive Value
    | | | | | | | 1: 'CPU'
    | | | | | | | 2:
    | | | | | Stop Key: Inclusive Value
    | | | | | | | 1: 'CPU'
    | | | | | | | 2:
    | | | | Data Prefetch: None
    | | | | Index Prefetch: None
    | | | | Lock Intents
    | | | | | Table: Intent Share
    | | | | | Row : Next Key Share
    | | | | Sargable Predicate(s)
    | | | | | #Predicates = 1
    | | Insert Into Asynchronous Local Table Queue ID = q1
    | Access Local Table Queue ID = q1 #Columns = 2
    | Distinct Filter #Columns = 1
    UNION
    | Access Table Name = S2USER.TLS_FACILITY ID = 3,1536
    | | Index Scan: Name = S2USER.XFACUN02 ID = 6
    | | | Regular Index (Not Clustered)
    | | | Index Columns:
    | | | | 1: FAC_PID_DEAL (Ascending)
    | | | | 2: FAC_NME_FACILITY (Ascending)
    | | #Columns = 1
    | | Skip Inserted Rows
    | | Skip Deleted Rows
    | | #Key Columns = 1
    | | | Start Key: Inclusive Value
    | | | | | 1: '-Q6V797H'
    | | | Stop Key: Inclusive Value
    | | | | | 1: '-Q6V797H'
    | | Data Prefetch: None
    | | Index Prefetch: None
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | Nested Loop Join
    | | Access Table Name = S2USER.TLS_FAC_COMMIT_TRN ID = 3,1552
    | | | Index Scan: Name = S2USER.XFTRNC02 ID = 3
    | | | | Regular Index (Clustered)
    | | | | Index Columns:
    | | | | | 1: FTR_PID_DEAL (Ascending)
    | | | | | 2: FTR_PID_FACILITY (Ascending)
    | | | #Columns = 1
    | | | Skip Inserted Rows
    | | | Skip Deleted Rows
    | | | Evaluate Index Predicates Before Locking Key
    | | | #Key Columns = 0
    | | | | Start Key: Beginning of Index
    | | | | Stop Key: End of Index
    | | | Data Prefetch: Eligible 15
    | | | Index Prefetch: Eligible 15
    | | | Lock Intents
    | | | | Table: Intent Share
    | | | | Row : Next Key Share
    | | | Sargable Index Predicate(s)
    | | | | #Predicates = 1
    | Nested Loop Join
    | | Data Stream 1:
    | | | Not Piped
    | | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
    | | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
    | | | | | Regular Index (Clustered)
    | | | | | Index Columns:
    | | | | | | 1: GB2_TID_TABLE_ID (Ascending)
    | | | | | | 2: GB2_CDE_CODE (Ascending)
    | | | | #Columns = 2
    | | | | Skip Inserted Rows
    | | | | Skip Deleted Rows
    | | | | #Key Columns = 1
    | | | | | Start Key: Inclusive Value
    | | | | | | | 1: 'CPU'
    | | | | | Stop Key: Inclusive Value
    | | | | | | | 1: 'CPU'
    | | | | Data Prefetch: None
    | | | | Index Prefetch: None
    | | | | Lock Intents
    | | | | | Table: Intent Share
    | | | | | Row : Next Key Share
    | | | | Sargable Predicate(s)
    | | | | | #Predicates = 1
    | | | | | Process Build Table for Hash Join

  4. #4
    Join Date
    Nov 2008
    Posts
    4
    | | | Hash Join
    | | | | Early Out: Single Match Per Outer Row
    | | | | Estimated Build Size: 4001
    | | | | Estimated Probe Size: 4776574
    | | | | Bit Filter Size: 800
    | | | | Access Table Name = S2USER.TLS_CUST_CON_PURP ID = 3,19
    | | | | | Index Scan: Name = S2USER.XCCPUC01 ID = 1
    | | | | | | Regular Index (Clustered)
    | | | | | | Index Columns:
    | | | | | | | 1: CCP_RID_CONTACT (Ascending)
    | | | | | | | 2: CCP_CDE_PURPOSE (Ascending)
    | | | | | #Columns = 2
    | | | | | Skip Inserted Rows
    | | | | | Skip Deleted Rows
    | | | | | #Key Columns = 0
    | | | | | | Start Key: Beginning of Index
    | | | | | | Stop Key: End of Index
    | | | | | Index-Only Access
    | | | | | Index Prefetch: Eligible 1206
    | | | | | Lock Intents
    | | | | | | Table: Intent Share
    | | | | | | Row : Next Key Share
    | | | | | Sargable Index Predicate(s)
    | | | | | | Process Probe Table for Hash Join
    | | | Nested Loop Join
    | | | | Access Table Name = S2USER.TLS_CUST_CONTACT ID = 3,12
    | | | | | Index Scan: Name = S2USER.XCONUN01 ID = 3
    | | | | | | Regular Index (Not Clustered)
    | | | | | | Index Columns:
    | | | | | | | 1: CON_RID_CONTACT (Ascending)
    | | | | | #Columns = 2
    | | | | | Single Record
    | | | | | Fully Qualified Unique Key
    | | | | | Skip Inserted Rows
    | | | | | Skip Deleted Rows
    | | | | | #Key Columns = 1
    | | | | | | Start Key: Inclusive Value
    | | | | | | | | 1: ?
    | | | | | | Stop Key: Inclusive Value
    | | | | | | | | 1: ?
    | | | | | Data Prefetch: None
    | | | | | Index Prefetch: None
    | | | | | Lock Intents
    | | | | | | Table: Intent Share
    | | | | | | Row : Next Key Share
    | | | Nested Loop Join
    | | | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
    | | | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
    | | | | | | Regular Index (Clustered)
    | | | | | | Index Columns:
    | | | | | | | 1: GB2_TID_TABLE_ID (Ascending)
    | | | | | | | 2: GB2_CDE_CODE (Ascending)
    | | | | | #Columns = 1
    | | | | | Single Record
    | | | | | Fully Qualified Unique Key
    | | | | | Skip Inserted Rows
    | | | | | Skip Deleted Rows
    | | | | | #Key Columns = 2
    | | | | | | Start Key: Inclusive Value
    | | | | | | | | 1: 'CPU'
    | | | | | | | | 2:
    | | | | | | Stop Key: Inclusive Value
    | | | | | | | | 1: 'CPU'
    | | | | | | | | 2:
    | | | | | Index-Only Access
    | | | | | Index Prefetch: None
    | | | | | Lock Intents
    | | | | | | Table: Intent Share
    | | | | | | Row : Next Key Share
    | | | Nested Loop Join
    | | | | Access Table Name = S2USER.TLS_CUSTOMER ID = 3,3083
    | | | | | Index Scan: Name = S2USER.XCUSUN01 ID = 4
    | | | | | | Regular Index (Not Clustered)
    | | | | | | Index Columns:
    | | | | | | | 1: CUS_CID_CUST_ID (Ascending)
    | | | | | #Columns = 2
    | | | | | Single Record
    | | | | | Fully Qualified Unique Key
    | | | | | Skip Inserted Rows
    | | | | | Skip Deleted Rows
    | | | | | #Key Columns = 1
    | | | | | | Start Key: Inclusive Value
    | | | | | | | | 1: ?
    | | | | | | Stop Key: Inclusive Value
    | | | | | | | | 1: ?
    | | | | | Data Prefetch: None
    | | | | | Index Prefetch: None
    | | | | | Lock Intents
    | | | | | | Table: Intent Share
    | | | | | | Row : Next Key Share
    | | | Insert Into Temp Table ID = t1
    | | | | #Columns = 3
    | | End of Data Stream 1
    | | Access Temp Table ID = t1
    | | | #Columns = 3
    | | | Relation Scan
    | | | | Prefetch: Eligible
    | | | Sargable Predicate(s)
    | | | | Insert Into Sorted Temp Table ID = t2
    | | | | | #Columns = 4
    | | | | | #Sort Key Columns = 1
    | | | | | | Key 1: (Ascending)
    | | | | | Sortheap Allocation Parameters:
    | | | | | | #Rows = 1
    | | | | | | Row Width = 60
    | | | | | Piped
    | Sorted Temp Table Completion ID = t2
    | Access Temp Table ID = t2
    | | #Columns = 4
    | | Relation Scan
    | | | Prefetch: Eligible
    | Nested Loop Join
    | | Access Table Name = S2USER.TLS_FAC_TR_SG_SHR ID = 3,1559
    | | | Index Scan: Name = S2USER.XFTSNC02 ID = 4
    | | | | Regular Index (Clustered)
    | | | | Index Columns:
    | | | | | 1: FTS_RID_FAC_TRAN (Ascending)
    | | | #Columns = 1
    | | | Single Record
    | | | Skip Inserted Rows
    | | | Skip Deleted Rows
    | | | #Key Columns = 1
    | | | | Start Key: Inclusive Value
    | | | | | | 1: ?
    | | | | Stop Key: Inclusive Value
    | | | | | | 1: ?
    | | | Data Prefetch: None
    | | | Index Prefetch: None
    | | | Lock Intents
    | | | | Table: Intent Share
    | | | | Row : Next Key Share
    | | | Sargable Predicate(s)
    | | | | #Predicates = 1
    | Distinct Filter #Columns = 1
    UNION
    | Access Table Name = S2USER.TLS_DEAL_BORROWER ID = 3,536
    | | Index Scan: Name = S2USER.XDBRNC02 ID = 2
    | | | Regular Index (Clustered)
    | | | Index Columns:
    | | | | 1: DBR_PID_DEAL (Ascending)
    | | | | 2: DBR_RID_LOCATION (Ascending)
    | | #Columns = 1
    | | Skip Inserted Rows
    | | Skip Deleted Rows
    | | #Key Columns = 1
    | | | Start Key: Inclusive Value
    | | | | | 1: '-Q6V797H'
    | | | Stop Key: Inclusive Value
    | | | | | 1: '-Q6V797H'
    | | Data Prefetch: None
    | | Index Prefetch: None
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | Nested Loop Join
    | | Access Table Name = S2USER.TLS_CUST_CONTACT ID = 3,12
    | | | Index Scan: Name = S2USER.XCONNC02 ID = 4
    | | | | Regular Index (Clustered)
    | | | | Index Columns:
    | | | | | 1: CON_CID_CUST_ID (Ascending)
    | | | #Columns = 3
    | | | Skip Inserted Rows
    | | | Skip Deleted Rows
    | | | #Key Columns = 1
    | | | | Start Key: Inclusive Value
    | | | | | | 1: ?
    | | | | Stop Key: Inclusive Value
    | | | | | | 1: ?
    | | | Data Prefetch: None
    | | | Index Prefetch: None
    | | | Lock Intents
    | | | | Table: Intent Share
    | | | | Row : Next Key Share
    | | | Sargable Predicate(s)
    | | | | Process Build Table for Hash Join
    | Hash Join
    | | Early Out: Single Match Per Inner Row
    | | Estimated Build Size: 4533
    | | Estimated Probe Size: 4407
    | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
    | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
    | | | | Regular Index (Clustered)
    | | | | Index Columns:
    | | | | | 1: GB2_TID_TABLE_ID (Ascending)
    | | | | | 2: GB2_CDE_CODE (Ascending)
    | | | #Columns = 2
    | | | Skip Inserted Rows
    | | | Skip Deleted Rows
    | | | #Key Columns = 1
    | | | | Start Key: Inclusive Value
    | | | | | | 1: 'CPU'
    | | | | Stop Key: Inclusive Value
    | | | | | | 1: 'CPU'
    | | | Index-Only Access
    | | | Index Prefetch: None
    | | | Lock Intents
    | | | | Table: Intent Share
    | | | | Row : Next Key Share
    | | | Sargable Index Predicate(s)
    | | | | Process Probe Table for Hash Join
    | Nested Loop Join
    | | Access Table Name = S2USER.TLS_CUST_CON_PURP ID = 3,19
    | | | Index Scan: Name = S2USER.XCCPUC01 ID = 1
    | | | | Regular Index (Clustered)
    | | | | Index Columns:
    | | | | | 1: CCP_RID_CONTACT (Ascending)
    | | | | | 2: CCP_CDE_PURPOSE (Ascending)
    | | | #Columns = 1
    | | | Skip Inserted Rows
    | | | Skip Deleted Rows
    | | | #Key Columns = 1
    | | | | Start Key: Inclusive Value
    | | | | | | 1: ?
    | | | | Stop Key: Inclusive Value
    | | | | | | 1: ?
    | | | Index-Only Access
    | | | Index Prefetch: None
    | | | Lock Intents
    | | | | Table: Intent Share
    | | | | Row : Next Key Share
    | Nested Loop Join
    | | Access Table Name = S2USER.TLS_CUSTOMER ID = 3,3083
    | | | Index Scan: Name = S2USER.XCUSUN01 ID = 4
    | | | | Regular Index (Not Clustered)
    | | | | Index Columns:
    | | | | | 1: CUS_CID_CUST_ID (Ascending)
    | | | #Columns = 2
    | | | Single Record
    | | | Fully Qualified Unique Key
    | | | Skip Inserted Rows
    | | | Skip Deleted Rows
    | | | #Key Columns = 1
    | | | | Start Key: Inclusive Value
    | | | | | | 1: ?
    | | | | Stop Key: Inclusive Value
    | | | | | | 1: ?
    | | | Data Prefetch: None
    | | | Index Prefetch: None
    | | | Lock Intents
    | | | | Table: Intent Share
    | | | | Row : Next Key Share
    | Nested Loop Join
    | | Access Table Name = S2USER.TLS_FAM_GLOBAL2 ID = 3,1808
    | | | Index Scan: Name = S2USER.XGB2UC01 ID = 1
    | | | | Regular Index (Clustered)
    | | | | Index Columns:
    | | | | | 1: GB2_TID_TABLE_ID (Ascending)
    | | | | | 2: GB2_CDE_CODE (Ascending)
    | | | #Columns = 1
    | | | Single Record
    | | | Fully Qualified Unique Key
    | | | Skip Inserted Rows
    | | | Skip Deleted Rows
    | | | #Key Columns = 2
    | | | | Start Key: Inclusive Value
    | | | | | | 1: 'CPU'
    | | | | | | 2:
    | | | | Stop Key: Inclusive Value
    | | | | | | 1: 'CPU'
    | | | | | | 2:
    | | | Data Prefetch: None
    | | | Index Prefetch: None
    | | | Lock Intents
    | | | | Table: Intent Share
    | | | | Row : Next Key Share
    | | | Sargable Predicate(s)
    | | | | #Predicates = 1
    | Insert Into Sorted Temp Table ID = t3
    | | #Columns = 4
    | | #Sort Key Columns = 2
    | | | Key 1: (Ascending)
    | | | Key 2: CUS_CID_CUST_ID (Ascending)
    | | Sortheap Allocation Parameters:
    | | | #Rows = 1
    | | | Row Width = 56
    | | Piped
    | | Duplicate Elimination
    | Access Temp Table ID = t3
    | | #Columns = 4
    | | Relation Scan
    | | | Prefetch: Eligible
    )
    Return Data to Application
    | #Columns = 3

    End

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Is your hardware appropriate for intra-partition parallelism? Try setting it to 1. Also, remove DISTINCT and use UNION instead of UNION ALL; that will require one sort instead of three. Is the cardinality estimate correct (that is, do you expect the query to return very few rows)? If not, statistics may not be up to date.

    It might be also a good idea to indicate your platform and the DB2 version.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you might, also, want to think about your DB design. wouldn't a pid table that contaied your pid nbr and the type of pid make things a lot simpler?

Posting Permissions

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