Oracle FAQ

Your Portal to the Oracle Knowledge Grid

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

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

Bug, tuning issue or bad sql?

From: Ed <mrclark_at_xnet.com>
Date: Wed, 27 Feb 2002 07:23:34 -0800
Message-ID: <F001.00419C2A.20020227072334@fatcity.com>

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:

Query runs with no ORDER BY.
Query runs with ORDER BY, when limiting rows returned.
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).

Received on Wed Feb 27 2002 - 09:23:34 CST

This message: [ Message body ]
Next message: Farnsworth, Dave: "RE: Manager decrees "his" data warehouse design. Help!"
Previous message: Orr, Steve: "RE: Manager decrees "his" data warehouse design. Help!"
Next in thread: Jesse, Rich: "RE: Bug, tuning issue or bad sql?"
Maybe reply: Jesse, Rich: "RE: Bug, tuning issue or bad sql?"
Maybe reply: dmeng_at_focal.com: "Re: Bug, tuning issue or bad sql?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message

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