Επεξεργασία και βελτιστοποίηση σχεσιακών ερωτημάτων

1. Επεξεργασία και βελτιστοποίηση σχεσιακών ερωτημάτων Βάσεις Δεδομένων II Τμήμα Διδακτικής της Τεχνολογίας και Ψηφιακών Συστημάτων Δαμιανός Χατζηαντωνίου

2. Βασικά βήματα στην επεξεργασία ερωτημάτων 1. Μετάφραση 2. Βελτιστοποίηση 3. Υπολογισμός

3. Βασικά βήματα στην επεξεργασία ερωτημάτων Μετάφραση (Parsing and translation) • Μετάφραση του ερωτήματος σε μία internal μορφή. Αυτή μετατρέπεται κατόπιν σε σχεσιακή άλγεβρα. • Ο Parser ελέγχει το συντακτικό και τους πίνακες. Υπολογισμός (Evaluation) Η μηχανή εκτέλεσης ερωτημάτων(query-execution engine)παίρνει το σχέδιο υπολογισμού (query-evaluation plan), το εκτελεί, και επιστρέφει την απάντηση στο χρήστη.

4. Βασικά βήματα στην επεξεργασία ερωτημάτων Βελτιστοποίηση–εύρεση του πλάνου υπολογισμού με το ελάχιστοκόστος • Μία έκφραση σχεσιακής άλγεβρας μπορεί να έχει πολλές ισοδύναμες μορφέςΠ.χ., balance2500(balance(account)) είναι ισοδύναμη με: balance(balance2500(account)) • Κάθε έκφραση σχεσιακής άλγεβρας μπορεί να υπολογιστεί με διάφορετικούς τρόπους. Μία «σημειωμένη» έκφραση (annotated expression) η οποία σημειώνει τον τρόπο υπολογισμού καλείταισχέδιο υπολογισμού(evaluation-plan).Π.χ.,μπορεί να χρησιμοποιηθείένα ευρετήριο επί του balanceγια να βρεθούν λογαριασμοί με balance < 2500, η να σαρωθεί πλήρως ο πίνακας • Από όλες τις ισοδύναμες εκφράσεις, επέλεξε αυτή με το «φθηνότερο» σχέδιο υπολογισμού. Κατάλογος με κόστη.

5. Παράδειγμα – παράσταση αλγεβρικών εκφράσεων • Έστω το ερώτημα: «βρες το όνομα των πελατών με λογαριασμούς με υπόλοιπο κάτω από 2500», το οποίο έχει μετατραπεί σε σχεσιακή άλγεβρα: πcustomer-name (σbalance<2500(account) customer)

6. Παράδειγμα – ισοδύναμες αλγεβρικές εκφράσεις • Μία αλγεβρική έκφραση μπορεί να μετατραπεί σε ισοδύναμες εκφράσεις βάση κανόνων ισοδυναμίας (equivalence rules, transformation rules).

7. Παράδειγμα – «σημειωμένες» εκφράσεις • Έχοντας μία αλγεβρική έκφραση όπως προηγουμένως, σημειώνουμε πάνω στο δέντρο τους αλγόριθμους με τους οποίους υπολογίζεται κάθε τελεστής και τον τρόπο «διάχυσης» των δεδομένων από επίπεδο σε επίπεδο.

8. Παράδειγμα – «σημειωμένες» εκφράσεις • Παρατηρήσεις: • Δεδομένου ότι υπάρχουν διαφορετικοί τρόποι για να υπολογίσουμε έναν τελεστή και διαφορετικοί τρόποι για να «διαχύσουμε» τα δεδομένα, η ίδια αλγεβρική έκφραση γίνεται «annotated» με πολλούς τρόπους. • Σύμφωνα με κάποια μοντέλα κόστους, κάθε annotated έκφραση έχει κάποιο κόστος. • Αυτό το κάνουμε για κάθε ισοδύναμη αλγεβρική έκφραση. Θεωρητικά, επιλέγουμε από όλα τα δυνατά πλάνα, εκείνο με το μικρότερο κόστος.

