Re: Full table scan error

2 things:

1. don't send attachments to the list, they rarely arrive unscathed. Yours didn't.
2. why do you think the full table scan is an 'error'? It could be that an FTS is the fastest access method for this query.
3. ok, three things. Please make an attempt to format longish queries so that they are somewhat readable.

Personally, I always format queries before working with them, otherwise it is too difficult to determine what is being done in the query.

Jared

On Friday 03 January 2003 04:19, Krishnaswamy, Ranganath wrote:
> 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.FA
>H
> RZEUGHERSTELLERTEXT,B1.FAHRZEUGTYPTEXT,B1.FIN,B1.VERKAUFSBEZEICHNUNG,H1.AUF
>T 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','Ersatzpl
>a kette','SOL')
> group by ZPAB.historie.produkt) OR H1.PRODUKT IN
> ('StandardGutachten','SchadenGutachten','BewertungZustandspruefung','MagBew
>e
> rtungZustandspruefung','Transportschadenbericht','Reparaturpruefung','Rechn
>u
> ngspruefungsbericht','Reparaturpruefungsbericht','FzgSchadengutachten','Hag
>e
> lschadenbericht','Massenschadenbericht','Kalkulationsbericht','Schadenberic
>h
> t','Bericht','DekraSiegel','NfzSchadenGutachten','HypoWBWGutachten','MagGut
>a 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.

Content-Type: application/msword; name="Doc1.doc"
Content-Transfer-Encoding: 7bit
Content-Description: 
----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).