Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Bug, tuning issue or bad sql?

Re: Bug, tuning issue or bad sql?

From: Anjo Kolk <anjo_at_oraperf.com>
Date: Wed, 27 Feb 2002 09:35:38 -0800
Message-ID: <F001.00419FBC.20020227093538@fatcity.com>


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,'temp_at_here.com') 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,'temp_at_here.com') 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,'temp_at_here.com') 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,'temp_at_here.com') 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,'temp_at_here.com') 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,'temp_at_here.com') 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,'temp_at_here.com') 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: mrclark_at_xnet.com
>
> 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: ListGuru_at_fatcity.com (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: anjo_at_oraperf.com

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: ListGuru_at_fatcity.com (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).
Received on Wed Feb 27 2002 - 11:35:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US