9. Σχεσιακοί τελεστές • Θα εξετάσουμε πως να υλοποιήσουμε: • Selection ( ) Επιλέγει υποσύνολο γραμμών από σχέση. • Projection ( ) Διαγράφει στήλες από μία σχέση. • Join ( ) Επιτρέπει να συνδυαστούν δύο σχέσεις. • Set-difference ( ) Γραμμές στην 1η σχέση και όχι στη 2η. • Union ( ) Γραμμές και στην 1η και στη 2η σχέση. • Aggregation (SUM, MIN, etc.) καιGROUP BY • Αφού κάθε τελεστής επιστρέφει μία σχέση, οι τελεστές συντίθενται! Αφού εξετάσουμε τους τελεστές, θα δού-με πως να βελτιστοποιήσουμε τα σύνθετα ερωτήματα

10. Υπολογισμός τελεστών-Γενικές έννοιες • Τεχνικές αλγορίθμων: • Επανάληψη • Ευρετηριοποίηση • Διαμέριση • Διαδρομές πρόσβασης (access paths) • Σάρωση αρχείου • Ευρετήριο + κατάλληλη συνθήκη επιλογής • Επιλεκτικότητα της διαδρομής πρόσβασης (selectivity): πλήθος των σελίδων που ανακτώνται για να ανακτήσουμε τις εγγραφές που θέλουμε. • Κόστος: μετράμε μόνο το Ι/Ο (πλήθος σελιδών).

11. Schema για τα παραδείγματα Sailors (sid: integer, sname: string, rating: integer, age: real) Reserves (sid: integer, bid: integer, day: dates, rname: string) • Reserves: • Κάθε πλειάδαείναι 40 bytes, 100 εγγραφές ανά σελίδα, 1000 σελίδες. • Sailors: • Κάθε πλειάδαείναι 50 bytes, 80 εγγραφές ανά σελίδα, 500 σελίδες.

12. SELECT * FROM Reserves R WHERE R.rname < ‘C%’ Απλά Selections • Της μορφής: • Το μέγεθος του αποτελέσματος προσεγγίζεται σανsize of R * reduction factor; Θα εξετάσουμε αργότερα πως να εκτιμούμε το reduction factor. • Χωρίς ευρετήριο και ταξινόμηση. • Ταξινομημένα δεδομένα. • Ευρετήριοστο γνώρισμα επιλογής • Β+ δέντρο. • Κατακερματισμένα ευρετήρια.

13. Χωρίς ευρετήρια • Μη-ταξινομημένα δεδομένα • Σάρωση όλης της σχέσης • Κόστος: Μ (ο αριθμός των σελίδων της σχέσης) • Π.χ. R.name=‘Joe’ είναι 1000 Ι/Οs. • Ταξινομημένα δεδομένα • Δυαδική αναζήτηση για την πρώτη εγγραφή • Κατόπιν σειριακή ανάκτηση σελίδων όσο η συνθήκη ικανοποιείται. • Κόστος log2M + ανάκτηση σελιδών • Π.χ. R.name=‘Joe’ είναι 10 Ι/Οs + α.σ.

14. Χρησιμοποιώντας ευρετήρια – Β+ δέντρα • Το κόστος εξαρτάται από τον # των εγγραφών που πληρούν τη συνθήκη και το clustering. • Το κόστος για να βρούμε τις εγγραφές (συνήθως μικρό)+ κόστος για να ανακτήσουμετις εγγραφές (μπορεί να είναι μεγάλο χωρίς clustering). • Π.χ. Θεωρώντας ομοιόμορφη κατανομή των ονομάτων, και έστω ότι 10% των εγγραφών πληρούν τη συνθήκη (100 pages, 10000 εγγραφές). Με ένα clustered ευρετήριο, το κόστος είναι λίγο παραπάνω από 100 I/Os; Εάν όμως το ευρετήριο είναι unclustered, μπορεί και 10000 I/Os! • Σημαντική τροποποίησηγια unclustered ευρετήρια: 1. Βρες τις εγγραφές (από το ευρετήριο) που πληρούν τη συνθήκη. 2. Ταξινόμησε τα rid’s (record ids) των εγγραφών που θα ανακτηθούν. 3. Ανάκτησε τα rids σε σειρά. Αυτό εγγυάται ότι κάθε data page θα ερευνηθεί μία μόνο φορά (παρόλο που ο αριθμόςτων σελιδών θα είναι μεγαλύτεροςαπό τον αντίστοιχο με clustering).

15. Χρησιμοποιώντας κατακερματισμένα ευρετήρια • Χρήσιμο αν στη συνθήκη ο τελεστής op είναι = • Ένα ή δύο Ι/Ο για την ανάκτηση του bucket + το κόστος ανάκτησης των εγγραφών. • Π.χ. R.name=‘Joe’ και έστω ότι υπάρχουν 100 πλειάδες με R.name=‘Joe’. Πόσα Ι/Ο?

16. Γενικές συνθήκες επιλογής* • (day<8/9/94 AND rname=‘Paul’) OR bid=5 OR sid=3 • Αυτές οι συνθήκες πρώτα μετατρέπονται σεconjunctive normal form (CNF) – κανονική μορφή σύζευξης : (day<8/9/94 OR bid=5 OR sid=3 ) AND (rname=‘Paul’ OR bid=5 OR sid=3) • Εξετάζουμε μόνο την περίπτωση χωρίς ORs (μία σύζευξη όρων της μορφής: attr op value). • Ένα ευρετήριοταιριάζει (μία σύζευξη) όρωνπου εμπεριέχει μόνο γνωρίσματα σε έναprefixτου κλειδιού αναζήτησης. • Ευρετήριοστο<a, b, c> ταιριάζει τοa=5 AND b= 3, αλλά όχι τοb=3.

17. Η πράξη της Προβολής SELECTDISTINCT R.sid, R.bid FROM Reserves R • Μετατρέπεται σε πR.sid, R.bid (Reserves) • Απαραίτητα βήματα: • Απομάκρυνση των άχρηστων γνωρισμάτων • Απομάκρυνση των παραγόμενων διπλότυπων • Αλγόριθμοι: • Ταξινόμηση • Κατακερματισμός

18. Η πράξη της Προβολής - Ταξινόμηση • Βήματα αλγόριθμου: • Σάρωσε τον R και απομάκρυνε τα ανεπιθύμητα γνωρίσματα • Ταξινόμησε το αποτέλεσμα βάση όλων των γνωρισμάτων • Σάρωσε το ταξινομημένο αποτέλεσμα και απομάκρυνε τις διπλότυπες πλειάδες. Βήμα 1: Μ Ι/Ο για σάρωση και Τ Ι/Ο για τον προσωρινό πίνακα Βήμα 2: κόστος: Ο(ΤlogT) Βήμα 3: κόστος: Τ Ι/Ο • Π.χ. Στην περίπτωση του Reserves? • Μπορούμε να βελτιώσουμε τον αλγόριθμο ώστε να κάνει προβολή με απομάκρυνση διπλοτύπων (εκτός ύλης)

19. Η πράξη της Προβολής-Κατακερματισμός • Φάση διαμερισμού: Διάβασε το R χρησιμοποιώντας μία είσοδο (ένα input buffer). Για κάθε εγγραφή, πέταξε τα ανεπιθύμηταγνωρίσματα, και χρησιμοποίησε τη συνάρτηση κατακερματισμούh1στα υπόλοιπα γνωρίσματα για να επιλέξεις μία από τις B-1 εξόδους (output buffers). • Το αποτέλεσμα είναι B-1 διαμερίσεις (εγγραφών χωρίς τα ανεπιθύμητα πεδία). Δύο εγγραφές διαφορετικών διαμερισμάτων είναι διακριτές.

20. Η πράξη της Προβολής-Κατακερματισμός • Φάση απομάκρυνσης διπλοτύπων: Για κάθε διαμέριση, διάβασε την και κατασκεύασε έναπίνακα στη μνήμη κατακερματισμού χρησιμοποιώντας τη συνάρτησηh2 (<> h1) σε όλα τα πεδία, καθώς απομακρύνεις τα διπλότυπα. • Εάν η διαμέριση δε χωράει στη μνήμη, μπορεί να εφαρμόστεί αυτός ο αλγόριθμος αναδρομικά σε αυτή τη διαμέριση. • Κόστος: Για τη διαμέριση, το διάβασμα του R (Μ Ι/Ο)+ το γράψιμο κάθε εγγραφής, αλλά με λιγότερα πεδία (Τ Ι/Ο). Το αποτέλεσμαδιαβάζεται στην επόμενη φάση (Τ Ι/Ο). Σύνολο: Μ+2Τ σελίδες.

