| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Bug, tuning issue or bad sql?
Ed -
Sounds like you hit bug 1331849. Check Metalink for more info.
Dennis
"Ed"
<mrclark_at_xnet To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
.com> cc:
Sent by: Subject: Bug, tuning issue or bad sql?
root_at_fatcity.
com
02/27/02
09:23 AM
Please
respond to
ORACLE-L
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
*
In other words:
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
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
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
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
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
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
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)
-- 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: INET: dmeng_at_focal.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:54:16 CST
 |
 |