[Oracle] Meest efficiente manier voor dubbele voorwaarden

[Deddiekoel]

De titel is niet bijster duidelijk dus zal ik het hier wat beter uitleggen.

Ik heb een childtabel X met daarin een hoop records. Uit deze tabel wil ik alle records opvragen waarvan het documentnummer (die NIET uniek is) voldoet aan de volgende voorwaarde. Voor alle documentnummers mag veld Y niet niet in een speciale lijst voorkomen. Deze lijst wordt opgehaald met een sub-select.

Dus voor een documentnummer zijn verschillende records aanwezig in tabel X. Als van al deze records veld Y niet in de lijst voorkomt dan pas mag het documentnummer worden teruggegeven.

Op zich is er al een oplossing maar deze duurt nogal lang (ruim een uur) om te draaien. Daarom ben ik op zoek naar de meest efficiente oplossing voor dit probleem.

[NMe]

En welke oplossing had je al?

[Deddiekoel]

De oplossing die er al ligt is een om eerst naar de betreffende tabel te gaan om daar ervoor te zorgen dat het documentnummer bestaat. Daarna wordt er nog een keer naar de tabel gegaan om er voor te zorgen dat er het documentnummer niet in de lijst voorkomt.

[Swa-baldie]

Kun je je query niet even posten? Dan kunnen we daar naar kijken? Kun je tevens iets meer zeggen over de relaties tussen de tabellen en eventuele indexen ?

[justmental]

Als je een "not in" constructie hebt met een niet gecorreleerde subquery dan kun je vaak met een HASH_AJ hint de optimizer de goede kant op helpen.

[Deddiekoel]

justmental schreef op 30 september 2004 @ 09:32:
Als je een "not in" constructie hebt met een niet gecorreleerde subquery dan kun je vaak met een HASH_AJ hint de optimizer de goede kant op helpen.

Ik voel me nu een redelijk n00b. Het klinkt bijzonder interessant maar zou je me iets meer uitleg geven. Wat is een gecorreleerde subquery en wat is een HASH_AJ hint?

Wat ik nu probeer te bewerkstelligen is een soort van dubbele count. Ik wil met een count alle records tellen zonder voorwaarde én een count die alle records tellen die aan een voorwaarde voldoen (bepaalde vlag). Is zoiets mogelijk want dan kan ik die twee counts vergelijken en als ze gelijk (dus alle records voldoen aan de voorwaarde) dan mag de record door. Maar is een dergelijke count mogelijk?

[justmental]

Zo blind kunnen we weinig voor je betekenen.
Wat zijn je (betrokken) tabellen met de aantallen records waar het om gaat?
Wat is je query?

[Verwijderd]

Voor elk record in X wordt een subquery gedaan op de 'speciale lijst' (Z).
Stel X bevat 100000 records en Z bevat 100000 records. De subquery wordt dan 100000 keer uitgevoerd. Het aantal keren dat een record wordt gelezen is dan 10 miljoen. Dit is een worst case scenario!
Maar je moet ORACLE een beetje helpen met hints, zoals eerder voorgesteld. De subquery wordt dan 1 maal uitgevoerd en het resultaat tijdelijk hashed, directe toegang op waarde van Y opgeslagen. Het aantal read's wordt nu 100000 keer Z plus 100000 keer X plus 100000 keer hashed Z = 300000. Dus 30 keer minder reads. Je doorlooptijd van een uur wordt dus 2 minuten! Kassa! Mag ik even vangen?!

[Deddiekoel]

SWEET!
En hoe implementeer ik zo'n HASH?

[Verwijderd]

Google: oracle hint hash

[Swa-baldie]

justmental schreef op 30 september 2004 @ 10:10:
Zo blind kunnen we weinig voor je betekenen.
Wat zijn je (betrokken) tabellen met de aantallen records waar het om gaat?
Wat is je query?

Post nu ff deze dingen dan kunnen we je echt helpen

[Deddiekoel]

Dit is de query in al haar glorie. Deze query levert de juiste recordset op, alleen doet deze er dus ruim een uur over.

code:

SELECT 
    DOCNR,  DOCTYPE, CMP 
FROM A, B, 
    (
    SELECT DOCNR, DOCTYPE, CMP
    FROM C 
    WHERE C4= 'n'  
  AND C5 = 'm' ) LO
WHERE  
    A.SHHOLD = '  '
    AND A.CMP = '1138'
    AND TRIM(A.A4) IN ('00001', '00001')
    AND A.DOCTYPE IN ('Val1', 'Val2', 'Val3')  
    AND A.DOCNR = B.DOCNR (+)
    AND A.DOCTYPE = B.DOCTYPE (+)
    AND A.CMP = B.CMP (+)
    AND LO.DOCNR = A.DOCNR
    AND LO.DOCTYPE = A.DOCTYPE
    AND LO.CMP = A.CMP
    AND B.B4 IN ('X ', 'Y ', 'Z')
    AND B.B5 >= BVal1
    AND B.B5 <= BVal2
    AND A.DOCNR not in (
        SELECT distinct(D.DOCNR) 
        FROM D
        WHERE D.CMP = '1138' 
            AND D.DOCTYPE IN ('Val1', 'Val2', 'Val3')       
            AND trim(D4) in (
              SELECT trim(E_KEY0) 
                FROM E
              WHERE E_KEY1 = '59'  
                AND E_KEY2 = 'QC'
                AND E_FLAG = 'N' 
            )
    )
    AND A.DOCNR in (
        SELECT distinct(D.DOCNR) 
        FROM D
        WHERE D.CMP = '1138' 
        AND D.DOCTYPE IN ('Val1', 'Val2', 'Val3')
    )
    GROUP BY A.CMP, A.DOCNR, A.DOCTYPE