21. Η πράξη της προβολής • Η τεχνική ταξινόμησης είναι το standard; καλύτερος χειρισμός μη-ομοιορφίας + ταξινομημένο αποτέλεσμα • Εάν ένα ευρετήριο περιέχει όλα τα επιθυμητά πεδία στο κλειδί αναζήτησης, μπορεί να χρησιμοποιηθεί. • Εφάρμοσε τις τεχνικές προβολής στο ευρετήριο (μικρότερο). • Εάν ένα ταξινομημένο ευρετήριο (π.χ., δέντρο) index περιέχει όλα τα επιθυμητά πεδία σαν prefix στο κλειδί αναζήτησης, μπορούμε ακόμα καλύτερα: • Ανάκτησε τα στοιχεία του ευρετηρίου σε σειρά (index-only scan), πέταξε τα ανεπιθύμητα πεδία και σύγκρινε γειτονικές εγγραφές για διπλότυπα.

22. Συνολοθεωρητικές πράξεις • Η τομή και καρτεσιανό γινόμενο ειδικές περιπτώσεις σύζευξης • Η ένωση (Distinct) καιη διαφοράείναι παρόμοιες. Ένωση: • Προσέγγιση βασισμένη στην ταξινόμηση: • Ταξινόμησε τις δύο σχέσεις (ως προς το συνδυασμό όλων των πεδίων) • Σάρωσε τις ταξινομημένες σχέσεις και συγχώνευσέ τις. • Ενναλακτικά: Παραγωγή ταξινομημένων συρμών (runs) των δύο σχέσεων και παράλληλη συγχώνευση. (*) • Προσέγγιση βασισμένη στον κατακερματισμό: • Διαμέρισε τις R και S χρησιμοποιώντας μια συνάρτηση κατακερματισ. h. • Για κάθε S-διαμέριση, κατασκεύασε στη μνήμηέναν πίνακα κατακερμα-τισμού (χρησιμοποιώντας μίαh2), σάρωσε την αντίστοιχη R-διαμέριση και είτε πρόσθεσέ τηνστο πίνακα, είτε απόρριψέ την (διπλότυπα).

23. Η πράξη της σύζευξης SELECT * FROM Reserves R1, Sailors S1 WHERE R1.sid=S1.sid • Στην άλγεβρα: R S. Πολύ σύνηθες! Πρέπει να βελτιστοποι-ηθεί. R S πολύ μεγάλο,άρα, R S ακολουθούμενο από επιλογή είναι αναποτελεσματικό. • Υπόθεση: M πλειάδες στη R, pRπλειάδες ανά σελίδα, N πλειάδεςστη S, pSπλειάδες ανά σελίδα. • Στα παραδείγματά μας, R είναι η Reserves και S είναι η Sailors. • Μετρική κόστους: ο αριθμός των I/Os. • Χρησιμοποιούμε το συμβολισμό ri==sjγια τη συνθήκη σύζευξης

24. Απλή εμφώλευση βρόγχων • Απλή εμφώλευση βρόγχων (simple nested loop join) • Κόστος: M + pR * M * N • Π.χ. 1000 + 100*1000*500 = 1000+5*107 Ι/Ο (140 ώρες) • Βελτίωση: σύζευξη ανά σελίδες: για κάθε σελίδα της R, ανακτούμε μία-μία σελίδα της S και κάνουμε τη σύζευξη Εξωτερική σχέση foreach tuple r in R do foreach tuple s in S do if ri == sj then add <r, s> to result Εσωτερική σχέση

25. Απλή εμφώλευση βρόγχων for each page Br of R do begin for each page Bs of S do beginfor each tuple tr in Br do beginfor each tuple ts in Bs do begintest pair (tr,ts) for satisfying the join condition if they do, add <tr,ts> to the result.endendend end • Κόστος: Μ + Μ*Ν • Π.χ. 1000 + 1000*500 = 501.000 Ι/Ο (1.4 ώρες) • Ποιά σχέση πρέπει να επιλέγουμε σαν εξωτερική?

26. Εμφώλευση βρόγχων κατά block • Εστω ότι έχουμε αρκετή ενδιάμεση μνήμη για να χωρέσει μία από τις δύο σχέσεις (έστω R). Αλγόριθμος? • Φορτώνουμε την R στη μνήμη (κόστος Μ) και κατόπιν για κάθε σελίδα της S κάνουμε τη σύζευξη στη μνήμη (κόστος Ν). Συνολικό κόστος: Μ + Ν. • Ποια είναι η εξωτερική και ποια η εσωτερική σχέση σε αυτή την περίπτωση? • Βελτίωση: κατασκευή πίνακα κατακερματισμού στη μνήμη για την R (μείωση του χρόνου CPU, όχι Ι/Ο). • Τι κάνουμε αν δε χωράει στη μνήμη καμμία σχέση?

