Oracle 8.1.6.0 NT 4.0
I'm having a problem with a query which is basically just a bunch of UNION
ALL's that I want sorted in a certain way. The query runs flawlessly when I
limit the result set with a where clause, but when I remove it, the query
crashes when sorting (in the order by at the far bottom). If I remove the
ORDER BY, the query runs beautifully. The max rows returned by this is
about 12,000. I am confident that the values returned are consistant across
each of the UNION's.
Here's the error returned:
trans_demographic td
*
ERROR at line 257:
ORA-00600: internal error code, arguments: [5213], [], [], [], [], [], [],
[]
In other words:
1. Query runs with no ORDER BY.
2. Query runs with ORDER BY, when limiting rows returned.
3. When limiting rows returned, I can use any value in the full range (in
this case poe_assoc_id), and everything works as long as the total rows
returned are less than around 3,000.
I have tested every range of data, and I can use any set of valid values as
long as I do it in pieces.
So my question is: is the sql bad somehow (I don't think so). Is this a
tuning issue on the database itself? Is this a bug in 8.1.6.0?
Thanks in advance!
Ed
P.S. Here's the big ole query:
select ae_email_addr, ae_assoc_id, poe_assoc_id, table_type, poe, adds,
changes, drops, ae_name from
(select a.assoc_id poe_assoc_id,
a.poe poe,
'Member' table_type,
SUM(DECODE(tm.record_change_type,'A',1,0)) adds,
SUM(DECODE(tm.record_change_type,'C',1,0)) +
SUM(DECODE(tm.record_change_type,'T',1,0)) changes,
SUM(DECODE(tm.record_change_type,'D',1,0)) drops,
a.ae_email_addr ae_email_addr,
a.ae_name ae_name,
a.ae_assoc_id ae_assoc_id
from (select a.assoc_id,
poe_am.mem_id poe,
ae_m.first_name||' '||ae_m.last_name ae_name,
ae_am.assoc_id ae_assoc_id,
NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr
from ASSOCIATION a,
ASSOCIATION_MEMBER poe_am,
MEMBER poe_m,
ASSOCIATION_MEMBER ae_am,
MEMBER ae_m
where a.assoc_type = 'L'
and a.assoc_status_cd = 'A'
and a.assoc_id = poe_am.assoc_id
and poe_am.relation_type = 'POE'
and poe_am.mem_id = poe_m.mem_id
and poe_m.primary_assoc_id = ae_am.assoc_id
and ae_am.relation_type = 'EO_ID'
and ae_am.mem_id = ae_m.mem_id) a,
TRANS_MEMBER tm
where a.assoc_id = tm.primary_assoc_id(+)
and a.poe = tm.sender_id(+)
and tm.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001'
GROUP BY a.assoc_id,
a.poe,
a.ae_email_addr,
a.ae_name,
a.ae_assoc_id
UNION ALL
SELECT a.assoc_id poe_assoc_id,
a.poe poe,
'Member Supplemental' table_type,
SUM(DECODE(tms.record_change_type,'A',1,0)) adds,
SUM(DECODE(tms.record_change_type,'C',1,0)) changes,
SUM(DECODE(tms.record_change_type,'D',1,0)) drops,
a.ae_email_addr ae_email_addr,
a.ae_name ae_name,
a.ae_assoc_id ae_assoc_id
FROM (select a.assoc_id,
poe_am.mem_id poe,
ae_m.first_name||' '||ae_m.last_name ae_name,
ae_am.assoc_id ae_assoc_id,
NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr
from ASSOCIATION a,
ASSOCIATION_MEMBER poe_am,
MEMBER poe_m,
ASSOCIATION_MEMBER ae_am,
MEMBER ae_m
where a.assoc_type = 'L'
and a.assoc_status_cd = 'A'
and a.assoc_id = poe_am.assoc_id
and poe_am.relation_type = 'POE'
and poe_am.mem_id = poe_m.mem_id
and poe_m.primary_assoc_id = ae_am.assoc_id
and ae_am.relation_type = 'EO_ID'
and ae_am.mem_id = ae_m.mem_id) a,
trans_member_supplemental tms
WHERE a.assoc_id = tms.assoc_id(+)
AND a.poe = tms.sender_id(+)
and tms.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001'
GROUP BY a.assoc_id,
a.poe,
a.ae_email_addr,
a.ae_name,
a.ae_assoc_id
UNION ALL
SELECT a.assoc_id poe_assoc_id,
a.poe poe,
'Office' table_type,
SUM(DECODE(tof.record_change_type,'A',1,0)) adds,
SUM(DECODE(tof.record_change_type,'C',1,0)) +
SUM(DECODE(tof.record_change_type,'T',1,0)) changes,
SUM(DECODE(tof.record_change_type,'D',1,0)) drops,
a.ae_email_addr ae_email_addr,
a.ae_name ae_name,
a.ae_assoc_id ae_assoc_id
FROM (select a.assoc_id,
poe_am.mem_id poe,
ae_m.first_name||' '||ae_m.last_name ae_name,
ae_am.assoc_id ae_assoc_id,
NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr
from ASSOCIATION a,
ASSOCIATION_MEMBER poe_am,
MEMBER poe_m,
ASSOCIATION_MEMBER ae_am,
MEMBER ae_m
where a.assoc_type = 'L'
and a.assoc_status_cd = 'A'
and a.assoc_id = poe_am.assoc_id
and poe_am.relation_type = 'POE'
and poe_am.mem_id = poe_m.mem_id
and poe_m.primary_assoc_id = ae_am.assoc_id
and ae_am.relation_type = 'EO_ID'
and ae_am.mem_id = ae_m.mem_id) a,
TRANS_OFFICE tof
WHERE a.assoc_id = tof.primary_assoc_id(+)
AND a.poe = tof.sender_id(+)
and tof.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001'
GROUP BY a.assoc_id,
a.poe,
a.ae_email_addr,
a.ae_name,
a.ae_assoc_id
UNION ALL
SELECT a.assoc_id poe_assoc_id,
a.poe poe,
'Office Supplemental' table_type,
SUM(DECODE(tos.record_change_type,'A',1,0)) adds,
SUM(DECODE(tos.record_change_type,'C',1,0)) +
SUM(DECODE(tos.record_change_type,'T',1,0)) changes,
SUM(DECODE(tos.record_change_type,'D',1,0)) drops,
a.ae_email_addr ae_email_addr,
a.ae_name ae_name,
a.ae_assoc_id ae_assoc_id
FROM (select a.assoc_id,
poe_am.mem_id poe,
ae_m.first_name||' '||ae_m.last_name ae_name,
ae_am.assoc_id ae_assoc_id,
NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr
from ASSOCIATION a,
ASSOCIATION_MEMBER poe_am,
MEMBER poe_m,
ASSOCIATION_MEMBER ae_am,
MEMBER ae_m
where a.assoc_type = 'L'
and a.assoc_status_cd = 'A'
and a.assoc_id = poe_am.assoc_id
and poe_am.relation_type = 'POE'
and poe_am.mem_id = poe_m.mem_id
and poe_m.primary_assoc_id = ae_am.assoc_id
and ae_am.relation_type = 'EO_ID'
and ae_am.mem_id = ae_m.mem_id) a,
TRANS_OFFICE tos
WHERE a.assoc_id = tos.primary_assoc_id(+)
AND a.poe = tos.sender_id(+)
and tos.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001'
GROUP BY a.assoc_id,
a.poe,
a.ae_email_addr,
a.ae_name,
a.ae_assoc_id
UNION ALL
SELECT a.assoc_id poe_assoc_id,
a.poe poe,
'Financial' table_type,
SUM(DECODE(tf.record_change_type,'A',1,0)) adds,
SUM(DECODE(tf.record_change_type,'C',1,0)) +
SUM(DECODE(tf.record_change_type,'T',1,0)) changes,
SUM(DECODE(tf.record_change_type,'D',1,0)) drops,
a.ae_email_addr ae_email_addr,
a.ae_name ae_name,
a.ae_assoc_id ae_assoc_id
FROM (select a.assoc_id,
poe_am.mem_id poe,
ae_m.first_name||' '||ae_m.last_name ae_name,
ae_am.assoc_id ae_assoc_id,
NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr
from ASSOCIATION a,
ASSOCIATION_MEMBER poe_am,
MEMBER poe_m,
ASSOCIATION_MEMBER ae_am,
MEMBER ae_m
where a.assoc_type = 'L'
and a.assoc_status_cd = 'A'
and a.assoc_id = poe_am.assoc_id
and poe_am.relation_type = 'POE'
and poe_am.mem_id = poe_m.mem_id
and poe_m.primary_assoc_id = ae_am.assoc_id
and ae_am.relation_type = 'EO_ID'
and ae_am.mem_id = ae_m.mem_id) a,
trans_financial tf
WHERE a.assoc_id = tf.assoc_id(+)
AND a.poe = tf.sender_id(+)
and tf.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001'
GROUP BY a.assoc_id,
a.poe,
a.ae_email_addr,
a.ae_name,
a.ae_assoc_id
UNION ALL
SELECT a.assoc_id poe_assoc_id,
a.poe poe,
'Education' table_type,
SUM(DECODE(te.record_change_type,'A',1,0)) adds,
SUM(DECODE(te.record_change_type,'C',1,0)) +
SUM(DECODE(te.record_change_type,'T',1,0)) changes,
SUM(DECODE(te.record_change_type,'D',1,0)) drops,
a.ae_email_addr ae_email_addr,
a.ae_name ae_name,
a.ae_assoc_id ae_assoc_id
FROM (select a.assoc_id,
poe_am.mem_id poe,
ae_m.first_name||' '||ae_m.last_name ae_name,
ae_am.assoc_id ae_assoc_id,
NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr
from ASSOCIATION a,
ASSOCIATION_MEMBER poe_am,
MEMBER poe_m,
ASSOCIATION_MEMBER ae_am,
MEMBER ae_m
where a.assoc_type = 'L'
and a.assoc_status_cd = 'A'
and a.assoc_id = poe_am.assoc_id
and poe_am.relation_type = 'POE'
and poe_am.mem_id = poe_m.mem_id
and poe_m.primary_assoc_id = ae_am.assoc_id
and ae_am.relation_type = 'EO_ID'
and ae_am.mem_id = ae_m.mem_id) a,
trans_education te
WHERE a.assoc_id = te.assoc_id(+)
AND a.poe = te.sender_id(+)
and te.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001'
GROUP BY a.assoc_id,
a.poe,
a.ae_email_addr,
a.ae_name,
a.ae_assoc_id
UNION ALL
SELECT a.assoc_id poe_assoc_id,
a.poe poe,
'Demographic' table_type,
SUM(DECODE(td.record_change_type,'A',1,0)) adds,
SUM(DECODE(td.record_change_type,'C',1,0)) +
SUM(DECODE(td.record_change_type,'T',1,0)) changes,
SUM(DECODE(td.record_change_type,'D',1,0)) drops,
a.ae_email_addr ae_email_addr,
a.ae_name ae_name,
a.ae_assoc_id ae_assoc_id
FROM (select a.assoc_id,
poe_am.mem_id poe,
ae_m.first_name||' '||ae_m.last_name ae_name,
ae_am.assoc_id ae_assoc_id,
NVL(ae_m.email_addr,'[EMAIL PROTECTED]') ae_email_addr
from ASSOCIATION a,
ASSOCIATION_MEMBER poe_am,
MEMBER poe_m,
ASSOCIATION_MEMBER ae_am,
MEMBER ae_m
where a.assoc_type = 'L'
and a.assoc_status_cd = 'A'
and a.assoc_id = poe_am.assoc_id
and poe_am.relation_type = 'POE'
and poe_am.mem_id = poe_m.mem_id
and poe_m.primary_assoc_id = ae_am.assoc_id
and ae_am.relation_type = 'EO_ID'
and ae_am.mem_id = ae_m.mem_id) a,
trans_demographic td
WHERE a.assoc_id = td.assoc_id(+)
AND a.poe = td.sender_id(+)
and td.last_changed_dt(+) BETWEEN '01-JUN-2001' AND '30-JUN-2001'
GROUP BY a.assoc_id,
a.poe,
a.ae_email_addr,
a.ae_name,
a.ae_assoc_id)
where poe_assoc_id between 1000 and 5000
order by ae_email_addr, poe_assoc_id
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ed
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
