{"id":2268,"date":"2021-02-28T22:12:15","date_gmt":"2021-03-01T01:12:15","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=2268"},"modified":"2021-09-02T22:40:36","modified_gmt":"2021-09-03T01:40:36","slug":"maximizando-a-eficiencia-do-hash-join","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/maximizando-a-eficiencia-do-hash-join\/","title":{"rendered":"Maximizando a efici\u00eancia do Hash Join"},"content":{"rendered":"<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Analisando um plano de execu\u00e7\u00e3o eventualmente podemos chegar a conclus\u00e3o que o m\u00e9todo de jun\u00e7\u00e3o mais eficiente para duas tabelas neste plano de execu\u00e7\u00e3o seja o HASH JOIN, nesta opera\u00e7\u00e3o podemos utilizar alguns recursos do Oracle para deixa-la mais eficiente, porem alguns desses recursos dependem do tipo de licenciamento do banco, podemos usar paralelismo, particionamento de tabelas, Exadata ou Serial Bloom Filter. Neste artigo vamos mostrar um exemplo de como utilizar o Serial Bloom Filter para deixar seu plano de execu\u00e7\u00e3o que utiliza a opera\u00e7\u00e3o Hash JOIN mais eficiente.<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">O banco de dados Oracle usa a t\u00e9cnica Bloom Filter nas seguintes situa\u00e7\u00f5es:<\/span><\/p>\n<ul style=\"text-align: justify;\">\n<li><span style=\"font-size: 12pt;\">Para reduzir a transfer\u00eancia de dados entre &#8220;Slave processes&#8221; em Jun\u00e7\u00f5es paralelas<\/span><\/li>\n<li><span style=\"font-size: 12pt;\">Para implementar Join-Filter Pruning<\/span><\/li>\n<li><span style=\"font-size: 12pt;\">Para suportar Result Caches<\/span><\/li>\n<li><span style=\"font-size: 12pt;\">Para reduzir a transfer\u00eancia de dados entre os Storage Disks e as inst\u00e2ncias do banco no Exadata<\/span><\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">A utiliza\u00e7\u00e3o do Bloom Filter em Jun\u00e7\u00f5es paralelas foi implementada na vers\u00e3o 10gR2 e as demais foram implementadas a partir da vers\u00e3o 11gR1.<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Na demonstra\u00e7\u00e3o pr\u00e1tica vamos verificar como o uso do Bloom Filter poder maximizar a efici\u00eancia da opera\u00e7\u00e3o Hash Join, para isso vamos executar 3 etapas:<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">1) Criar duas tabelas para a demonstra\u00e7\u00e3o pr\u00e1tica<\/span><br \/>\n<span style=\"font-size: 12pt;\">2) Executar uma consulta com Hash Join nas duas tabelas <\/span><br \/>\n<span style=\"font-size: 12pt;\">3) Executar uma consulta com Hash Join nas duas tabelas usando Bloom Filter<\/span><\/p>\n<h2><span style=\"font-size: 14pt;\">1) Criar duas tabelas para a demonstra\u00e7\u00e3o pr\u00e1tica<\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Vamos criar as duas tabelas necess\u00e1rias para realizar a nossa demonstra\u00e7\u00e3o.<\/span><\/p>\n<p>&nbsp;<\/p>\n<div class=\"codecolorer-container php default codecolorer-noborder\" style=\"overflow:auto;white-space:nowrap;width:100%;height:520px;\"><table cellspacing=\"0\" cellpadding=\"0\"><tbody><tr><td class=\"line-numbers\"><div>1<br \/>2<br \/>3<br \/>4<br \/>5<br \/>6<br \/>7<br \/>8<br \/>9<br \/>10<br \/>11<br \/>12<br \/>13<br \/>14<br \/>15<br \/>16<br \/>17<br \/>18<br \/>19<br \/>20<br \/>21<br \/>22<br \/>23<br \/>24<br \/>25<br \/>26<br \/>27<br \/>28<br \/>29<br \/>30<br \/>31<br \/>32<br \/>33<br \/>34<br \/>35<br \/>36<br \/>37<br \/>38<br \/>39<br \/>40<br \/>41<br \/><\/div><\/td><td><div class=\"php codecolorer\">SQL <span class=\"sy0\">&gt;<\/span> SELECT BANNER_LEGACY FROM V<span class=\"re0\">$VERSION<\/span> where rownum <span class=\"sy0\">&lt;<\/span> <span class=\"nu0\">2<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nBANNER_LEGACY<br \/>\n<span class=\"sy0\">--------------------------------------------------------------------------------<\/span><br \/>\nOracle Database 18c Express Edition Release 18<span class=\"sy0\">.<\/span>0<span class=\"sy0\">.<\/span>0<span class=\"sy0\">.<\/span>0<span class=\"sy0\">.<\/span>0 <span class=\"sy0\">-<\/span> Production<br \/>\n<br \/>\n<span class=\"nu0\">1<\/span> linha selecionada<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span> CREATE TABLE dbtw068_1<br \/>\n&nbsp; <span class=\"nu0\">2<\/span> &nbsp;<span class=\"kw1\">AS<\/span><br \/>\n&nbsp; <span class=\"nu0\">3<\/span> &nbsp;WITH tabpad <span class=\"kw1\">AS<\/span> <span class=\"br0\">&#40;<\/span><br \/>\n&nbsp; <span class=\"nu0\">4<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT rownum id<br \/>\n&nbsp; <span class=\"nu0\">5<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM dual<br \/>\n&nbsp; <span class=\"nu0\">6<\/span> &nbsp; &nbsp; &nbsp; &nbsp; CONNECT BY level <span class=\"sy0\">&lt;=<\/span> <span class=\"nu19\">1e4<\/span> <span class=\"br0\">&#41;<\/span><br \/>\n&nbsp; <span class=\"nu0\">7<\/span> &nbsp;SELECT rownum &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;id<span class=\"sy0\">,<\/span><br \/>\n&nbsp; <span class=\"nu0\">8<\/span> &nbsp; &nbsp; &nbsp; &nbsp; LPAD<span class=\"br0\">&#40;<\/span>rownum<span class=\"sy0\">,<\/span><span class=\"nu0\">30<\/span><span class=\"sy0\">,<\/span><span class=\"st_h\">'0'<\/span><span class=\"br0\">&#41;<\/span> vseq<br \/>\n&nbsp; <span class=\"nu0\">9<\/span> &nbsp; &nbsp;FROM tabpad &nbsp; &nbsp; &nbsp; t1<span class=\"sy0\">,<\/span><br \/>\n&nbsp;<span class=\"nu0\">10<\/span> &nbsp; &nbsp; &nbsp; &nbsp; tabpad &nbsp; &nbsp; &nbsp; t2<br \/>\n&nbsp;<span class=\"nu0\">11<\/span> &nbsp; WHERE rownum <span class=\"sy0\">&lt;=<\/span> <span class=\"nu19\">1e7<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nTabela criada<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span> CREATE TABLE dbtw068_2<br \/>\n&nbsp; <span class=\"nu0\">2<\/span> &nbsp;<span class=\"kw1\">AS<\/span><br \/>\n&nbsp; <span class=\"nu0\">3<\/span> &nbsp;WITH tabpad <span class=\"kw1\">AS<\/span> <span class=\"br0\">&#40;<\/span><br \/>\n&nbsp; <span class=\"nu0\">4<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT rownum id<br \/>\n&nbsp; <span class=\"nu0\">5<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM dual<br \/>\n&nbsp; <span class=\"nu0\">6<\/span> &nbsp; &nbsp; &nbsp; &nbsp; CONNECT BY level <span class=\"sy0\">&lt;=<\/span> <span class=\"nu19\">1e4<\/span> <span class=\"br0\">&#41;<\/span><br \/>\n&nbsp; <span class=\"nu0\">7<\/span> &nbsp;SELECT <a href=\"http:\/\/www.php.net\/round\"><span class=\"kw3\">ROUND<\/span><\/a><span class=\"br0\">&#40;<\/span>rownum <span class=\"sy0\">+<\/span> <span class=\"nu19\">0.5<\/span><span class=\"sy0\">,<\/span><span class=\"nu0\">2<\/span><span class=\"br0\">&#41;<\/span> id<span class=\"sy0\">,<\/span><br \/>\n&nbsp; <span class=\"nu0\">8<\/span> &nbsp; &nbsp; &nbsp; &nbsp; MOD<span class=\"br0\">&#40;<\/span>rownum<span class=\"sy0\">,<\/span><span class=\"nu19\">1e5<\/span><span class=\"br0\">&#41;<\/span> &nbsp; &nbsp; &nbsp; tipo<span class=\"sy0\">,<\/span><br \/>\n&nbsp; <span class=\"nu0\">9<\/span> &nbsp; &nbsp; &nbsp; &nbsp; LPAD<span class=\"br0\">&#40;<\/span>rownum<span class=\"sy0\">,<\/span><span class=\"nu0\">10<\/span><span class=\"br0\">&#41;<\/span> &nbsp; &nbsp; &nbsp; vseq<br \/>\n&nbsp;<span class=\"nu0\">10<\/span> &nbsp; &nbsp;FROM tabpad t1<span class=\"sy0\">,<\/span><br \/>\n&nbsp;<span class=\"nu0\">11<\/span> &nbsp; &nbsp; &nbsp; &nbsp; tabpad t2<br \/>\n&nbsp;<span class=\"nu0\">12<\/span> &nbsp; WHERE rownum <span class=\"sy0\">&lt;=<\/span> <span class=\"nu19\">1e7<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nTabela criada<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nSQL <span class=\"sy0\">&gt;<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n<p>&nbsp;<\/p>\n<h2><span style=\"font-size: 14pt;\">2) Executar uma consulta com Hash Join nas duas tabelas<\/span><\/h2>\n<p>&nbsp;<\/p>\n<div class=\"codecolorer-container php default codecolorer-noborder\" style=\"overflow:auto;white-space:nowrap;width:100%;height:520px;\"><table cellspacing=\"0\" cellpadding=\"0\"><tbody><tr><td class=\"line-numbers\"><div>1<br \/>2<br \/>3<br \/>4<br \/>5<br \/>6<br \/>7<br \/>8<br \/>9<br \/>10<br \/>11<br \/>12<br \/>13<br \/>14<br \/>15<br \/>16<br \/>17<br \/>18<br \/>19<br \/>20<br \/>21<br \/>22<br \/>23<br \/>24<br \/>25<br \/>26<br \/>27<br \/>28<br \/>29<br \/>30<br \/>31<br \/>32<br \/>33<br \/>34<br \/>35<br \/>36<br \/>37<br \/>38<br \/>39<br \/>40<br \/>41<br \/>42<br \/>43<br \/>44<br \/>45<br \/>46<br \/>47<br \/>48<br \/>49<br \/>50<br \/>51<br \/>52<br \/>53<br \/>54<br \/>55<br \/>56<br \/>57<br \/>58<br \/>59<br \/>60<br \/>61<br \/>62<br \/>63<br \/>64<br \/>65<br \/>66<br \/>67<br \/><\/div><\/td><td><div class=\"php codecolorer\">SQL <span class=\"sy0\">&gt;<\/span> ALTER SESSION SET statistics_level<span class=\"sy0\">=<\/span>ALL<span class=\"sy0\">;<\/span><br \/>\n<br \/>\nSess\u00e3o alterada<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nDecorrido<span class=\"sy0\">:<\/span> <span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.01<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span> SELECT <span class=\"coMULTI\">\/* dbtw-068.1 *\/<\/span><br \/>\n&nbsp; <span class=\"nu0\">2<\/span> &nbsp; &nbsp; &nbsp; &nbsp; t1<span class=\"sy0\">.<\/span>vseq<span class=\"sy0\">,<\/span> t2<span class=\"sy0\">.<\/span>vseq<br \/>\n&nbsp; <span class=\"nu0\">3<\/span> &nbsp; &nbsp;FROM dbtw068_2 t2<span class=\"sy0\">,<\/span><br \/>\n&nbsp; <span class=\"nu0\">4<\/span> &nbsp; &nbsp; &nbsp; &nbsp; dbtw068_1 t1<br \/>\n&nbsp; <span class=\"nu0\">5<\/span> &nbsp; WHERE t2<span class=\"sy0\">.<\/span>tipo <span class=\"sy0\">=<\/span> <span class=\"nu0\">0<\/span><br \/>\n&nbsp; <span class=\"nu0\">6<\/span> &nbsp; &nbsp; AND t1<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> t2<span class=\"sy0\">.<\/span>id<span class=\"sy0\">;<\/span><br \/>\n<br \/>\nn\u00e3o h\u00e1 linhas selecionadas<br \/>\n<br \/>\nDecorrido<span class=\"sy0\">:<\/span> <span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">03.81<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span> column sql_id new_value m_sql_id<br \/>\nSQL <span class=\"sy0\">&gt;<\/span> column child_number new_value m_child_no<br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span> SELECT sql_id<span class=\"sy0\">,<\/span> child_number<br \/>\n&nbsp; <span class=\"nu0\">2<\/span> &nbsp; &nbsp;FROM v<span class=\"re0\">$sql<\/span><br \/>\n&nbsp; <span class=\"nu0\">3<\/span> &nbsp; WHERE sql_text LIKE <span class=\"st_h\">'%dbtw-068.1%'<\/span><br \/>\n&nbsp; <span class=\"nu0\">4<\/span> &nbsp; &nbsp; AND sql_text NOT LIKE <span class=\"st_h\">'%v$sql%'<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nSQL_ID &nbsp; &nbsp; &nbsp; &nbsp;CHILD_NUMBER<br \/>\n<span class=\"sy0\">-------------<\/span> <span class=\"sy0\">------------<\/span><br \/>\nbn86xps6jz1qj &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"nu0\">0<\/span><br \/>\n<br \/>\n<span class=\"nu0\">1<\/span> linha selecionada<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nDecorrido<span class=\"sy0\">:<\/span> <span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.08<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span> SELECT <span class=\"sy0\">*<\/span><br \/>\n&nbsp; <span class=\"nu0\">2<\/span> &nbsp; &nbsp;FROM TABLE <span class=\"br0\">&#40;<\/span>dbms_xplan<span class=\"sy0\">.<\/span>display_cursor <span class=\"br0\">&#40;<\/span><span class=\"st_h\">'&amp;m_sql_id'<\/span><span class=\"sy0\">,&amp;<\/span>m_child_no<span class=\"sy0\">,<\/span><span class=\"st_h\">'ALLSTATS LAST'<\/span><span class=\"br0\">&#41;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nPLAN_TABLE_OUTPUT<br \/>\n<span class=\"sy0\">--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span><br \/>\nSQL_ID &nbsp;bn86xps6jz1qj<span class=\"sy0\">,<\/span> child number <span class=\"nu0\">0<\/span><br \/>\n<span class=\"sy0\">-------------------------------------<\/span><br \/>\nSELECT <span class=\"coMULTI\">\/* dbtw-068.1 *\/<\/span> &nbsp; &nbsp; &nbsp; &nbsp;t1<span class=\"sy0\">.<\/span>vseq<span class=\"sy0\">,<\/span> t2<span class=\"sy0\">.<\/span>vseq &nbsp; FROM dbtw068_2 t2<span class=\"sy0\">,<\/span><br \/>\n&nbsp; &nbsp; dbtw068_1 t1 &nbsp;WHERE t2<span class=\"sy0\">.<\/span>tipo <span class=\"sy0\">=<\/span> <span class=\"nu0\">0<\/span> &nbsp; &nbsp;AND t1<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> t2<span class=\"sy0\">.<\/span>id<br \/>\n<br \/>\nPlan <a href=\"http:\/\/www.php.net\/hash\"><span class=\"kw3\">hash<\/span><\/a> value<span class=\"sy0\">:<\/span> <span class=\"nu0\">2988268432<\/span><br \/>\n<br \/>\n<span class=\"sy0\">------------------------------------------------------------------------------------------------------------------------------<\/span><br \/>\n<span class=\"sy0\">|<\/span> Id &nbsp;<span class=\"sy0\">|<\/span> Operation &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span> Name &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span> Starts <span class=\"sy0\">|<\/span> E<span class=\"sy0\">-<\/span>Rows <span class=\"sy0\">|<\/span> A<span class=\"sy0\">-<\/span>Rows <span class=\"sy0\">|<\/span> &nbsp; A<span class=\"sy0\">-<\/span><a href=\"http:\/\/www.php.net\/time\"><span class=\"kw3\">Time<\/span><\/a> &nbsp; <span class=\"sy0\">|<\/span> Buffers <span class=\"sy0\">|<\/span> Reads &nbsp;<span class=\"sy0\">|<\/span> &nbsp;OMem <span class=\"sy0\">|<\/span> &nbsp;1Mem <span class=\"sy0\">|<\/span> Used<span class=\"sy0\">-<\/span>Mem <span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">------------------------------------------------------------------------------------------------------------------------------<\/span><br \/>\n<span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">0<\/span> <span class=\"sy0\">|<\/span> SELECT STATEMENT &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">0<\/span> <span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">03.84<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">96484<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">96476<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">|*<\/span> &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp;<a href=\"http:\/\/www.php.net\/hash\"><span class=\"kw3\">HASH<\/span><\/a> <a href=\"http:\/\/www.php.net\/join\"><span class=\"kw3\">JOIN<\/span><\/a> &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp;<span class=\"nu0\">100<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">0<\/span> <span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">03.84<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">96484<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">96476<\/span> <span class=\"sy0\">|<\/span> &nbsp;1506K<span class=\"sy0\">|<\/span> &nbsp;1506K<span class=\"sy0\">|<\/span> 1617K <span class=\"br0\">&#40;<\/span><span class=\"nu0\">0<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">|*<\/span> &nbsp;<span class=\"nu0\">2<\/span> <span class=\"sy0\">|<\/span> &nbsp; TABLE ACCESS FULL<span class=\"sy0\">|<\/span> DBTW068_2 <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; <span class=\"nu0\">99<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp;<span class=\"nu0\">100<\/span> <span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.30<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">38473<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">38469<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">3<\/span> <span class=\"sy0\">|<\/span> &nbsp; TABLE ACCESS FULL<span class=\"sy0\">|<\/span> DBTW068_1 <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; 10M<span class=\"sy0\">|<\/span> &nbsp; &nbsp; 10M<span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">01.25<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">58011<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">58007<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">------------------------------------------------------------------------------------------------------------------------------<\/span><br \/>\n<br \/>\nPredicate Information <span class=\"br0\">&#40;<\/span>identified by operation id<span class=\"br0\">&#41;<\/span><span class=\"sy0\">:<\/span><br \/>\n<span class=\"sy0\">---------------------------------------------------<\/span><br \/>\n<br \/>\n&nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">-<\/span> access<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;T1&quot;<\/span><span class=\"sy0\">.<\/span><span class=\"st0\">&quot;ID&quot;<\/span><span class=\"sy0\">=<\/span><span class=\"st0\">&quot;T2&quot;<\/span><span class=\"sy0\">.<\/span><span class=\"st0\">&quot;ID&quot;<\/span><span class=\"br0\">&#41;<\/span><br \/>\n&nbsp; &nbsp;<span class=\"nu0\">2<\/span> <span class=\"sy0\">-<\/span> filter<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;T2&quot;<\/span><span class=\"sy0\">.<\/span><span class=\"st0\">&quot;TIPO&quot;<\/span><span class=\"sy0\">=<\/span><span class=\"nu0\">0<\/span><span class=\"br0\">&#41;<\/span><br \/>\n<br \/>\n<br \/>\n<span class=\"nu0\">22<\/span> linhas selecionadas<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nDecorrido<span class=\"sy0\">:<\/span> <span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.27<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Observe no plano de execu\u00e7\u00e3o que o Otimizador utilizou a opera\u00e7\u00e3o Hash Join para juntar as duas tabelas e o tempo de execu\u00e7\u00e3o da consulta foi de 3.84 segundos.<\/span><\/p>\n<h2><span style=\"font-size: 14pt;\">3) Executar uma consulta com Hash Join nas duas tabelas usando Bloom Filter<\/span><\/h2>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Para for\u00e7ar a utiliza\u00e7\u00e3o do Bloom Filter vamos utilizar o HINT PX_JOIN_FILTER(T1).<\/span><\/p>\n<p>&nbsp;<\/p>\n<div class=\"codecolorer-container php default codecolorer-noborder\" style=\"overflow:auto;white-space:nowrap;width:100%;height:520px;\"><table cellspacing=\"0\" cellpadding=\"0\"><tbody><tr><td class=\"line-numbers\"><div>1<br \/>2<br \/>3<br \/>4<br \/>5<br \/>6<br \/>7<br \/>8<br \/>9<br \/>10<br \/>11<br \/>12<br \/>13<br \/>14<br \/>15<br \/>16<br \/>17<br \/>18<br \/>19<br \/>20<br \/>21<br \/>22<br \/>23<br \/>24<br \/>25<br \/>26<br \/>27<br \/>28<br \/>29<br \/>30<br \/>31<br \/>32<br \/>33<br \/>34<br \/>35<br \/>36<br \/>37<br \/>38<br \/>39<br \/>40<br \/>41<br \/>42<br \/>43<br \/>44<br \/>45<br \/>46<br \/>47<br \/>48<br \/>49<br \/>50<br \/>51<br \/>52<br \/>53<br \/>54<br \/>55<br \/>56<br \/>57<br \/>58<br \/>59<br \/>60<br \/>61<br \/>62<br \/>63<br \/>64<br \/><\/div><\/td><td><div class=\"php codecolorer\">SQL <span class=\"sy0\">&gt;<\/span> SELECT <span class=\"coMULTI\">\/*+ px_join_filter(t1) *\/<\/span> <span class=\"coMULTI\">\/* dbtw-068.2 *\/<\/span><br \/>\n&nbsp; <span class=\"nu0\">2<\/span> &nbsp; &nbsp; &nbsp; &nbsp; t1<span class=\"sy0\">.<\/span>vseq<span class=\"sy0\">,<\/span> t2<span class=\"sy0\">.<\/span>vseq<br \/>\n&nbsp; <span class=\"nu0\">3<\/span> &nbsp; &nbsp;FROM dbtw068_2 t2<span class=\"sy0\">,<\/span><br \/>\n&nbsp; <span class=\"nu0\">4<\/span> &nbsp; &nbsp; &nbsp; &nbsp; dbtw068_1 t1<br \/>\n&nbsp; <span class=\"nu0\">5<\/span> &nbsp; WHERE t2<span class=\"sy0\">.<\/span>tipo <span class=\"sy0\">=<\/span> <span class=\"nu0\">0<\/span><br \/>\n&nbsp; <span class=\"nu0\">6<\/span> &nbsp; &nbsp; AND t1<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> t2<span class=\"sy0\">.<\/span>id<span class=\"sy0\">;<\/span><br \/>\n<br \/>\nn\u00e3o h\u00e1 linhas selecionadas<br \/>\n<br \/>\nDecorrido<span class=\"sy0\">:<\/span> <span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.81<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span> column sql_id new_value m_sql_id<br \/>\nSQL <span class=\"sy0\">&gt;<\/span> column child_number new_value m_child_no<br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span> SELECT sql_id<span class=\"sy0\">,<\/span> child_number<br \/>\n&nbsp; <span class=\"nu0\">2<\/span> &nbsp; &nbsp;FROM v<span class=\"re0\">$sql<\/span><br \/>\n&nbsp; <span class=\"nu0\">3<\/span> &nbsp; WHERE sql_text LIKE <span class=\"st_h\">'%dbtw-068.2%'<\/span><br \/>\n&nbsp; <span class=\"nu0\">4<\/span> &nbsp; &nbsp; AND sql_text NOT LIKE <span class=\"st_h\">'%v$sql%'<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nSQL_ID &nbsp; &nbsp; &nbsp; &nbsp;CHILD_NUMBER<br \/>\n<span class=\"sy0\">-------------<\/span> <span class=\"sy0\">------------<\/span><br \/>\nff290kagtx0cy &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"nu0\">0<\/span><br \/>\n<br \/>\n<span class=\"nu0\">1<\/span> linha selecionada<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nDecorrido<span class=\"sy0\">:<\/span> <span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.02<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span> SELECT <span class=\"sy0\">*<\/span><br \/>\n&nbsp; <span class=\"nu0\">2<\/span> &nbsp; &nbsp;FROM TABLE <span class=\"br0\">&#40;<\/span>dbms_xplan<span class=\"sy0\">.<\/span>display_cursor <span class=\"br0\">&#40;<\/span><span class=\"st_h\">'&amp;m_sql_id'<\/span><span class=\"sy0\">,&amp;<\/span>m_child_no<span class=\"sy0\">,<\/span><span class=\"st_h\">'ALLSTATS LAST'<\/span><span class=\"br0\">&#41;<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">;<\/span><br \/>\n<br \/>\nPLAN_TABLE_OUTPUT<br \/>\n<span class=\"sy0\">--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span><br \/>\nSQL_ID &nbsp;ff290kagtx0cy<span class=\"sy0\">,<\/span> child number <span class=\"nu0\">0<\/span><br \/>\n<span class=\"sy0\">-------------------------------------<\/span><br \/>\nSELECT <span class=\"coMULTI\">\/*+ px_join_filter(t1) *\/<\/span> <span class=\"coMULTI\">\/* dbtw-068.2 *\/<\/span> &nbsp; &nbsp; &nbsp; &nbsp;t1<span class=\"sy0\">.<\/span>vseq<span class=\"sy0\">,<\/span><br \/>\nt2<span class=\"sy0\">.<\/span>vseq &nbsp; FROM dbtw068_2 t2<span class=\"sy0\">,<\/span> &nbsp; &nbsp; &nbsp; &nbsp;dbtw068_1 t1 &nbsp;WHERE t2<span class=\"sy0\">.<\/span>tipo <span class=\"sy0\">=<\/span> <span class=\"nu0\">0<\/span><br \/>\nAND t1<span class=\"sy0\">.<\/span>id <span class=\"sy0\">=<\/span> t2<span class=\"sy0\">.<\/span>id<br \/>\n<br \/>\nPlan <a href=\"http:\/\/www.php.net\/hash\"><span class=\"kw3\">hash<\/span><\/a> value<span class=\"sy0\">:<\/span> <span class=\"nu0\">629010780<\/span><br \/>\n<br \/>\n<span class=\"sy0\">-------------------------------------------------------------------------------------------------------------------------------<\/span><br \/>\n<span class=\"sy0\">|<\/span> Id &nbsp;<span class=\"sy0\">|<\/span> Operation &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> Name &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span> Starts <span class=\"sy0\">|<\/span> E<span class=\"sy0\">-<\/span>Rows <span class=\"sy0\">|<\/span> A<span class=\"sy0\">-<\/span>Rows <span class=\"sy0\">|<\/span> &nbsp; A<span class=\"sy0\">-<\/span><a href=\"http:\/\/www.php.net\/time\"><span class=\"kw3\">Time<\/span><\/a> &nbsp; <span class=\"sy0\">|<\/span> Buffers <span class=\"sy0\">|<\/span> Reads &nbsp;<span class=\"sy0\">|<\/span> &nbsp;OMem <span class=\"sy0\">|<\/span> &nbsp;1Mem <span class=\"sy0\">|<\/span> Used<span class=\"sy0\">-<\/span>Mem <span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">-------------------------------------------------------------------------------------------------------------------------------<\/span><br \/>\n<span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">0<\/span> <span class=\"sy0\">|<\/span> SELECT STATEMENT &nbsp; &nbsp;<span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">0<\/span> <span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.81<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">96484<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">96476<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">|*<\/span> &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp;<a href=\"http:\/\/www.php.net\/hash\"><span class=\"kw3\">HASH<\/span><\/a> <a href=\"http:\/\/www.php.net\/join\"><span class=\"kw3\">JOIN<\/span><\/a> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp;<span class=\"nu0\">100<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">0<\/span> <span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.81<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">96484<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">96476<\/span> <span class=\"sy0\">|<\/span> &nbsp;1506K<span class=\"sy0\">|<\/span> &nbsp;1506K<span class=\"sy0\">|<\/span> 1231K <span class=\"br0\">&#40;<\/span><span class=\"nu0\">0<\/span><span class=\"br0\">&#41;<\/span><span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">2<\/span> <span class=\"sy0\">|<\/span> &nbsp; <a href=\"http:\/\/www.php.net\/join\"><span class=\"kw3\">JOIN<\/span><\/a> FILTER CREATE<span class=\"sy0\">|<\/span> <span class=\"sy0\">:<\/span>BF0000 &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; <span class=\"nu0\">99<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp;<span class=\"nu0\">100<\/span> <span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.19<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">38473<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">38469<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">|*<\/span> &nbsp;<span class=\"nu0\">3<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp;TABLE ACCESS FULL<span class=\"sy0\">|<\/span> DBTW068_2 <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; <span class=\"nu0\">99<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp;<span class=\"nu0\">100<\/span> <span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.18<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">38473<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">38469<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">4<\/span> <span class=\"sy0\">|<\/span> &nbsp; <a href=\"http:\/\/www.php.net\/join\"><span class=\"kw3\">JOIN<\/span><\/a> FILTER <span class=\"kw2\">USE<\/span> &nbsp; <span class=\"sy0\">|<\/span> <span class=\"sy0\">:<\/span>BF0000 &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; 10M<span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">15102<\/span> <span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.62<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">58011<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">58007<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">|*<\/span> &nbsp;<span class=\"nu0\">5<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp;TABLE ACCESS FULL<span class=\"sy0\">|<\/span> DBTW068_1 <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; 10M<span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">15102<\/span> <span class=\"sy0\">|<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.62<\/span> <span class=\"sy0\">|<\/span> &nbsp; <span class=\"nu0\">58011<\/span> <span class=\"sy0\">|<\/span> &nbsp;<span class=\"nu0\">58007<\/span> <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; <span class=\"sy0\">|<\/span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class=\"sy0\">|<\/span><br \/>\n<span class=\"sy0\">-------------------------------------------------------------------------------------------------------------------------------<\/span><br \/>\n<br \/>\nPredicate Information <span class=\"br0\">&#40;<\/span>identified by operation id<span class=\"br0\">&#41;<\/span><span class=\"sy0\">:<\/span><br \/>\n<span class=\"sy0\">---------------------------------------------------<\/span><br \/>\n<br \/>\n&nbsp; &nbsp;<span class=\"nu0\">1<\/span> <span class=\"sy0\">-<\/span> access<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;T1&quot;<\/span><span class=\"sy0\">.<\/span><span class=\"st0\">&quot;ID&quot;<\/span><span class=\"sy0\">=<\/span><span class=\"st0\">&quot;T2&quot;<\/span><span class=\"sy0\">.<\/span><span class=\"st0\">&quot;ID&quot;<\/span><span class=\"br0\">&#41;<\/span><br \/>\n&nbsp; &nbsp;<span class=\"nu0\">3<\/span> <span class=\"sy0\">-<\/span> filter<span class=\"br0\">&#40;<\/span><span class=\"st0\">&quot;T2&quot;<\/span><span class=\"sy0\">.<\/span><span class=\"st0\">&quot;TIPO&quot;<\/span><span class=\"sy0\">=<\/span><span class=\"nu0\">0<\/span><span class=\"br0\">&#41;<\/span><br \/>\n&nbsp; &nbsp;<span class=\"nu0\">5<\/span> <span class=\"sy0\">-<\/span> filter<span class=\"br0\">&#40;<\/span>SYS_OP_BLOOM_FILTER<span class=\"br0\">&#40;<\/span><span class=\"sy0\">:<\/span>BF0000<span class=\"sy0\">,<\/span><span class=\"st0\">&quot;T1&quot;<\/span><span class=\"sy0\">.<\/span><span class=\"st0\">&quot;ID&quot;<\/span><span class=\"br0\">&#41;<\/span><span class=\"br0\">&#41;<\/span><br \/>\n<br \/>\n<br \/>\n<span class=\"nu0\">26<\/span> linhas selecionadas<span class=\"sy0\">.<\/span><br \/>\n<br \/>\nDecorrido<span class=\"sy0\">:<\/span> <span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu8\">00<\/span><span class=\"sy0\">:<\/span><span class=\"nu19\">00.07<\/span><br \/>\nSQL <span class=\"sy0\">&gt;<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify;\"><span style=\"font-size: 12pt;\">Com a utiliza\u00e7\u00e3o do Bloom Filter no plano de execu\u00e7\u00e3o o tempo de reposta da consulta caiu para 81 cent\u00e9simos de segundo, esta redu\u00e7\u00e3o de tempo ocorre porque durante a execu\u00e7\u00e3o do plano o Oracle passa o resultado de cada opera\u00e7\u00e3o (Row Source) para o opera\u00e7\u00e3o pai, no nosso plano apesar da opera\u00e7\u00e3o 5 (TABLE ACCESS FULL) ter lido 10 milh\u00f5es de linhas, devido a utiliza\u00e7\u00e3o da opera\u00e7\u00e3o JOIN FILTER USE\u00a0(Bloom Filter) ela s\u00f3 vai passar para opera\u00e7\u00e3o pai 1 (HASH JOIN) cerca de 15 mil linhas. Esta redu\u00e7\u00e3o faz toda a diferen\u00e7a pois a opera\u00e7\u00e3o HASH JOIN vai trabalhar com um numero de linhas bem menor comparada com as 10 milh\u00f5es de linhas tratadas na primeira consulta sem a utiliza\u00e7\u00e3o do Bloom Filter.<\/span><\/p>\n<h2><span style=\"font-size: 14pt;\">Refer\u00eancias<\/span><\/h2>\n<p><strong><span style=\"font-size: 12pt;\"><a href=\"https:\/\/antognini.ch\/papers\/BloomFilters20080620.pdf\" target=\"_blank\">https:\/\/antognini.ch\/papers\/BloomFilters20080620.pdf<\/a><\/span><\/strong><\/p>\n<p>&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n<a href=\"http:\/\/dbtimewizard.com.br\/treinamento\/\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-1255\" src=\"http:\/\/dbtimewizard.com.br\/blog\/wp-content\/uploads\/2018\/10\/promo_cur02.jpg\" alt=\"Promo-D75L\" width=\"330\" height=\"242\" \/><\/a><\/p>\n<p><a href=\"https:\/\/youtu.be\/l6OphhTle9I\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-full wp-image-1215\" src=\"http:\/\/dbtimewizard.com.br\/wp-content\/uploads\/2018\/01\/video-02-300x168.jpg\" alt=\"video-02\" width=\"330\" height=\"242\" \/><\/a><br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<br \/>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Analisando um plano de execu\u00e7\u00e3o eventualmente podemos chegar a conclus\u00e3o que o m\u00e9todo de jun\u00e7\u00e3o mais eficiente para duas tabelas neste plano de execu\u00e7\u00e3o seja o HASH JOIN, nesta opera\u00e7\u00e3o podemos utilizar alguns recursos do Oracle para deixa-la mais eficiente, porem alguns desses recursos dependem do tipo de licenciamento do banco, podemos usar paralelismo, particionamento de tabelas, Exadata ou Serial<\/p>\n","protected":false},"author":2,"featured_media":2269,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[87],"tags":[246,89,82,10,247],"class_list":["post-2268","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-table-join","tag-bloom-filter","tag-hash-join","tag-optimizer","tag-otimizador","tag-px_join_filter"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2268","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=2268"}],"version-history":[{"count":10,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2268\/revisions"}],"predecessor-version":[{"id":2284,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2268\/revisions\/2284"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/2269"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=2268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=2268"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=2268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}