27. Εμφώλευση βρόγχων κατά block • Διαμερίζουμε την R σε blocks (ο όρος block διαφορετικός από τον όροσελίδα), που το καθένα χωράει στην ενδιάμεση μνήμη, και για κάθε block σαρώνουμε την S. • Έστω ότι έχουμε Β σελίδες ενδιάμεσης μνήμης. for each block Br (of size B-2 pages) of R do begin for each page Bs of S do beginfor each tuple tr in Br do beginfor each tuple ts in Bs do begintest pair (tr,ts) for satisfying the join condition if they do, add <tr,ts> to the result.endendend end

28. . . . Εμφώλευση βρόγχων κατά block • Κόστος: Μ + Ν * [Μ/(Β-2)] • Για το «ταίριασμα» των πλειάδων δημιουργούμε έναν πίνακα κατακερματισμού στη μνήμη. Αποτέλεσμα Σύζευξης R & S Πίνακας κατακερματισμού για το block της R(k < B-1 pages) . . . . . . Output buffer Input buffer for S Ενδιάμεση μνήμη (Β σελίδες)

29. Εμφώλευση βρόγχων κατά block • Παράδειγμα: • Χρησιμοποιώντας τη Reserves (R) σαν εξωτερική σχέση και δίνοντας 100 σελίδες σε κάθε block: • Κόστος σαρώματος της R είναι 1000 Ι/Οs • Έχουμε 10 blocks • Για κάθε block σαρώνουμε τη Sailors = 10*500 Ι/Οs • Σύνολο: 1000 + 5000 = 6000 Ι/Οs. • Χρησιμοποιώντας τη Sailors (S) σαν εξωτερική σχέση: • Κόστος σαρώματος της S είναι 500 Ι/Οs • Έχουμε 5 blocks • Για κάθε block σαρώνουμε τη Reserves = 5*1000 Ι/Οs • Σύνολο: 500 + 5000 = 5500 Ι/Οs. • Λίγο περισσότερο από ένα λεπτό!

30. Εμφώλευση βρόγχων και ευρετήρια • Αν υπάρχει ευρετήριο σε μία από τις σχέσεις στο γνώρισμα σύζευξης, τότε τη θέτουμε σαν την εσωτερική σχέση S. • Για κάθε πλειάδα r της R βρίσκουμε αυτές τις πλειάδες της S που ταιριάζουν με την r χρησιμοποιώντας το ευρετήριο. • Κόστος σάρωσης της R: Μ • Κόστος ανάκτησης πλειάδων της S: • B+ tree – 2-4 I/Os + κόστος ανάκτησης (συγκροτημένο ή όχι) • Hash index – 1-2 I/Os + κόστος ανάκτησης (συγκροτημένο ή όχι) • Συγκροτημένα ευρετήρια: συνήθως ένα Ι/Ο • Μη-συγκροτημένα ευρετήρια: ένα Ι/Ο για κάθε matched πλειάδα. • Συνολικό κόστος: Μ + ( (M*pR) * κόστος εύρεσης S πλειάδων)

31. Εμφώλευση βρόγχων και ευρετήρια • Παράδειγμα: • Ευρετήριο κατακερματισμού στο sid (Π.Κ.) της Sailors. • Έστω 1.2 Ι/Ο για την ανάκτηση της σελίδας ευρετηρίου. • Κόστος σάρωσης της Reserves: 1000 I/O. • pR =100 - 1000 σελίδες => 100000 πλειάδες στην Reserves. • Κόστος ανάκτησης για κάθε πλειάδα (1.2 + 1) Ι/Ο • Συνολικό κόστος: 220000 + 1000 = 221000 Ι/Οs.

