Re: ORA-3113 During delete from with subquery
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: sstefick@xxxxxxxxx
- Date: Mon, 25 Apr 2005 19:01:53 +0200
Scott Stefick wrote:
>Anyone else experience this?
>OS: Solaris 8
>DB: 10g (10.1.0.3)
>My delete returns an ORA-3113 when I execute this on my 10g databases,
>but it works fine on my 8.1.7 databases.
>
>Here is my DML:
>
>DELETE FROM v_trn_event_attendees=20
>WHERE pers_id =3D 86409
>AND class_id IN (SELECT a.class_id=20
> FROM trn_events e, trn_event_attendees a =20
> WHERE e.class_id =3D a.class_id=20
> AND pers_id =3D 86409
> AND element_id =3D 1015891
> AND date_conducted =3D (select max(date_conducted)=
>=20
> from
>trn_events e, trn_event_attendees a
> where
>e.class_id =3D a.class_id
> and pers_id =3D=
>86409
> and
>element_id =3D 1015891
> and
>date_conducted <=3D '25-APR-2005'))
>/
>
>
>Thanks in advance..
>Scott
>--
>
>
Scott,
A 3113 error is usually associated with a .trc file in the
user_dump_dest directory. It may give a clue, but it smells like a bug.
Rewriting queries also sometimes magically works around bugs.
Try this (I hope the values are hard-coded for testing purposes only.
Also, avoid relying on implicit date formats)
DELETE FROM v_trn_event_attendees
WHERE (pers_id, class_id) IN
(SELECT pers_id, a.class_id
FROM trn_events e,
trn_event_attendees a
WHERE e.class_id = a.class_id
AND (pers_id, element_id, date_conducted) =
(select pers_id, element_id, max(date_conducted)
from trn_events e,
trn_event_attendees a
where e.class_id = a.class_id
and pers_id = 86409
and element_id = 1015891
and date_conducted <= to_date('25-APR-2005', 'DD-MON-YYYY')
group by pers_id, element_id))
/
If you have the composite indexes I think you have, it may work well.
You can probably do even better with analytic functions.
HTH,
SF
--
//www.freelists.org/webpage/oracle-l
Other related posts:
