All Forums Database
mihirdhakan93 14 posts Joined 04/15
20 Oct 2015
Error: Result Exceeded Maximum Length while selecting Table

Hi All,
 
I am selecting a table and while running below query, i am getting this Error: Select Failed 9134 Result Exceeded Maximum Length.
 

SELect oreplace(a.requesttext,'dev7','$env'), oreplace(b.requesttext,'dev15','$env')
    FROM dbc.tablesv as  a, dbc.tablesv as b
    
    WHERE 
   a.tablename = b.tablename and 
    a.databasename ='dev7_stg' and  b.databasename='dev15_stg'
      --AND  a.tablename ='STG_1005_3048_accounts' 
        AND a.tablekind='T' AND b.tablekind='T'
        AND
        oreplace(a.requesttext,'dev7','$env') = oreplace(b.requesttext,'dev15','$env')

Explain Plan:
 

explain SELect oreplace(a.requesttext,'dev7','$env'), oreplace(b.requesttext,'dev15','$env')
    FROM dbc.tablesv as  a, dbc.tablesv as b
    
    WHERE 
   a.tablename = b.tablename and 
    a.databasename ='dev7_stg' and  b.databasename='dev15_stg'
      --AND  a.tablename ='STG_1005_3048_accounts' 
		AND a.tablekind='T' AND b.tablekind='T'
		AND
        oreplace(a.requesttext,'dev7','$env') = oreplace(b.requesttext,'dev15','$env');

  1) First, we lock DBC.tvm in view tablesv for access, we lock
     DBC.dbase in view tablesv for access, and we lock DBC.OU in view
     tablesv for access.
  2) Next, we do an all-AMPs RETRIEVE step from DBC.dbase in view
     tablesv by way of an all-rows scan with a condition of (
     "DBC.dbase in view tablesv.DatabaseName = 'dev7_stg'") into Spool
     2 (all_amps), which is duplicated on all AMPs.  The size of Spool
     2 is estimated with no confidence to be 5,152 rows (535,808 bytes).
     The estimated time for this step is 0.01 seconds.
  3) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
     all-rows scan, which is joined to DBC.tvm in view tablesv by way
     of an all-rows scan with a condition of ("((DBC.tvm in view
     tablesv.TableKind (CHAR(1), CHARACTER SET LATIN, NOT
     CASESPECIFIC))= 'T') AND (((DBC.tvm in view tablesv.TVMId >
     '00C001000000'XB) AND (DBC.tvm in view tablesv.TVMId <
     '00C002000000'XB)) OR ((DBC.tvm in view tablesv.TVMId <
     '00C001000000'XB) OR (((DBC.tvm in view tablesv.TVMId >
     '00C002000000'XB) AND (DBC.tvm in view tablesv.TVMId <
     '00C009000000'XB)) OR (((DBC.tvm in view tablesv.TVMId >
     '00C009000000'XB) AND (DBC.tvm in view tablesv.TVMId <
     '00C010000000'XB)) OR(((DBC.tvm in view tablesv.TVMId >
     '00C010000000'XB) AND (DBC.tvm in view tablesv.TVMId <
     '00C017000000'XB)) OR (DBC.tvm in view tablesv.TVMId >
     '00C017000000'XB))))))").  Spool 2 and DBC.tvm are joined using a
     dynamic hash join, with a join condition of ("DBC.tvm.DatabaseId =
     DatabaseId").  The result goes into Spool 3 (all_amps), which is
     built locally on the AMPs.  Then we do a SORT to order Spool 3 by
     row hash.  The size of Spool 3 is estimated with no confidence to
     be 4,772 rows (40,729,020 bytes).  The estimated time for this
     step is 0.41 seconds.
  4) We do an all-AMPs RETRIEVE step from DBC.OU in view tablesv by way
     of an all-rows scan with a condition of ("(DBC.OU in view
     tablesv.FieldId IS NULL) AND ((DBC.OU in view tablesv.IndexNumber
     IS NULL) AND ((((DBC.OU in view tablesv.ObjectId >
     '00C001000000'XB) AND (DBC.OU in view tablesv.ObjectId <
     '00C002000000'XB)) OR ((DBC.OU in view tablesv.ObjectId <
     '00C001000000'XB) OR (((DBC.OU in view tablesv.ObjectId >
     '00C002000000'XB) AND (DBC.OU in view tablesv.ObjectId <
     '00C009000000'XB)) OR (((DBC.OU in view tablesv.ObjectId >
     '00C009000000'XB) AND (DBC.OU in view tablesv.ObjectId <
     '00C010000000'XB)) OR (((DBC.OU in view tablesv.ObjectId >
     '00C010000000'XB) AND (DBC.OU in view tablesv.ObjectId <
     '00C017000000'XB)) OR (DBC.OU in view tablesv.ObjectId >
     '00C017000000'XB)))))) AND (NOT (DBC.OU in view tablesv.DatabaseId
     IS NULL ))))") into Spool 4 (all_amps), which is duplicated on all
     AMPs.  Then we do a SORT to order Spool 4 by row hash.  The size
     of Spool 4 is estimated with no confidence to be 616,469 rows (
     14,178,787 bytes).  The estimated time for this step is 0.16
     seconds.
  5) We execute the following steps in parallel.
       1) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of
          a RowHash match scan, which is joined to Spool 4 by way of a
          RowHash match scan.  Spool 3 and Spool 4 are left outer
          joined using a merge join, with a join condition of (
          "(ObjectId = TVMId) AND (DatabaseId = DatabaseId)").  The
          result goes into Spool 1 (used to materialize view, derived
          table, table function or table operator a) (all_amps), which
          is built locally on the AMPs.  The size of Spool 1 is
          estimated with no confidence to be 4,772 rows (40,709,932
          bytes).  The estimated time for this step is 0.17 seconds.
       2) We do an all-AMPs RETRIEVE step from DBC.dbase in view
          tablesv by way of an all-rows scan with a condition of (
          "DBC.dbase in view tablesv.DatabaseName = 'dev15_stg'") into
          Spool 6 (all_amps), which is duplicated on all AMPs.  The
          size of Spool 6 is estimated with no confidence to be 5,152
          rows (87,584 bytes).  The estimated time for this step is
          0.01 seconds.
  6) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
     all-rows scan, which is joined to DBC.tvm in view tablesv by way
     of an all-rows scan with a condition of ("((DBC.tvm in view
     tablesv.TableKind (CHAR(1), CHARACTER SET LATIN, NOT
     CASESPECIFIC))= 'T') AND (((DBC.tvm in view tablesv.TVMId >
     '00C001000000'XB) AND (DBC.tvm in view tablesv.TVMId <
     '00C002000000'XB)) OR ((DBC.tvm in view tablesv.TVMId <
     '00C001000000'XB) OR (((DBC.tvm in view tablesv.TVMId >
     '00C002000000'XB) AND (DBC.tvm in view tablesv.TVMId <
     '00C009000000'XB)) OR (((DBC.tvm in view tablesv.TVMId >
     '00C009000000'XB) AND (DBC.tvm in view tablesv.TVMId <
     '00C010000000'XB)) OR(((DBC.tvm in view tablesv.TVMId >
     '00C010000000'XB) AND (DBC.tvm in view tablesv.TVMId <
     '00C017000000'XB)) OR (DBC.tvm in view tablesv.TVMId >
     '00C017000000'XB))))))").  Spool 6 and DBC.tvm are joined using a
     dynamic hash join, with a join condition of ("DBC.tvm.DatabaseId =
     DatabaseId").  The result goes into Spool 7 (all_amps), which is
     built locally on the AMPs.  Then we do a SORT to order Spool 7 by
     row hash.  The size of Spool 7 is estimated with no confidence to
     be 4,772 rows (40,299,540 bytes).  The estimated time for this
     step is 0.41 seconds.
  7) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by
          way of an all-rows scan with a condition of ("(a.TABLEKIND =
          'T') AND (a.DATABASENAME = 'dev7_stg')") into Spool 9
          (all_amps) fanned out into 4 hash join partitions, which is
          redistributed by hash code to all AMPs.  The size of Spool 9
          is estimated with no confidence to be 4,772 rows (40,256,592
          bytes).  The estimated time for this step is 0.44 seconds.
       2) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of
          a RowHash match scan, which is joined to Spool 4 (Last Use)
          by way of a RowHash match scan.  Spool 7 and Spool 4 are
          left outer joined using a merge join, with a join condition
          of ("(ObjectId = TVMId) AND (DatabaseId = DatabaseId)").  The
          result goes into Spool 10 (all_amps) fanned out into 4 hash
          join partitions, which is redistributed by hash code to all
          AMPs.  The size of Spool 10 is estimated with no confidence
          to be 4,772 rows (40,256,592 bytes).  The estimated time for
          this step is 0.45 seconds.
  8) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an
     all-rows scan, which is joined to Spool 10 (Last Use) by way of an
     all-rows scan.  Spool 9 and Spool 10 are joined using a hash join
     of 4 partitions, with a join condition of (
     "((TD_SYSFNLIB.oreplace ({LeftTable}.REQUESTTEXT, 'dev7',
     '$env'))= (TD_SYSFNLIB.oreplace ({RightTable}.RequestText,
     'dev15', '$env'))) AND (TABLENAME = TVMName)").  The result goes
     into Spool 5 (group_amps), which is built locally on the AMPs.
     The size of Spool 5 is estimated with no confidence to be 12,107
     rows (64,857,199 bytes).  The estimated time for this step is 0.63
     seconds.
  9) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 5 are sent back to the user as the result of
     statement 1.  The total estimated time is 2.25 seconds.

Can anyone help me out ?

You must sign in to leave a comment.