644:
807:
133:
718:
691:
827:
758:
738:
664:
583:
563:
543:
428:
408:
388:
368:
340:
316:
292:
268:
244:
221:
197:
177:
153:
87:
67:
904:
545:
as can be fit in the available memory, loading all such tuples into a hash table, and then repeatedly scans
595:
763:
846:
47:
36:
872:
295:
92:
226:
The block nested loop join algorithm improves on the simple nested loop join by only scanning
319:
43:
696:
669:
343:
585:
that are necessary. In fact, this algorithm is essentially a special-case of the classic
812:
743:
723:
649:
568:
548:
528:
413:
393:
373:
353:
325:
301:
277:
253:
229:
206:
200:
182:
162:
138:
72:
52:
898:
271:
347:
32:
586:
28:
318:. For example, one variant of the block nested loop join reads an entire
525:
A more aggressive variant of this algorithm loads as many pages of
156:
89:(the "outer" and "inner" join operands, respectively). Suppose
760:
respectively in pages. Note that block nested loop runs in
390:
tuples that match any of the tuples in the current page of
847:"8.2.1.14 Block Nested-Loop and Batched Key Access Joins"
666:
is the number of available pages of internal memory and
815:
766:
746:
726:
699:
672:
652:
598:
571:
551:
531:
416:
396:
376:
356:
328:
304:
280:
256:
232:
209:
185:
165:
141:
95:
75:
55:
199:tuples, and particularly if there is no applicable
821:
801:
752:
732:
712:
685:
658:
638:
577:
557:
537:
422:
402:
382:
362:
334:
310:
286:
262:
238:
215:
191:
171:
147:
127:
81:
61:
565:. This further reduces the number of scans of
8:
42:This algorithm is a variation of the simple
814:
790:
777:
765:
745:
725:
704:
698:
677:
671:
651:
625:
619:
609:
597:
570:
550:
530:
415:
395:
375:
355:
327:
303:
279:
255:
231:
223:, this operation will be very expensive.
208:
184:
164:
140:
120:
112:
104:
96:
94:
74:
54:
838:
829:fits in the available internal memory.
410:. This reduces the number of scans of
298:of all groups has the same tuples as
135:. In a traditional nested loop join,
7:
370:, and probes the hash table to find
14:
639:{\displaystyle O(P_{r}P_{s}/M)}
179:. If there are many qualifying
155:will be scanned once for every
802:{\displaystyle O(P_{r}+P_{s})}
796:
770:
633:
602:
592:The block nested loop runs in
121:
113:
105:
97:
1:
506:satisfy the join condition
921:
851:MySQL 5.6 Reference Manual
128:{\displaystyle |R|<|S|}
879:. MariaDB Corporation Ab
873:"Block Nested Loop Join"
270:tuples. Here groups are
436:block_nested_loop_join
823:
803:
754:
734:
714:
687:
660:
640:
579:
559:
539:
424:
404:
384:
364:
346:and loads them into a
336:
312:
288:
264:
240:
217:
193:
173:
149:
129:
83:
63:
824:
804:
755:
735:
715:
713:{\displaystyle P_{s}}
688:
686:{\displaystyle P_{r}}
661:
641:
580:
560:
540:
425:
405:
385:
365:
337:
313:
289:
265:
241:
218:
194:
174:
150:
130:
84:
64:
853:. Oracle Corporation
813:
764:
744:
724:
697:
670:
650:
596:
569:
549:
529:
430:that are necessary.
414:
394:
374:
354:
326:
302:
278:
254:
230:
207:
203:for the join key on
183:
163:
139:
93:
73:
53:
37:relational database
35:two relations in a
819:
799:
750:
730:
710:
683:
656:
636:
575:
555:
535:
420:
400:
380:
360:
332:
308:
284:
260:
236:
213:
189:
169:
145:
125:
79:
59:
822:{\displaystyle R}
753:{\displaystyle S}
733:{\displaystyle R}
659:{\displaystyle M}
578:{\displaystyle S}
558:{\displaystyle S}
538:{\displaystyle R}
423:{\displaystyle S}
403:{\displaystyle R}
383:{\displaystyle S}
363:{\displaystyle S}
335:{\displaystyle R}
311:{\displaystyle R}
287:{\displaystyle R}
263:{\displaystyle R}
239:{\displaystyle S}
216:{\displaystyle S}
192:{\displaystyle R}
172:{\displaystyle R}
148:{\displaystyle S}
82:{\displaystyle S}
62:{\displaystyle R}
21:block-nested loop
912:
889:
888:
886:
884:
869:
863:
862:
860:
858:
843:
828:
826:
825:
820:
808:
806:
805:
800:
795:
794:
782:
781:
759:
757:
756:
751:
739:
737:
736:
731:
719:
717:
716:
711:
709:
708:
692:
690:
689:
684:
682:
681:
665:
663:
662:
657:
645:
643:
642:
637:
629:
624:
623:
614:
613:
584:
582:
581:
576:
564:
562:
561:
556:
544:
542:
541:
536:
429:
427:
426:
421:
409:
407:
406:
401:
389:
387:
386:
381:
369:
367:
366:
361:
350:. It then scans
341:
339:
338:
333:
317:
315:
314:
309:
293:
291:
290:
285:
269:
267:
266:
261:
245:
243:
242:
237:
222:
220:
219:
214:
198:
196:
195:
190:
178:
176:
175:
170:
154:
152:
151:
146:
134:
132:
131:
126:
124:
116:
108:
100:
88:
86:
85:
80:
68:
66:
65:
60:
44:nested loop join
920:
919:
915:
914:
913:
911:
910:
909:
905:Join algorithms
895:
894:
893:
892:
882:
880:
871:
870:
866:
856:
854:
845:
844:
840:
835:
811:
810:
786:
773:
762:
761:
742:
741:
722:
721:
700:
695:
694:
673:
668:
667:
648:
647:
615:
605:
594:
593:
567:
566:
547:
546:
527:
526:
524:
522:
412:
411:
392:
391:
372:
371:
352:
351:
324:
323:
300:
299:
276:
275:
252:
251:
246:once for every
228:
227:
205:
204:
181:
180:
161:
160:
137:
136:
91:
90:
71:
70:
51:
50:
17:
12:
11:
5:
918:
916:
908:
907:
897:
896:
891:
890:
864:
837:
836:
834:
831:
818:
798:
793:
789:
785:
780:
776:
772:
769:
749:
729:
707:
703:
680:
676:
655:
635:
632:
628:
622:
618:
612:
608:
604:
601:
574:
554:
534:
432:
419:
399:
379:
359:
331:
307:
283:
259:
235:
212:
188:
168:
144:
123:
119:
115:
111:
107:
103:
99:
78:
58:
46:and joins two
15:
13:
10:
9:
6:
4:
3:
2:
917:
906:
903:
902:
900:
878:
874:
868:
865:
852:
848:
842:
839:
832:
830:
816:
791:
787:
783:
778:
774:
767:
747:
727:
705:
701:
678:
674:
653:
630:
626:
620:
616:
610:
606:
599:
590:
588:
572:
552:
532:
520:
516:
512:
509:
505:
501:
498:
495:
492:
488:
484:
481:
478:
474:
470:
467:
464:
460:
456:
453:
450:
446:
442:
439:
435:
431:
417:
397:
377:
357:
349:
345:
329:
321:
305:
297:
281:
274:of tuples in
273:
272:disjoint sets
257:
249:
233:
224:
210:
202:
186:
166:
158:
142:
117:
109:
101:
76:
56:
49:
45:
40:
38:
34:
30:
26:
22:
881:. Retrieved
876:
867:
855:. Retrieved
850:
841:
591:
523:
518:
514:
510:
507:
503:
499:
496:
493:
490:
486:
482:
479:
476:
472:
468:
465:
462:
458:
454:
451:
448:
444:
440:
437:
433:
342:tuples into
247:
225:
41:
24:
20:
18:
720:is size of
646:I/Os where
589:algorithm.
833:References
513:tuple <
348:hash table
587:hash join
434:algorithm
48:relations
29:algorithm
16:Algorithm
899:Category
883:2 August
857:2 August
809:I/Os if
483:for each
469:for each
455:for each
441:for each
294:and the
31:used to
27:) is an
877:MariaDB
485:tuple
471:tuple
344:memory
521:>
511:yield
457:page
443:page
296:union
248:group
201:index
157:tuple
885:2015
859:2015
740:and
693:and
508:then
502:and
320:page
110:<
69:and
33:join
489:in
475:in
461:in
447:in
322:of
250:of
159:of
25:BNL
901::
875:.
849:.
497:if
494:do
491:ps
480:do
477:pr
466:do
459:ps
452:do
445:pr
438:is
39:.
19:A
887:.
861:.
817:R
797:)
792:s
788:P
784:+
779:r
775:P
771:(
768:O
748:S
728:R
706:s
702:P
679:r
675:P
654:M
634:)
631:M
627:/
621:s
617:P
611:r
607:P
603:(
600:O
573:S
553:S
533:R
519:s
517:,
515:r
504:s
500:r
487:s
473:r
463:S
449:R
418:S
398:R
378:S
358:S
330:R
306:R
282:R
258:R
234:S
211:S
187:R
167:R
143:S
122:|
118:S
114:|
106:|
102:R
98:|
77:S
57:R
23:(
Text is available under the Creative Commons Attribution-ShareAlike License. Additional terms may apply.