Ik heb (en had al) op het net gezocht naar hoe ik die hints moet implementeren maar het is me nog niet duidelijk...

[ Voor 38% gewijzigd door Deddiekoel op 30-09-2004 12:36 ]

[Swa-baldie]

Jeetje wat een beroerde querie, de (+) gaan je sowieso al geen snelheidswinst opleveren. Misschien dat je je datamodel ook nog kan vermelden? Ziet er nl niet allemaal evenhandig uit (hoe liggen de relaties tussen de tabellen?)

Wat je volgens mij in ieder geval al kunt toepassen ipv de 'not in' en 'in' beperking op A.DOCTNR via tabel D is onderstaand

code:

 AND EXSIST IN (
        SELECT 'dummy' 
        FROM D
        WHERE 
        AND    A.DOCNR = D1
        AND D3 = '1138' 
        AND D2 IN ('Val1', 'Val2', 'Val3')
            AND trim(D4)  not in (
              SELECT trim(DRKY) 
                from E
              WHERE E_KEY1 = '59'  
                    AND E_KEY2 = 'QC'
                    AND E_FLAG = 'N' 
            )
    )

[ Voor 9% gewijzigd door Swa-baldie op 30-09-2004 12:40 . Reden: foutje in de code ]

[DaCoTa]

Wat krijg je als je explain plan uitvoert op deze query? Misschien is het wel gewoon een kwestie van de goede indexen aanleggen, of is de database verder fixed?

[Deddiekoel]

Het grote probleem is het vinden van het DOCNR uit tabel D waarvoor alle records voldoen aan de voorwaarde dat ze niet in de lijst uit de select tabel E voorkomen. In tabel D staan dus meerdere records voor een bepaald DOCNR. Hiervan kunnen de individuele records wel of niet voldoen aan de voorwaarden uit Tabel E. Alleen als ALLE records voor een specifiek DOCNR niet aan de voorwaarde uit tabel E voldoen dan pas mag dat DOCNR terug worden gegeven.

Hoe zou ik de originele query middels een hint kunnen versnellen? Want als tabel D wordt opgeslagen dan zou dat enorm schelen. Die tabel is nu meen ik 660000 records groot...

[Verwijderd]

Je probleem zit hem in 'trim(D4) not in (SELECT trim(DRKY)'. Op deze manier forceer je een full table scan op tabel E voor iedere rij in tabel D. Dus je cartesisch produkt wordt 660.000 maal hoeveelheid rijen in E, en dat alleen voor een sub-select.

[Deddiekoel]

Dan klopt wel aardig, want als ik dat deel weglaat dan gaat alles een stuk sneller. Maar hoe moet het dan beter. Ik heb al geprobeert om alles met joins te doen en de subselects weg te laten. Maar dan krijg ik niet de goede recordset.

Waar ik ook aan zat te denken was om een subselect of view te maken waarin ik voor alle DOCTYPES in tabel D de records tel én de records tel die niet in tabel E voorkomen. Als deze twee getallen overeenkomen betekent dat dus dat alle records goed zijn en dat het DOCTYPE geselecteerd moet worden. Ik heb alleen nog geen idee hoe ik dat voor elkaar moet gaan boksen....

[Verwijderd]

The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access the specified table.
Probeer eens:

SELECT /*+ HASH_AJ(E) */
'dummy'
FROM D
WHERE
AND A.DOCNR = D1
AND D3 = '1138'
AND D2 IN ('Val1', 'Val2', 'Val3')
AND trim(D4) not in (
SELECT trim(DRKY)
from E
WHERE E_KEY1 = '59'
AND E_KEY2 = 'QC'
AND E_FLAG = 'N'

Met de tool TOAD kan je eerst de 'explain plans' bekijken zonder de query daadwerkelijk uit te voeren. Wat je veel tijd kan besparen. Je ziet dan ook direct waar het zeer doet.

[Deddiekoel]

Explain plan wil niet werken....

Maar ik heb inmiddels een oplossing waarvan ik nog maar 1 obstakel hoef te overkomen. Momenteel creeer ik een speciale view. Wat deze view doet is eerst de verschillende flag waarden bij de unieke document nummers neerzetten. Vervolgens gaat hij er nog een keer overheen om te kijken welke document nummers maar een enkele flag waarde hebben. Dit werkt dus om alle document nummers die goede en slechte records hebben eruit te filteren. Helaas is het ook zo dat document nummers die alleen maar slechte records hebben ook in de lijst komen. Ik moet nu nog bedenken hoe ik ervoor kan zorgen dat als de er maar een enkele flagwaarde is deze flag positief is...