FAQ
You have a outer join in yr history table. Also you have a IN condition for
the history table for which the CBO might think a FTS might be cheaper..

Babu
----- Original Message -----
To: "Multiple recipients of list ORACLE-L"
Sent: Friday, January 03, 2003 7:19 AM
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

<>


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.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Babu Nagarajan
INET: orclbabu_at_hotmail.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 3 | next ›
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 3, '03 at 12:19p
activeJan 3, '03 at 3:53p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase