[oracle/sql] onbestaande records selecteren

[Verwijderd]

Ik heb al even zoeken op dit probleem maar kom er niet uit.

Probleem is als volgt : personen kunnen hun aantal werkuren per dag ingeven en dit wordt opgeslaan in de tabel copy_work. Nu is het de bedoeling dat men kan zien, wie nog niets heeft ingevuld voor een bepaalde dag. Ik heb daar een werkende query voor, maar deze gaat enorm traag (copy_work bevat ong 200.000 records)

De database ziet er als volgt uit:

tabel copy principal:
- principalid
- projectid
- ...

tabel copy_work:
- workid
- workdate
- workhours
- workprincipalid (dit is dus de link naar de andere tabel)

De query hieronder werkt dus, maar is te traag.

code:

$query = "SELECT copy_principal.PRINLOGINNAME,copy_principal.PRINID 
FROM copy_principal 
WHERE copy_principal.PRINID 
      NOT IN (SELECT copy_work.WORKPRINCIPLEID FROM copy_work 
            WHERE copy_work.WORKDATE=to_date('".$today."','yyyymmdd')) 
ORDER BY copy_principal.PRINID";

Wat ik dan geprobeerd heb is om eerst de subquery uit te voeren en de resultaten op te slaan in een tijdelijke tabel. Daarna dacht ik van de id's die in de tijdelijk tabel staan te vergelijken met de id's in de tabel met personen, maar blijkt dus dat er voor elke waarde uit de ene tabel vergeleken wordt met elke waarde uit de 2e tabel, wat resulteert in dubbele resultaten.
Nu heb ik mij al suf gezocht en heb een bijna werkende query :

code:

SELECT copy_principal.PRINLOGINNAME, copy_principal.PRINID 
FROM copy_principal, TEMP 
WHERE copy_principal.PRINID != TEMP.WORKPRINCIPLEID 
GROUP by copy_principal.PRINID, copy_principal.PRINLOGINNAME 
HAVING count(copy_principal.PRINID)>3

waarbij de 3 achteraan het aantal records is in de tijdelijke tabel-1

Nu dacht ik dat het mogelijk zou zijn om een count() van de 2e tabel te gebruiken en deze zou dan dynamisch zijn, maar om de een of andere reden, is die count verschillend als er wel een overeenkomst gevonden wordt en zit ik dus een beetje vast.


Hopelijk weten jullie of het mogelijk is om dit probleem op te lossen, zoniet zal men enorm lang op de data moeten wachten.

[ Voor 3% gewijzigd door Verwijderd op 18-03-2005 13:45 ]

[bigbeng]

Het magische woord is index en wel op workdate. Dat zou je zo al een flink aantal seconden kunnen schelen.

[leuk_he]

Doe ons eerst effe een lol en formateer de SQL blokken ff over meer regels.

Ik zelf dacht aan de eerste gewoon te optimaliseren:

code:

$query = "SELECT copy_principal.PRINLOGINNAME,
               copy_principal.PRINID 
FROM copy_principal 
WHERE NOT EXISTS (SELECT copy_work.WORKPRINCIPLEID 
         FROM copy_work 
         WHERE copy_work.WORKDATE=to_date('".$today."','yyyymmdd')) 
           and copy_work.WORKPRINCIPLEID  = copy_principal.PRINID 
         ORDER BY copy_principal.PRINID";

Waarbij je ECHT moet checken of de indexen op copy_work.WORKPRINCIPLEID & copy_principal.PRINID bestaan.

draai op een plan_tables voor je query.

[Verwijderd]

leuk_he schreef op vrijdag 18 maart 2005 @ 13:40:
Ik zelf dacht aan de eerste gewoon te optimaliseren:

code:

$query = "SELECT copy_principal.PRINLOGINNAME,
               copy_principal.PRINID 
FROM copy_principal 
WHERE NOT EXISTS (SELECT copy_work.WORKPRINCIPLEID 
         FROM copy_work 
         WHERE copy_work.WORKDATE=to_date('".$today."','yyyymmdd')) 
           and copy_work.WORKPRINCIPLEID  = copy_principal.PRINID 
         ORDER BY copy_principal.PRINID";

Waarbij je ECHT moet checken of de indexen op copy_work.WORKPRINCIPLEID & copy_principal.PRINID bestaan.

Als ik deze query test, krijk ik een error, indien ik echter het haakje na to_date() verplaats naar de regel eronder (dus net voor de order by) krijgk wel het gewenste resultaat, maar nog niet zo heel erg snel.

Indien jullie denken dat het wss niet beter zal gaan, laat het dan ff weten, dan laat ik het zo

draai op een plan_tables voor je query.

en wat wil je hiermee zeggen? (ben niet echt mee met deze zin)


Edit: BigBen zn oplossing in combinatie met deze van leuk_he heeft de truc gedaan, van 20+ sec naar 1 sec ofzo.

Hartelijk dank

[ Voor 20% gewijzigd door Verwijderd op 18-03-2005 14:03 ]

[leuk_he]

Verwijderd schreef op vrijdag 18 maart 2005 @ 13:52:
[...]

Als ik deze query test, krijk ik een error, indien ik echter het haakje na to_date() verplaats naar de regel eronder (dus net voor de order by) krijgk wel het gewenste resultaat, maar nog niet zo heel erg snel.

had hem ook niet getest....

en wat wil je hiermee zeggen? (ben niet echt mee met deze zin)

explain plan: kijk of hij de indexes (je gebruikt toch indexes) goed gebruikt.

http://www.lc.leidenuniv....er.920/a96533/ex_plan.htm


(evt statistics verzamlen , lig t aan db versie. http://www.lc.leidenuniv....20/a96533/stats.htm#26713 )

/edit: ik zie nu dat je geedit hebt en dat inderdaad nog een index moest worden teogevoegd.

[ Voor 8% gewijzigd door leuk_he op 18-03-2005 14:04 ]