32. Εμφώλευση βρόγχων και ευρετήρια • Παράδειγμα: • Ευρετήριο κατακερματισμού στο sid (Ξ.Κ.) της Reserves. • Έστω 1.2 Ι/Ο για την ανάκτηση της σελίδας ευρετηρίου. • Κόστος σάρωσης της Sailors: 500 I/O. • pR =80 - 500 σελίδες => 40000 πλειάδες στην Reserves. • Κόστος ανάκτησης ευρετηρίου: 1.2 * 40000 Ι/Ο = 48000 Ι/Ο • Κόστος ανάκτησης πλειάδων: • Ομοιόμορφη κατανομή, 2.5 κρατήσεις για κάθε ναύτη • 2.5 * 40000 = 100000 Ι/Ο • Συνολικό κόστος: 500 + 48000 + 100000 = 148500 Ι/Οs

33. Εξωτερική ταξινόμηση (External Sort) • Σε πολλές περιπτώσεις πρέπει να ταξινομηθεί μία σχέση. Πως επιτυγχάνεται? Αν χωράει στη μνήμη, τότε χρησιμοποιούμε κλασσικές μεθόδους ταξινόμησης. Αλλιώς? • Εξωτερική ταξινόμηση με συγχώνευση - Εxternal sort-merge. • Βασική ιδέα: Έστω ότι έχουμε Β σελίδες διαθέσιμη μνήμη. • Φέρε τις πρώτες Β σελίδες από τη σχέση στη μνήμη • Ταξινόμησέ τις πλειάδες αυτών των σελιδών (γίνεται στη μνήμη) • Γράψε το ταξινομημένο αποτέλεσμα στο δίσκο • Φέρε τις επόμενες Β σελίδες και επανέλαβε • Αφού τελειώσει η σχέση, συγχώνευσε τα ταξινομημένα αποτελέσματα • Κάθε block των Β σελίδων ονομάζεται run (1st, 2nd, …) ή συρμός.

34. Εξωτερική ταξινόμηση (External Sort) • Δημιούργησε ταξινομημένους συρμούς όπως προηγουμένως.Έστω ότι το i είναι αρχικά 0. Επαναληπτικά κάνε το κάτωθι μέχρι το τέλος της σχέσης: (a)Read Β blocks of relation into memory (b) Sort the in-memory blocks (c) Write sorted data to run Ri; increment i. • Συγχώνευσε τα runs; υπέθεσεότι i < Β. Σε ένα μόνο βήμα συγχώνευσης, χρησιμοποιήσε i blocks μνήμης to buffer input runs, και 1 block to buffer output. Κάνε το κάτωθι επαναληπτικά μέχρι όλες οι input buffer σελίδες έχουν αδειάσει: (a) Select the first record in sort order from each of the buffers (b) Write the record to the output (c) Delete the record from the buffer page; if the buffer page is empty, read the next block (if any) of the run into the buffer. Εάν i >= Β, απαιτούνται πολλά περάσματα συγχώνευσης. Σε κάθε πέρασμα, συγχωνεύουμε ομάδες των Β-1 runs. Συνεπώς, ένα πέρασμα μειώνει τον αριθμό των runs κατά ένα παράγοντα Β -1, μεγαλώνοντας το μέγεθός τους επίσης κατά Β-1. Εκτελούμε πολλαπλά περάσματα μέχρι όλοι οι συρμοί έχουν συγχωνευθεί σε ένα.

35. Εξωτερική ταξινόμηση – Παράδειγμα • Κόστος? • Τάξης: Ο(ΜlogΜ) (η σχέση έχει Μ σελίδες) • Γιατί?

36. Σύζευξη με ταξινόμηση & συγχώνευσηSort-Merge Join (R S) i=j • Ταξινόμησε τις σχέσεις R και S στο γνώρισμα σύζευξης και κατόπιν σάρωσε τις και «συγχώνευσε» τις (δεν είναι ακριβώς συγχώνευση αλλά «ταίριασμα») στο γνώρισμα αυτό. Σύζευξη στο γνώρισμα α1. Όταν οι τιμές στο α1 είναι ίσες, τότε η πλειάδα <pr,ps> προστίθεται στο αποτέλεσμα

37. Σύζευξη με ταξινόμηση & συγχώνευση • Βασική ιδέα: • Advance scan of R until current R-tuple >= current S tuple, then advance scan of S until current S-tuple >= current R tuple; do this until current R tuple = current S tuple. • At this point, all R tuples with same value in Ri (current R group) and all S tuples with same value in Sj (current S group) match; output <r, s> for all pairs of such tuples. • Then resume scanning R and S. • Η R σαρώνεται μία φορά. Κάθε S group σαρώνεται μία φορά για κάθε matching πλειάδα του R. Πολλαπλά σαρώματα ενός S group πιθανόν να υπάρχουν στη διαθέσιμη μνήμη.

