Problem with Oracle Indexes

[AndreasAuer]

Hi

I use somw views to calculate a "Status" over some Tables. The explain plan shows one Full Table scan and I tried to make Indexes but I didn't find the correct index.

Does anyone know an easy manual on how to make indexes?

Andreas


p.s.: here's the view

CREATE OR REPLACE VIEW VW_STATUS_WERT_KLEIN ( AUFNR,
AUFTRAGSSUMME, STATUS, LFDANGAUFGUTNR, LETZTLIEFDATUM,
GLASLIEFDATUM ) AS SELECT
distinct au.aufnr as aufnr,
decode(ag.waehrung,3,(ag.summwst1/decode(ag.mwst1, 0, 1, ag.mwst1)*100),(ag.summwst1/decode(ag.mwst1, 0, 1, ag.mwst1)*100)/13.7603) as auftragssumme,
decode(au.pb_kommiss_am, null, decode(min(pr.status), 4,5 ,3, 4, 2, 3, decode(au.pb_ausmstatus, 1, 1, 0)), 6) as status,
au.lfdangaufgutnr,
au.pb_lieferdat_mat as letztliefdatum ,
gl.glasliefdatum as glasliefdatum
FROM
auftrag au,
angaufgut ag,
produktion pr,
vw_status_glas gl
WHERE
(pr.artnr in (select lfdnr from artikel where artnr1 <> 'VERSAND') or pr.artnr is null) and
ag.aagaufart in (2,8,11) and
au.lfdangaufgutnr=ag.lfdnr and
au.aufnr=pr.bezugnr(+) and
au.aufnr=gl.aufnr(+)
group by au.aufnr ,ag.summwst1,ag.mwst1, ag.erledigt, au.lieferschein, au.pb_nichtverr, au.pb_kommiss_am, au.pb_ausmstatus, au.lfdangaufgutnr, au.pb_lieferdat_mat, gl.glasliefdatum, ag.waehrung

and here's the explain plan

SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1559702771602 Card=493098476253 Bytes=29585908575180)
VIEW OF VW_STATUS_WERT_KLEIN (Cost=1559702771602 Card=493098476253 Bytes=29585908575180)
SORT (UNIQUE) (Cost=1559702771602 Card=493098476253 Bytes=139546868779599)
SORT (GROUP BY) (Cost=1559702771602 Card=493098476253 Bytes=139546868779599)
FILTER
MERGE JOIN (OUTER) (Cost=322098 Card=493098476253 Bytes=139546868779599)
MERGE JOIN (OUTER) (Cost=29663 Card=73183682 Bytes=18442287864)
SORT (JOIN) (Cost=17488 Card=61151 Bytes=12230200)
NESTED LOOPS (Cost=212 Card=61151 Bytes=12230200)
INLIST ITERATOR (CONCATENATED)
TABLE ACCESS (BY INDEX ROWID) OF ANGAUFGUT (Cost=6 Card=206 Bytes=20600)
INDEX (RANGE SCAN) OF PB_ANGAUFGUT_AUFART_LFDNR (UNIQUE) (Cost=2 Card=206)
TABLE ACCESS (BY INDEX ROWID) OF AUFTRAG (Cost=1 Card=29685 Bytes=2968500)
INDEX (UNIQUE SCAN) OF AUFLFDNR (UNIQUE)
SORT (JOIN) (Cost=12175 Card=119677 Bytes=6223204)
TABLE ACCESS (FULL) OF PRODUKTION (Cost=180 Card=119677 Bytes=6223204)
SORT (JOIN) (Cost=292435 Card=673782 Bytes=20887242)
VIEW OF VW_STATUS_GLAS (Cost=238443 Card=673782 Bytes=20887242)
SORT (GROUP BY) (Cost=238443 Card=673782 Bytes=64009290)
NESTED LOOPS (Cost=573 Card=673782 Bytes=64009290)
TABLE ACCESS (BY INDEX ROWID) OF BESTELLUNG (Cost=10 Card=563 Bytes=31528)
INDEX (RANGE SCAN) OF BESTSTATUS (NON-UNIQUE) (Cost=2 Card=563)
TABLE ACCESS (BY INDEX ROWID) OF PRODUKTION (Cost=1 Card=119677 Bytes=4667403)
INDEX (UNIQUE SCAN) OF PK_PRODUKTION (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF ARTIKEL (Cost=2 Card=1 Bytes=25)
INDEX (UNIQUE SCAN) OF PK_ARTIKEL (UNIQUE) (Cost=1 Card=29)

 

[Dagon]

Create indexes on the joining columns of your tables. I'd also recommend you analyze all the tables i.e.

analyze table xxx compute statistics;

or, if the table is very big,

analyze table xxx estimate statistics;

 

[jimbopalmer]

au.aufnr=pr.bezugnr(+) and

in this line you do an outer join as if the column in producktion may not exist, so oracle ignores the index and full table scan as the index does not contain Nulls. I tried to remain child-like, all I acheived was childish.

 

[raygg]

Please translate the German words into English language equivalents in the sample code.