ããHash Joinåªè½ç¨äºç¸çè¿æ¥ï¼ä¸åªè½å¨CBOä¼åå¨æ¨¡å¼ä¸ãç¸å¯¹äºnested loop joinï¼hash joinæ´éåå¤ç大åç»æéHash Joinçæ§è¡è®¡å第1个æ¯hash表(build table)ï¼ç¬¬2个æ¢æ¥è¡¨(probe table)ï¼ä¸è¬ä¸å«å
å¤è¡¨ï¼nested loopææå
å¤è¡¨Hash表ä¹å°±æ¯æè°çå
表ï¼æ¢æ¥è¡¨æè°çå¤è¡¨
ãã两è
çæ§è¡è®¡åå½¢å¦ï¼
ããnested loop
ããouter table --驱å¨è¡¨
ããinner table
ããhash join
ããbuild table (inner table) --驱å¨è¡¨
ããprobe table (outer table)
ããå
çä¸å¼ å¾çï¼å¤§è´äºè§£Hash Joinçè¿ç¨ï¼
ããæ·±å
¥ç解Oracle表ï¼ä¸å¤§è¡¨è¿æ¥æ¹å¼è¯¦è§£ä¹Hash Joinçå®ä¹ï¼åçï¼ç®æ³ï¼ææ¬ï¼æ¨¡å¼åä½å¾ - Andy - Andys homeä¸é¢è¯¦ç»äºè§£ä¸ä¸Hash Join
ããä¸ Hash joinæ¦å¿µ
ããHash joinç®æ³çä¸ä¸ªåºæ¬ææ³å°±æ¯æ ¹æ®å°çrow sources(称ä½build input ä¹å°±æ¯åææå°çbuild tableï¼æ们记è¾å°ç表为Sï¼è¾å¤§ç表为B)建ç«ä¸ä¸ªå¯ä»¥åå¨äºhash areaå
åä¸çhash tableç¶åç¨å¤§çrow sources(称ä½probe inputï¼ä¹å°±æ¯åææå°çprobe table) æ¥æ¢æµåé¢æ建çhash tableå¦æhash areaå
åä¸å¤å¤§ï¼hash tableå°±æ æ³å®å
¨åæ¾å¨hash areaå
åä¸é对è¿ç§æ
åµï¼Oracleå¨è¿æ¥é®å©ç¨ä¸ä¸ªhashå½æ°å°build inputåprobe inputåå²æå¤ä¸ªä¸ç¸è¿çååºåå«è®°ä½SiåBiï¼è¿ä¸ªé¶æ®µå«åååºé¶æ®µï¼ç¶ååèªç¸åºçååºï¼å³SiåBiååHash joinï¼è¿ä¸ªé¶æ®µå«åjoiné¶æ®µå¦æHASH表太大ï¼æ æ³ä¸æ¬¡æé å¨å
åä¸ï¼ååæè¥å¹²ä¸ªpartitionï¼åå
¥ç£ççtemporary segmentï¼åä¼å¤ä¸ä¸ªåç代价ï¼ä¼éä½æçè³äºå°è¡¨çæ¦å¿µï¼å¯¹äº hash join æ¥è¯´ï¼è½å®¹çº³å¨ pga ä¸ç hash table é½å¯ä»¥å«å°è¡¨ï¼é常æ¯å¦ï¼
ããpga_aggregate_target big integer 1073741824hash area size 大ä½è½ä½¿ç¨å°40å¤ M ï¼è¿æ ·çè¯é常å¯è½å®¹çº³ å åä¸çè®°å½hash area size缺çæ¯2*sort_area_size,æ们å¯ä»¥ç´æ¥ä¿®æ¹SORT_AREA_SIZE ç大å°ï¼HASH_AREA_SIZEä¹ä¼è·çæ¹åçå¦æä½ çworkarea_size_policy=autoï¼é£ä¹æ们åªé设å®pga_aggregate_targetä½è¯·è®°ä½ï¼è¿æ¯ä¸ä¸ªsession级å«çåæ°ï¼ææ¶ï¼æ们æ´å¾åäºæhash_area_sizeç大å°è®¾æ驱å¨è¡¨ç1.6åå·¦å³é©±å¨è¡¨ä»
ä»
ç¨äºnested loop join å hash joinï¼ä½Hash joinä¸éè¦å¨é©±å¨è¡¨ä¸åå¨ç´¢å¼ï¼ènested loop joinåè¿«åéæ±ä¸ä¸¤ç¾ä¸è®°å½ç表 joinä¸ åä¸è®°å½ç表ï¼hash joinçé常表ç°é常好ä¸è¿ï¼å¤ä¸å°ï¼å¤§ä¸å°ï¼å¾å¤æ¶åå¾é¾éåï¼å
·ä½æ
åµè¿å¾å
·ä½åæå¦æå¨ååºåï¼é对æ个ååºæ建çhash tableè¿æ¯å¤ªå¤§çè¯ï¼oracleå°±éç¨nested loop hash joinæè°çnested-loops hash joinå°±æ¯å¯¹é¨åSi建ç«hash tableï¼ç¶å读åææçBiä¸æ建çhash tableåè¿æ¥ç¶åå对å©ä½çSi建ç«hash tableï¼åå°ææçBiä¸æ建çhash tableåè¿æ¥ï¼ç´è³ææçSié½è¿æ¥å®äºäº Hash Joinåç
ããèè以ä¸ä¸¤ä¸ªæ°æ®éï¼
ããS={1,1,1,3,3,4,4,4,4,5,8,8,8,8,10}
ããB={0,0,1,1,1,1,2,2,2,2,2,2,3,8,9,9,9,10,10,11}
ããHash Joinç第ä¸æ¥å°±æ¯å¤å®å°è¡¨ï¼å³build inputï¼æ¯å¦è½å®å
¨åæ¾å¨hash areaå
åä¸å¦æè½å®å
¨åæ¾å¨å
åä¸ï¼åå¨å
åä¸å»ºç«hash tableï¼è¿æ¯æç®åçhash joinå¦æä¸è½å
¨é¨åæ¾å¨å
åä¸ï¼åbuild inputå¿
é¡»ååºãååºç个æ°å«åfan-outFan-outæ¯ç±hash_area_sizeåcluster sizeæ¥å³å®çãå
¶ä¸cluster sizeçäºdb_block_size * _hash_multiblock_io_counthash_multiblock_io_countæ¯ä¸ªéèåæ°,å¨9.0.1以åå°±ä¸å使ç¨äº[sql]
ããsys@ORCL> ed
ããWrote file afiedt.buf
ãã1 select a.ksppinm name,b.ksppstvl value,a.ksppdesc description2 from x$ksppi a,x$ksppcv b
ãã3 where a.indx = b.indx
ãã4* and a.ksppinm like '%hash_multiblock_io_count%'
ããsys@ORCL> /
ããNAME VALUE DESCRIPTION
ãã------------------------------ ----- ------------------------------------------------------------_hash_multiblock_io_count 0 number of blocks hash join will read/write at onceOracleéç¨å
é¨ä¸ä¸ªhashå½æ°ä½ç¨äºè¿æ¥é®ä¸ï¼å°SåBåå²æå¤ä¸ªååºå¨è¿éæ们å设è¿ä¸ªhashå½æ°ä¸ºæ±ä½å½æ°ï¼å³Mod(join_column_value,10)è¿æ ·äº§çå个ååºï¼å¦ä¸è¡¨:
ããæ·±å
¥ç解Oracle表ï¼ä¸å¤§è¡¨è¿æ¥æ¹å¼è¯¦è§£ä¹Hash Joinçå®ä¹ï¼åçï¼ç®æ³ï¼ææ¬ï¼æ¨¡å¼åä½å¾ - Andy - Andys homeç»è¿è¿æ ·çååºä¹åï¼åªéè¦ç¸åºçååºä¹é´åjoinå³å¯ï¼ä¹å°±æ¯æè°çpartition pairsï¼å¦ææä¸ä¸ªååºä¸ºNULLçè¯ï¼åç¸åºçååºjoinå³å¯å¿½ç¥å¨å°S表读å
¥å
åååºæ¶ï¼oracleå³è®°å½è¿æ¥é®çå¯ä¸å¼ï¼æ建ææè°çä½å¾åéå®éè¦å hash areaå
åç5%å·¦å³ãå¨è¿éå³ä¸º{1,3,4,5,8,10}
ããå½å¯¹B表è¿è¡ååºæ¶ï¼å°æ¯ä¸ä¸ªè¿æ¥é®ä¸çå¼ä¸ä½å¾åéç¸æ¯è¾ï¼å¦æä¸å¨å
¶ä¸ï¼åå°å
¶è®°å½ä¸¢å¼å¨æ们è¿ä¸ªä¾åä¸ï¼B表ä¸ä»¥ä¸æ°æ®å°è¢«ä¸¢å¼{0,0,2,2,2,2,2,2,9,9,9,9,9}
ããè¿ä¸ªè¿ç¨å°±æ¯ä½å¾åéè¿æ»¤
ããå½S1,B1åå®è¿æ¥åï¼æ¥ç对Si,Biè¿è¡è¿æ¥
ããè¿éoracleå°æ¯è¾ä¸¤ä¸ªååºï¼éåå°çé£ä¸ªåbuild inputï¼å°±æ¯å¨æè§è²äºæ¢è¿ä¸ªå¨æè§è²äºæ¢åçå¨é¤ç¬¬ä¸å¯¹ååºä»¥å¤çååºä¸é¢ä¸ Hash Joinç®æ³
ãã第1æ¥ï¼å¤å®å°è¡¨æ¯å¦è½å¤å
¨é¨åæ¾å¨hash areaå
åä¸ï¼å¦æå¯ä»¥ï¼ååå
åhash joinãå¦æä¸è¡ï¼è½¬ç¬¬äºæ¥ç¬¬2æ¥ï¼å³å®fan-outæ°
ãã(Number of Partitions) * C<= Favm *M
ããå
¶ä¸C为Cluster sizeï¼å
¶å¼ä¸ºDB_BLOCK_SIZE*HASH_MULTIBLOCK_IO_COUNTFavm为hash areaå
åå¯ä»¥ä½¿ç¨çç¾åæ¯ï¼ä¸è¬ä¸º0.8å·¦å³M为Hash_area_sizeç大å°
ãã第3æ¥ï¼è¯»åé¨åå°è¡¨Sï¼éç¨å
é¨hashå½æ°(è¿é称为hash_fun_1)å°è¿æ¥é®å¼æ å°è³æ个ååºï¼åæ¶éç¨hash_fun_2å½æ°å¯¹è¿æ¥é®å¼äº§çå¦å¤ä¸ä¸ªhashå¼è¿ä¸ªhashå¼ç¨äºå建hash tableç¨ï¼å¹¶ä¸ä¸è¿æ¥é®å¼åæ¾å¨ä¸èµ·ç¬¬4æ¥ï¼å¯¹build input建ç«ä½å¾åé
ãã第5æ¥ï¼å¦æå
åä¸æ²¡æ空é´äºï¼åå°ååºåè³ç£çä¸ç¬¬6æ¥ï¼è¯»åå°è¡¨Sçå©ä½é¨åï¼éå¤ç¬¬ä¸æ¥ï¼ç´è³å°è¡¨Så
¨é¨è¯»å®ç¬¬7æ¥ï¼å°ååºæ大å°æåºï¼éåå 个ååºå»ºç«hash table(è¿ééåååºçååæ¯ä½¿éåçæ°éæå¤)第8æ¥ï¼æ ¹æ®åé¢ç¨hash_fun_2å½æ°è®¡ç®å¥½çhashå¼ï¼å»ºç«hash table第9æ¥ï¼è¯»å表Bï¼éç¨ä½å¾åéè¿è¡ä½å¾åéè¿æ»¤ç¬¬10æ¥ï¼å¯¹éè¿è¿æ»¤çæ°æ®éç¨hash_fun_1å½æ°å°æ°æ®æ å°å°ç¸åºçååºä¸å»ï¼å¹¶è®¡ç®hash_fun_2çhashå¼ç¬¬11æ¥ï¼å¦ææè½çååºå¨å
åä¸ï¼åå°åé¢éè¿hash_fun_2å½æ°è®¡ç®æå¾çhashå¼ä¸å
åä¸å·²åå¨çhash tableåè¿æ¥å°ç»æåè´ç£çä¸ãå¦ææè½çååºä¸å¨å
åä¸ï¼åå°ç¸åºçå¼ä¸è¡¨Sç¸åºçååºæ¾å¨ä¸èµ·ç¬¬12æ¥ï¼ç»§ç»è¯»å表Bï¼éå¤ç¬¬9æ¥ï¼ç´è³è¡¨B读åå®æ¯ç¬¬13æ¥ï¼è¯»åç¸åºç(Si,Bi)åhashè¿æ¥ãå¨è¿éä¼åçå¨æè§è²äºæ¢ç¬¬14æ¥ï¼å¦æååºè¿åï¼æå°çååºä¹æ¯å
å大ï¼ååçnested-loop hash joinå Hash Joinçææ¬
ããâ´ In-Memory Hash Join
ããCost(HJ)=Read(S)+ build hash table in memory(CPU)+Read(B) + Perform In memory Join(CPU)忽ç¥cpuçæ¶é´ï¼åï¼
ããCost(HJ)=Read(S)+Read(B)
ããâµ On-Disk Hash Join
ããæ ¹æ®ä¸è¿°çæ¥éª¤æè¿°ï¼æ们å¯ä»¥çåºï¼
ããCost(HJ)=Cost(HJ1)+Cost(HJ2)
ããå
¶ä¸Cost(HJ1)çææ¬å°±æ¯æ«æS,B表ï¼å¹¶å°æ æ³æ¾å¨å
åä¸çé¨åååç£çï¼å¯¹åºåé¢ç¬¬2æ¥è³ç¬¬12æ¥Cost(HJ2)å³ä¸ºånested-loop hash joinçææ¬ï¼å¯¹åºåé¢ç第13æ¥è³ç¬¬14æ¥å
¶ä¸Cost(HJ1)è¿ä¼¼çäºRead(S)+Read(B)+Write((S-M)+(B-B*M/S))å 为å¨ånested-loop hash joinæ¶ï¼å¯¹æ¯ä¸chunkçbuild inputï¼é½éè¦è¯»åæ´ä¸ªprobe inputï¼å æ¤Cost(HJ2)è¿ä¼¼çäºRead((S-M)+n*(B-B*M/S))ï¼å
¶ä¸næ¯nested-loop hash joinéè¦å¾ªç¯ç次æ°ï¼n=(S/F)/Mä¸è¬æ
åµä¸ï¼å¦æn大äº10çè¯ï¼hash joinçæ§è½å°å¤§å¤§ä¸éä»nç计ç®å
¬å¼å¯ä»¥çåºï¼nä¸Fan-outæåæ¯ä¾ï¼æé«fan-outï¼å¯ä»¥éä½nå½hash_area_sizeæ¯åºå®æ¶ï¼å¯ä»¥éä½cluster sizeæ¥æé«fan-outä»è¿éæ们å¯ä»¥çåºï¼æé«hash_multiblock_io_countåæ°çå¼å¹¶ä¸ä¸å®æé«hash joinçæ§è½äº Hash Joinçè¿ç¨
ããä¸æ¬¡å®æ´çhash joinå¦ä¸ï¼
ãã1 计ç®å°è¡¨çååºï¼bucketï¼æ°--Hashå桶
ããå³å®hash joinçä¸ä¸ªéè¦å ç´ æ¯å°è¡¨çååºï¼bucketï¼æ°è¿ä¸ªæ°åç±hash_area_sizeãhash_multiblock_io_countådb_block_sizeåæ°å
±åå³å®Oracleä¼ä¿çhash areaç20%æ¥åå¨ååºç头信æ¯ãhashä½å¾ä¿¡æ¯åhash表å æ¤ï¼è¿ä¸ªæ°åç计ç®å
¬å¼æ¯ï¼
ããBucketæ°=0.8*hash_area_size/(hash_multiblock_io_count*db_block_size)2 Hash计ç®
ãã读åå°è¡¨æ°æ®ï¼ç®ç§°ä¸ºRï¼ï¼å¹¶å¯¹æ¯ä¸æ¡æ°æ®æ ¹æ®hashç®æ³è¿è¡è®¡ç®Oracleéç¨ä¸¤ç§hashç®æ³è¿è¡è®¡ç®ï¼è®¡ç®åºè½è¾¾å°æå¿«é度çhashå¼ï¼ç¬¬ä¸hashå¼å第äºhashå¼ï¼èå
³äºè¿äºååºçå
¨é¨hashå¼ï¼ç¬¬ä¸hashå¼ï¼å°±æ为hash表3 åæ¾æ°æ®å°hashå
åä¸
ããå°ç»è¿hashç®æ³è®¡ç®çæ°æ®ï¼æ ¹æ®å个bucketçhashå¼ï¼ç¬¬ä¸hashå¼ï¼åå«æ¾å
¥ç¸åºçbucketä¸ç¬¬äºhashå¼å°±åæ¾å¨åæ¡è®°å½ä¸
ãã4 å建hashä½å¾
ããä¸æ¤åæ¶ï¼ä¹å建äºä¸ä¸ªå
³äºè¿ä¸¤ä¸ªhashå¼æ å°å
³ç³»çhashä½å¾5 è¶
åºå
å大å°é¨å被移å°ç£ç
ããå¦æhash area被å 满ï¼é£æ大ä¸ä¸ªååºå°±ä¼è¢«åå°ç£çï¼ä¸´æ¶è¡¨ç©ºé´ï¼ä¸å»ä»»ä½éè¦åå
¥å°ç£çååºä¸çè®°å½é½ä¼å¯¼è´ç£çååºè¢«æ´æ°è¿æ ·çè¯ï¼å°±ä¼ä¸¥éå½±åæ§è½ï¼å æ¤ä¸å®è¦å°½éé¿å
è¿ç§æ
åµ2-5ä¸ç´æç»å°æ´ä¸ªè¡¨çæ°æ®è¯»åå®æ¯
ãã6 对ååºæåº
ãã为äºè½å
åå©ç¨å
åï¼å°½éåå¨æ´å¤çååºï¼Oracleä¼æç
§å个ååºç大å°å°ä»ä»¬å¨å
åä¸æåº7 读å大表æ°æ®ï¼è¿è¡hashå¹é
ããæ¥ä¸æ¥å°±å¼å§è¯»å大表ï¼ç®ç§°Sï¼ä¸çæ°æ®
ããæ顺åºæ¯è¯»åä¸æ¡è®°å½ï¼è®¡ç®å®çhashå¼ï¼å¹¶æ£æ¥æ¯å¦ä¸å
åä¸çååºçhashå¼ä¸è´å¦ææ¯ï¼è¿åjoinæ°æ®
ããå¦æå
åä¸çååºæ²¡æ符åçï¼å°±å°Sä¸çæ°æ®åå
¥å°ä¸ä¸ªæ°çååºä¸ï¼è¿ä¸ªååºä¹éç¨ä¸è®¡ç®Rä¸æ ·çç®æ³è®¡ç®åºhashå¼ä¹å°±æ¯è¯´è¿äºSä¸çæ°æ®äº§ççæ°çååºæ°åºè¯¥åRçååºéçååºæ°ä¸æ ·ãè¿äºæ°çååºè¢«åå¨å¨ç£çï¼ä¸´æ¶è¡¨ç©ºé´ï¼ä¸8 å®å
¨å¤§è¡¨å
¨é¨æ°æ®ç读å
ããä¸ç´æç
§7è¿è¡ï¼ç´å°å¤§è¡¨ä¸çæææ°æ®ç读åå®æ¯9 å¤ç没æjoinçæ°æ®
ããè¿ä¸ªæ¶å就产çäºä¸å¤§å join好çæ°æ®åä»RåSä¸è®¡ç®åå¨å¨ç£çä¸çååº10 äºæ¬¡hash计ç®
ããä»RåSçååºéä¸æ½ååºæå°çä¸ä¸ªååºï¼ä½¿ç¨ç¬¬äºç§hashå½æ°è®¡ç®åºå¹¶å¨å
åä¸å建hash表éç¨ç¬¬äºç§hashå½æ°çåå æ¯ä¸ºäºä½¿æ°æ®åå¸æ§æ´å¥½11 äºæ¬¡hashå¹é
ããå¨ä»å¦ä¸ä¸ªæ°æ®æºï¼ä¸hashå¨å
åçé£ä¸ªååºæå±æ°æ®æºä¸åçï¼ä¸è¯»åååºæ°æ®ï¼ä¸å
åä¸çæ°hash表è¿è¡å¹é
ãè¿åjoinæ°æ®12 å®æå
¨é¨hash join
ãã继ç»æç
§9-11å¤çå©ä½ååºï¼ç´å°å
¨é¨å¤çå®æ¯å
Hash Joinç模å¼
ããOracleä¸ï¼Hash Joinä¹æä¸ç§æ¨¡å¼ï¼optimalï¼one-passï¼multi-passâ´ optimal
ããå½é©±å¨ç»æéçæçhash表å
¨é¨å¯ä»¥æ¾å
¥PGAçhash areaæ¶ï¼ç§°ä¸ºoptimalï¼å¤§è´è¿ç¨å¦ä¸ï¼
ããâ å
æ ¹æ®é©±å¨è¡¨ï¼å¾å°é©±å¨ç»æé
ããâ¡ å¨hash areaçæhash bulketï¼å¹¶å°è¥å¹²bulketåæä¸ç»ï¼æ为ä¸ä¸ªpartitionï¼è¿ä¼çæä¸ä¸ªbitmapçå表ï¼æ¯ä¸ªbulketå¨ä¸é¢å ä¸ä½â¢ 对ç»æéçjoiné®åhashè¿ç®ï¼å°æ°æ®åæ£å°ç¸åºpartitionçbulketä¸å½è¿ç®å®æåï¼å¦æé®å¼å¯ä¸æ§è¾é«çè¯ï¼bulketéçæ°æ®ä¼æ¯è¾ååï¼ä¹æå¯è½æç桶éé¢æ°æ®ä¼æ¯ç©ºçè¿æ ·bitmapä¸å¯¹åºçæ å¿ä½å°±æ¯0ï¼ææ°æ®ç桶ï¼æ å¿ä½ä¼æ¯1⣠å¼å§æ«æ第äºå¼ 表ï¼å¯¹jioné®åhashè¿ç®ï¼ç¡®å®åºè¯¥å°æ个partitionçæ个bulketå»æ¢æµæ¢æµä¹åï¼ä¼çè¿ä¸ªbulketçbitmapæ¯å¦ä¼1ï¼å¦æ为0ï¼è¡¨ç¤ºæ²¡æ°æ®ï¼è¿è¡å°±ç´æ¥ä¸¢å¼æ⤠å¦æbitmap为1ï¼åå¨æ¡¶å
å精确å¹é
ï¼å¤æOKåï¼è¿åæ°æ®è¿ä¸ªæ¯æä¼çhash joinï¼ä»çææ¬åºæ¬æ¯ä¸¤å¼ 表çfull table scanï¼å¨å å¾®éçhashè¿ç®å客å¼ç¯çé£å¹
å¾æè¿°çä¹å°±æ¯è¿ç§æ
åµ
ããâµ one-pass
ããå¦æè¿ç¨çpgaå¾å°ï¼æè
驱å¨è¡¨ç»æéå¾å¤§ï¼è¶
è¿äºhash areaç大å°ï¼ä¼æä¹åï¼
ããå½ç¶ä¼ç¨å°ä¸´æ¶è¡¨ç©ºé´ï¼æ¤æ¶oracleçå¤çæ¹å¼ç¨å¾®å¤æç¹é奥注æä¸é¢æå°çæ个partitionçæ¦å¿µå¯ä»¥è¿ä¹ç解ï¼æ°æ®æ¯ç»è¿ä¸¤æ¬¡hashè¿ç®çï¼å
ç¡®å®ä½ çpartitionï¼åç¡®å®ä½ çbulketå设hash areaå°äºæ´ä¸ªhash tableï¼ä½è³å°å¤§äºä¸ä¸ªpartitionçsizeï¼è¿ä¸ªæ¶åèµ°çå°±æ¯one-passå½æ们çæ好hash表åï¼ç¶åµæ¯é¨åpartitionçå¨å
åä¸ï¼å
¶ä»çpartitionçå¨ç£ç临æ¶è¡¨ç©ºé´ä¸å½ç¶ä¹æå¯è½æ个partitionä¸åå¨å
åï¼ä¸åå¨ç£çï¼å©ä¸çæ¥éª¤å¤§è´å¦ä¸ï¼
ããâ æ«æ第äºå¼ 表ï¼å¯¹joiné®åhashè¿ç®ï¼ç¡®å®å¥½å¯¹åºçpartitionåbulketâ¡ æ¥çbitmapï¼ç¡®å®bulketæ¯å¦ææ°æ®ï¼æ²¡æåç´æ¥ä¸¢å¼â¢ å¦æææ°æ®ï¼å¹¶ä¸è¿ä¸ªpartitionæ¯å¨å
åä¸çï¼å°±è¿å
¥å¯¹åºç桶å»ç²¾ç¡®å¹é
ï¼è½å¹é
ä¸ï¼å°±è¿åè¿è¡æ°æ®ï¼å¦å丢å¼â£ å¦æpartitionæ¯å¨ç£çä¸çï¼åå°è¿è¡æ°æ®æ¾å
¥ç£çä¸æåèµ·æ¥ï¼ä¿åçå½¢å¼ä¹æ¯partitionï¼bulketçæ¹å¼â¤ å½ç¬¬äºå¼ 表被æ«æå®åï¼å©ä¸çæ¯é©±å¨è¡¨åæ¢æµè¡¨çæçä¸å¤§å partitionï¼ä¿çå¨ç£çä¸â¥ ç±äºä¸¤è¾¹çæ°æ®é½æç
§ç¸åçhashç®æ³åäºpartitionåbulketï¼ç°å¨åªè¦æ对çæ¯è¾ä¸¤è¾¹partitionæ°æ®å³å¯å¹¶ä¸å¨æ¯è¾çæ¶åï¼oracleä¹åäºä¼åå¤çï¼æ²¡æä¸¥æ ¼ç驱å¨ä¸è¢«é©±å¨å
³ç³»ä»ä¼å¨partition对ä¸éè¾å°çä¸ä¸ªä½ä¸ºé©±å¨æ¥è¿è¡ï¼ç´å°ç£çä¸ææçpartition对é½joinå®å¯ä»¥åç°ï¼ç¸æ¯optimalï¼ä»å¤åºçææ¬æ¯å¯¹äºæ æ³æ¾å
¥å
åçpartitionï¼éæ°è¯»åäºä¸æ¬¡ï¼æ以称为one-passåªè¦ä½ çå
åä¿è¯è½è£
ä¸ä¸ä¸ªpartitionï¼oracleé½ä¼è
¾æªç©ºé´ï¼æ¯ä¸ªç£çpartitionåå°one-passⶠmulti-pass
ããè¿æ¯æå¤æï¼æç³ç³çhash join
ããæ¤æ¶hash areaå°å°è¿ä¸ä¸ªpartitionä¹å®¹çº³ä¸ä¸ï¼å½æ«æ好驱å¨è¡¨åå¯è½åªæå个partitionçå¨hash areaä¸ï¼å¦å个å å
¶ä»çpartitionå
¨å¨ç£çä¸å©ä¸çæ¥éª¤åone-passæ¯ä»·ç±»ä¼¼ï¼ä¸åçæ¯é对partitionçå¤çç±äºé©±å¨è¡¨åªæå个partitionå¨å
åä¸ï¼æ¢æµè¡¨å¯¹åºçpartitionæ°æ®åæ¢æµæ¶å¦æå¹é
ä¸ä¸ï¼è¿è¡è¿ä¸è½ç´æ¥ä¸¢å¼ï¼éè¦ç»§ç»ä¿çå°ç£çï¼å驱å¨è¡¨å©ä¸çå个partitionååjoinè¿é举ä¾çæ¯å
åå¯ä»¥è£
ä¸å个partitionï¼å¦æè£
çæ´å°çè¯ï¼åå¤joinç次æ°å°æ´å¤å½åçmulti-passæ¶ï¼partitionç©ç读ç次æ°ä¼æ¾èå¢å ä¸ Hash Joinçä½å¾
ããè¿ä¸ªä½å¾å
å«äºæ¯ä¸ªhashååºæ¯å¦ææå¼çä¿¡æ¯ãå®è®°å½äºææ°æ®çååºçhashå¼è¿ä¸ªä½å¾çæ大ä½ç¨å°±æ¯ï¼å¦æprobe inputä¸çæ°æ®æ²¡æä¸å
åä¸çhash表å¹é
ä¸å
æ¥çè¿ä¸ªä½å¾ï¼ä»¥å³å®æ¯å¦å°æ²¡æå¹é
çæ°æ®åå
¥ç£çé£äºä¸å¯è½å¹é
å°çæ°æ®ï¼å³ä½å¾ä¸å¯¹åºçååºæ²¡ææ°æ®ï¼å°±ä¸ååå
¥ç£çå
« å°ç»
ããâ 确认å°è¡¨æ¯é©±å¨è¡¨
ãã⡠确认æ¶åå°ç表åè¿æ¥é®åæè¿äº
ãã⢠å¦æå¨è¿æ¥é®ä¸æ°æ®ä¸ååçè¯ï¼å»ºè®®åæ±ç¶å¾â£ å¦æå¯ä»¥ï¼è°å¤§hash_area_sizeç大å°æpga_aggregate_targetçå¼â¤ Hash Joinéåäºå°è¡¨ä¸å¤§è¡¨è¿æ¥ãè¿å大åç»æéçè¿æ¥
温馨提示:答案为网友推荐,仅供参考