38. Σύζευξη με ταξινόμηση & συγχώνευση

39. Παράδειγμα της Sort-Merge Join • Κόστος: • M log M + N log N (ταξινόμηση των σχέσεων R και S) • (M+N) – κόστος συγχώνευσης. Μπορεί να είναι όμως και Μ*Ν, αν και όχι πιθανό. Σε ποιές περιπτώσεις?

40. Παράδειγμα της Sort-Merge Join • Διαθέσιμη μνήμη: 300 σελίδες • Ταξινόμηση της Reserves = 2*2*1000 = 4000 I/Os • Ταξινόμηση της Sailors = 2*2*500 = 2000 Ι/Οs • Συνολικό κόστος: 4000+2000+1000+500 = 7500 Ι/Οs • Διαθέσιμη μνήμη: 100 σελίδες • Συνολικό κόστος: 4000+2000+1000+500 = 7500 Ι/Οs • Διαθέσιμη μνήμη: 35 σελίδες • Συνολικό κόστος: 4000+2000+1000+500 = 7500 Ι/Οs • Αντίστοιχα με block nested loop join τα κόστη είναι: 2500, 7500 και 15000 Ι/Οs.

41. Original Relation Partitions OUTPUT 1 1 2 INPUT 2 hash function h . . . B-1 B-1 B main memory buffers Disk Disk Partitions of R & S Join Result Hash table for partition Ri (k < B-1 pages) hash fn h2 h2 Output buffer Input buffer for Si B main memory buffers Disk Disk Κατακερματισμός(Hash-Join) • Διαμέρισε και τις δύο σχέσειςμε μία hash συναρτησηh: οι πλειάδες της R στη διαμέριση i θα ται-ριάζουν μόνο μεπλειάδες της S στη διαμέριση i. • Διάβασε μία διαμέριση της R καικατακερμάτισε την χρησιμοποιώντας μίαh2 (<> h!). Σάρωσε την αντίστοιχη διαμέριση της S και ψάξε για matches.

42. Παρατηρήσεις επί της Hash-Join • #partitions k < B-1 (γιατί?), καιB-2 > μέγεθος μεγαλύ-τερηςδιαμέρισηςπου θα είναι στη μνήμη. Για ισο-μεγεθείς διαμερίσεις καιμεγιστοποιώνταςτο k: • k= B-1, και M/(B-1) < B-2, δηλ.., B πρέπει να > • Εάν δημιουργήσουμε έναν πίνακα κατακερματισμού στη μνήμη για να επιταγχύνουμε το ταίριασμα των πλειάδων, απαιτείται λίγο παραπάνω μνήμη. • Εάν η hash function δε διαμερίζει ομοιόμορφα, μία ή περισσότερες διαμερίσεις της R δε θα είναι στη μνήμη. Εφαρμόζουμε hashing αναδρομικάγια τη σύζευξη αυτού του R-partition με το αντίστοιχο S-partition.

43. Κόστος της σύζευξης με κατακερματισμό • Στη φάση της διαμέρισης, read+write και για τις δύο σχέσεις =2(M+N). Στη matching φάση, read και τις δύο σχέσεις =M+N I/Os. • Στο παράδειγμά μας= 4500 I/Os. • Sort-Merge Join vs. Hash Join: • Δεδομένου ενός minimum μεγέθους μνήμης (what is this, for each?) και οι δύο έχουν κόστος3(M+N) I/Os. Η Hash Join είναι καλύτερη αν τα μεγέθη των σχέσεων διαφέρουν σημαντικά.Επίσης, η Hash Join παραλληλοποιείται. • Η Sort-Merge είναι λιγότερο ευπαθής στο data skew; Έπίσης το αποτέλεσμα είναι ταξινομημένο (σημαντικό αργότερα).

