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)=
>trn_events e, trn_event_attendees a
>e.class_id =3D a.class_id
> and pers_id =3D=
>element_id =3D 1015891
>date_conducted <=3D '25-APR-2005'))
>Thanks in advance..
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,
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,
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.