Error: Result Exceeded Maximum Length while selecting Table

Database
Enthusiast

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 ?