44. Γενικές συνθήκες σύζευξης • Ισότητες επί πολλών γνωρισμάτων (π.χ., R.sid=S.sid ANDR.rname=S.sname): • Για Indexed NLJ, δημιούργησε ευρετήριο επί <sid, sname> (εανη S είναι εσωτερική); Ή χρησιμοποίησε τουsidήsname. • Για τις Sort-Merge και Hash Join, ταξινόμησε/διαμέρισεστο συνδυασμό των δύο γνωρισμάτων. • Συνθήκες με ανισότητες (π.χ., R.rname < S.sname): • Για indexed NLJ, χρειάζεται–συγκροτημένο- B+ tree ευρετ. • Ο αριθμός των matches θα είναι μάλλον αρκετά μεγαλύτερος από όταν έχουμε ισότητα. • Hash Join, Sort Merge Join δεν μπορούν να εφαρμοστούν. • Η μέθοδος Block NL μάλλον η καλύτερη μέθοδος σύζευξης.

45. Συναθροίσεις (AVG, MIN, κλπ.) • Χωρίς grouping: • Στη γενική περ’ιπτωση, απιτεί σάρωμα του πίνακα. • Εάν υπάρχει ευρετήριο που να περιέχει όλα τα γνωρίσματα στο SELECTή τοWHERE, μπορεί να χρησιμοποιηθεί μόνο το ευρετήριο. • Με grouping: • Ταξινόμησε στα group-by γνωρίσματα, και κατόπιν σάρωσε τη σχέση και υπολόγισε τις συναρτήσεις συνάθροισης για κάθε group. (Κάποιος μπορεί να συνδυάσει την ταξινόμηση με τον υπολογισμό.) • Παρόμοια τεχνική βασισμένη στον κατακερματισμό των group-by γνωρισμάτων. • Έχοντας ένα δεντρικό ευρετήριο του οποίου το κλειδί αναζήτησης περιλαμβάνει όλα τα γνωρίσματαστα SELECT, WHERE καιGROUP BY, μπορούμα να σαρώσουμε μόνο το ευρετήριο.Εάν τα group-by πεδία είναι το prefix του κλειδιού αναζήτησης μπορούμε να πάρουμε τα αποτελέσματα σε group-by σειρά.

46. Βασικά βήματα στην επεξεργασία ερωτημάτων 1. Μετάφραση 2. Βελτιστοποίηση 3. Υπολογισμός

47. Βελτιστοποίηση σχεσιακού ερωτήματος • Η βελτιστοποίηση ενός ερωτήματος αποτελείται από: • Απαρίθμηση των εναλλακτικών σχεδίων υπολογισμού • Εκτίμηση (τι σημαίνει?) του κόστους για κάθε ένα από τα εναλλακτικά σχέδια χρησιμοποιώντας τους καταλόγους του συστήματος και επιλογή εκείνου με το μικρότερο κόστος. • Εξετάζεται ένα υποσύνολο μόνο των εναλλακτικών σχεδίων – συνήθως ο αριθμός είναι πολύ μεγάλος (γιατί?). • Ένας εμπορικός βελτιστοποιητής: 40-50 ανθρωποέτη • System R - IBM

48. Schema για τα παραδείγματα Sailors (sid: integer, sname: string, rating: integer, age: real) Reserves (sid: integer, bid: integer, day: dates, rname: string) • Reserves: • Κάθε πλειάδαείναι 40 bytes, 100 εγγραφές ανά σελίδα, 1000 σελίδες. • Sailors: • Κάθε πλειάδαείναι 50 bytes, 80 εγγραφές ανά σελίδα, 500 σελίδες.

49. sname rating > 5 bid=100 sid=sid Sailors Reserves Σχέδια υπολογισμού ερωτημάτων (plans) SELECT S.sname FROM Reserves R, Sailors S WHERE R.sid=S.sid AND R.bid=100 AND S.rating>5 • πS.name(σbid=100^rating>5(Reserves R.sid=S.sid Sailors)) Αλγεβρική παράσταση (RA tree)

50. (On-the-fly) sname (On-the-fly) rating > 5 bid=100 (Simple Nested Loops) sid=sid Sailors Reserves Σχέδια υπολογισμού ερωτημάτων (plans) • Σχέδιο υπολογισμού. • Κόστος: 500+500*1000 I/Os • Ένα πολύ κακό σχέδιο!!! • Ευκαιρίες: • Οι επιλογές θα μπορούσαν να είχαν «σπρωχτεί» να γίνουν νωρίτερα, δε χρησιμοποιούνται ευρετήρια, κλπ. • Βελτιστοποίηση: καλύτερα πλάνα για τον ίδιο υπολογισμό