Bug. Ed wrote:
> 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk 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).
