KAK OPTIMIROWAT PERFORMANCE (SELECT dlitsia dolgo)

Imeetsia join,self join, outer join:
Wse polia kotorie uchastwuut w WHERE jawliautsia poliami is PK, a snachit na nih index uje lejit.
Sapustil w tode prowerku na Explain plan. Pokasal dlia sleduushil tabliz: split, idat, stat
TABLE ACCESS FULL
Chto mojno optimirowat?


FROM kap.split a,
kap.split mg,
kap.pdat b,
kap.stat c,
kap.idat d
WHERE a.bzppl = '111111'
AND a.posnr = '121212'
AND mg.posnr = '999999'
AND a.monat = mg.monat
AND a.jahr = mg.jahr
AND a.lcod = mg.lcod
AND a.fin_finr = mg.fin_finr
AND a.finr = mg.finr
AND a.artmatnr = mg.artmatnr
AND mg.bzppl = a.bzppl
AND a.cmg_sk = mg.cmg_sk
AND c.bzp = a.bzppl
AND c.finr = a.finr
AND UPPER (c.datenart) = 'M'
AND UPPER (c.modul) = 'SK'
AND d.finr = a.fin_finr
AND TO_DATE (a.jahr || '12' || '28', 'YYYYMMDD') BETWEEN d.gue_ab
AND d.gue_bis
AND b.finr(+) = a.finr
AND b.bzpapdat(+) = a.jahr || a.monat;

iudey

Imeetsia join,self join, outer join: Wse polia kotorie uchastwuut w WHERE jawliautsia poliami is PK, a snachit na nih index uje lejit. Sapustil w tode prowerku na Explain plan. Pokasal dlia sleduushil tabliz: split, idat, stat TABLE ACCESS FULL

План приведите

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 402
NESTED LOOPS OUTER 1 239 402
NESTED LOOPS 1 223 401
HASH JOIN 1 154 361
MERGE JOIN CARTESIAN 316 13 K 113
TABLE ACCESS FULL KAP.STAT 1 21 100
BUFFER SORT 3 K 86 K 13
TABLE ACCESS FULL KAP.IDAT 3 K 86 K 13
TABLE ACCESS FULL KAP.SPLIT 14 K 1 M 248
TABLE ACCESS BY INDEX ROWID KAP.SPLIT 1 69 40
INDEX RANGE SCAN KAP.SPLIT_PK 1 39
TABLE ACCESS BY INDEX ROWID KAP.PDAT 1 16 1
INDEX UNIQUE SCAN KAP.PDAT 1 0

вот это нормально распишите...

AND TO_DATE (a.jahr || '12' || '28', 'YYYYMMDD') BETWEEN d.gue_ab
AND d.gue_bis

или FBI сделайте

Spaisbo!!!!! S FBI eshe ne wstrechalsia, dogadiwaus, chto eto swiasanno s indexom.
Dayte nawodku..

AND TO_DATE (a.jahr || '12' || '28', 'YYYYMMDD') BETWEEN d.gue_ab
AND d.gue_bis


s Pomoshu <, > etogo wi imeete wwidu?

24 minute dlitsia select dlia 14000 rows(bes fbi)

было бы неплохо увидеть полный список индексов...

было бы неплохо, если бы автор сначала вот это прочел

Drugi moi, absolutno s wami soglasen, chto kosaetsia oformlenia soobshenia. Poymite,
pishu ja s raboti, sdes polnaja sekretnost i wse prosmatriwaetsia, poetomu prihoditsia imena tabliz ismeniat, chto bi ne nastuchali poboshke....