Hi List, I have the below query whose explain plan is showing that it is doing full table scan on Historie table:
select F1.AMTLICHESKENNZEICHEN
AMTLICHESKENNZEICHEN,F1.OID,F1.VERKAUFSBEZEICHNUNG,B1.FAHRZEUGARTTEXT,B1.FAH
RZEUGHERSTELLERTEXT,B1.FAHRZEUGTYPTEXT,B1.FIN,B1.VERKAUFSBEZEICHNUNG,H1.AUFT
RAGSPOSITIONSNR,H1.DATUMSTR,H1.OID,H1.PRODUKT,H1.VORGANGSNUMMER,'Stamm'
SOURCE
from ZPAB.FAHRZEUG F1 , ZPAB.FZGBRIEF B1 , ZPAB.HISTORIE H1
where F1.FZGBRIEF = B1.OID AND F1.OID = H1.MYTECHOBJEKT(+) AND
((H1.produkt, TO_DATE(H1.DATUMSTR,'YYYY-MM-DD')) IN (select
ZPAB.HISTORIE.produkt, TO_DATE(MAX(ZPAB.historie.DATUMSTR),'yyyy-mm-dd')
from ZPAB.historie, ZPAB.FAHRZEUG
WHERE ZPAB.FAHRZEUG.OID = ZPAB.historie.MYTECHOBJEKT AND ZPAB.FAHRZEUG.OID =
F1.OID AND ZPAB.historie.PRODUKT
IN('HU','AU','SP','HUPlus','GGVS','P193','P21','UVVFahrzeuganbau','Ersatzpla
kette','SOL')
group by ZPAB.historie.produkt) OR H1.PRODUKT IN
('StandardGutachten','SchadenGutachten','BewertungZustandspruefung','MagBewe
rtungZustandspruefung','Transportschadenbericht','Reparaturpruefung','Rechnu
ngspruefungsbericht','Reparaturpruefungsbericht','FzgSchadengutachten','Hage
lschadenbericht','Massenschadenbericht','Kalkulationsbericht','Schadenberich
t','Bericht','DekraSiegel','NfzSchadenGutachten','HypoWBWGutachten','MagGuta
chten','MagBewertung','Bewertung') OR F1.OID NOT IN (SELECT
ZPAB.FAHRZEUG.OID
FROM ZPAB.FAHRZEUG,ZPAB.HISTORIE
WHERE ZPAB.FAHRZEUG.OID = ZPAB.historie.MYTECHOBJEKT)) AND
(F1.AMTLICHESKENNZEICHEN LIKE 'DD%')AND rownum <=10
and for HISTORIE, it is showing TABLE ACCESS FULL.
I have created indexes on produkt and mytechobjekt columns of historie
table. Still I am getting full table scan errors. I am sending herewith
the snapshot of the explain plan as seen in spotlight.
Could you please advise me as to how do I eradicate the full table access
error on Historie table? Any help in this regard is very much appreciated.
Thanks and Regards,
Ranganath
<<Doc1.doc>>
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this message
by anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
Doc1.doc
Description: MS-Word document
