Re: ORA-3113 During delete from with subquery

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

--
http://www.freelists.org/webpage/oracle-l