IN = BAD, EXISTS = GOOD!

Você pode estar se perguntando: IN e EXISTS são diferentes, e usados em diferentes situações! Como posso dizer que um é “bom” e o outro é “ruim”?

Assim como nos posts anteriores desta série – Tuning de SQL – temo que prestar atenção nos casos em que essas “dicas” são aplicáveis. Obviamente o EXISTS não pode simplesmente substituir o IN em todos os casos – e o desenvolvedor/programador/analista/DBA é quem deve analisar o caso e verificar se é possível aplicar esta melhoria.

Usamos o IN para verificar se um valor está contido em uma lista, enquanto o EXISTS é usado para verificar a existência de linhas retornadas por uma subconsulta. IN verifica os valores reais, enquanto EXISTS apenas verifica a existência. Geralmente EXISTS tem um melhor desempenho do que IN em subconsultas – e como nosso objetivo aqui é desempenho, devemos usar EXISTS ao invés de IN sempre que possível.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”UnVpbSAodXNhbmRvIElOKToKClNRTD4gU0VMRUNUIGQuZGVwYXJ0bWVudF9pZCwgZC5kZXBhcnRtZW50X25hbWUKICAyICBGUk9NIGRlcGFydG1lbnRzIGQKICAzICBXSEVSRSBkLmRlcGFydG1lbnRfaWQgSU4KICA0ICAoU0VMRUNUIGRlcGFydG1lbnRfaWQgRlJPTSBlbXBsb3llZXMpOwoKREVQQVJUTUVOVF9JRCBERVBBUlRNRU5UX05BTUUKLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KICAgICAgICAgICAxMCBBZG1pbmlzdHJhdGlvbgogICAgICAgICAgIDIwIE1hcmtldGluZwogICAgICAgICAgIDMwIFB1cmNoYXNpbmcKICAgICAgICAgICA0MCBIdW1hbiBSZXNvdXJjZXMKICAgICAgICAgICA1MCBTaGlwcGluZwogICAgICAgICAgIDYwIElUCiAgICAgICAgICAgNzAgUHVibGljIFJlbGF0aW9ucwogICAgICAgICAgIDgwIFNhbGVzCiAgICAgICAgICAgOTAgRXhlY3V0aXZlCiAgICAgICAgICAxMDAgRmluYW5jZQogICAgICAgICAgMTEwIEFjY291bnRpbmcKCjExIGxpbmhhcyBzZWxlY2lvbmFkYXMuCkJvbSAodXNhbmRvIEVYSVNUUyk6CgpTUUw+IFNFTEVDVCBkZXBhcnRtZW50X2lkLCBkZXBhcnRtZW50X25hbWUKICAyICBGUk9NIGRlcGFydG1lbnRzIGQKICAzICBXSEVSRSBFWElTVFMKICA0ICAoU0VMRUNUIDEgRlJPTSBlbXBsb3llZXMgZQogIDUgIFdIRVJFIGUuZGVwYXJ0bWVudF9pZCA9IGQuZGVwYXJ0bWVudF9pZCk7CgpERVBBUlRNRU5UX0lEIERFUEFSVE1FTlRfTkFNRQotLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQogICAgICAgICAgIDEwIEFkbWluaXN0cmF0aW9uCiAgICAgICAgICAgMjAgTWFya2V0aW5nCiAgICAgICAgICAgMzAgUHVyY2hhc2luZwogICAgICAgICAgIDQwIEh1bWFuIFJlc291cmNlcwogICAgICAgICAgIDUwIFNoaXBwaW5nCiAgICAgICAgICAgNjAgSVQKICAgICAgICAgICA3MCBQdWJsaWMgUmVsYXRpb25zCiAgICAgICAgICAgODAgU2FsZXMKICAgICAgICAgICA5MCBFeGVjdXRpdmUKICAgICAgICAgIDEwMCBGaW5hbmNlCiAgICAgICAgICAxMTAgQWNjb3VudGluZwoKMTEgbGluaGFzIHNlbGVjaW9uYWRhcy4=” hover_enabled=”0″]UnVpbSAodXNhbmRvIElOKToKClNRTD4gU0VMRUNUIGQuZGVwYXJ0bWVudF9pZCwgZC5kZXBhcnRtZW50X25hbWUKICAyICBGUk9NIGRlcGFydG1lbnRzIGQKICAzICBXSEVSRSBkLmRlcGFydG1lbnRfaWQgSU4KICA0ICAoU0VMRUNUIGRlcGFydG1lbnRfaWQgRlJPTSBlbXBsb3llZXMpOwoKREVQQVJUTUVOVF9JRCBERVBBUlRNRU5UX05BTUUKLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KICAgICAgICAgICAxMCBBZG1pbmlzdHJhdGlvbgogICAgICAgICAgIDIwIE1hcmtldGluZwogICAgICAgICAgIDMwIFB1cmNoYXNpbmcKICAgICAgICAgICA0MCBIdW1hbiBSZXNvdXJjZXMKICAgICAgICAgICA1MCBTaGlwcGluZwogICAgICAgICAgIDYwIElUCiAgICAgICAgICAgNzAgUHVibGljIFJlbGF0aW9ucwogICAgICAgICAgIDgwIFNhbGVzCiAgICAgICAgICAgOTAgRXhlY3V0aXZlCiAgICAgICAgICAxMDAgRmluYW5jZQogICAgICAgICAgMTEwIEFjY291bnRpbmcKCjExIGxpbmhhcyBzZWxlY2lvbmFkYXMuCkJvbSAodXNhbmRvIEVYSVNUUyk6CgpTUUw+IFNFTEVDVCBkZXBhcnRtZW50X2lkLCBkZXBhcnRtZW50X25hbWUKICAyICBGUk9NIGRlcGFydG1lbnRzIGQKICAzICBXSEVSRSBFWElTVFMKICA0ICAoU0VMRUNUIDEgRlJPTSBlbXBsb3llZXMgZQogIDUgIFdIRVJFIGUuZGVwYXJ0bWVudF9pZCA9IGQuZGVwYXJ0bWVudF9pZCk7CgpERVBBUlRNRU5UX0lEIERFUEFSVE1FTlRfTkFNRQotLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQogICAgICAgICAgIDEwIEFkbWluaXN0cmF0aW9uCiAgICAgICAgICAgMjAgTWFya2V0aW5nCiAgICAgICAgICAgMzAgUHVyY2hhc2luZwogICAgICAgICAgIDQwIEh1bWFuIFJlc291cmNlcwogICAgICAgICAgIDUwIFNoaXBwaW5nCiAgICAgICAgICAgNjAgSVQKICAgICAgICAgICA3MCBQdWJsaWMgUmVsYXRpb25zCiAgICAgICAgICAgODAgU2FsZXMKICAgICAgICAgICA5MCBFeGVjdXRpdmUKICAgICAgICAgIDEwMCBGaW5hbmNlCiAgICAgICAgICAxMTAgQWNjb3VudGluZwoKMTEgbGluaGFzIHNlbGVjaW9uYWRhcy4=[/et_pb_dmb_code_snippet]

DISTINCT = BAD, EXISTS = GOOD!

Quando queremos suprimir linhas duplicadas, ou seja, não queremos linhas completamente repetidas no retorno de um SELECT, usamos a cláusula DISTINCT, correto? Para essa operação ser realizada, internamente o banco faz uma ordenação das linhas recuperadas para facilitar assim a exclusão das linhas repetidas.

Em alguns casos, é possível substituir o DISTINCT pelo EXISTS. O resultado do SELECT será o mesmo, porém, o Oracle não tem o custo de classificar todas as linhas para excluir as repetidas.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”UnVpbSAoRElTVElOQ1QpOgoKU1FMPiBTRUxFQ1QgRElTVElOQ1QgZC5kZXBhcnRtZW50X2lkLCBkLmRlcGFydG1lbnRfbmFtZQogIDIgIEZST00gZGVwYXJ0bWVudHMgZCwgZW1wbG95ZWVzIGUKICAzICBXSEVSRSBkLmRlcGFydG1lbnRfaWQgPSBlLmRlcGFydG1lbnRfaWQ7CgpERVBBUlRNRU5UX0lEIERFUEFSVE1FTlRfTkFNRQotLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQogICAgICAgICAgMTAwIEZpbmFuY2UKICAgICAgICAgICA1MCBTaGlwcGluZwogICAgICAgICAgIDcwIFB1YmxpYyBSZWxhdGlvbnMKICAgICAgICAgICAzMCBQdXJjaGFzaW5nCiAgICAgICAgICAgOTAgRXhlY3V0aXZlCiAgICAgICAgICAgMTAgQWRtaW5pc3RyYXRpb24KICAgICAgICAgIDExMCBBY2NvdW50aW5nCiAgICAgICAgICAgNDAgSHVtYW4gUmVzb3VyY2VzCiAgICAgICAgICAgMjAgTWFya2V0aW5nCiAgICAgICAgICAgNjAgSVQKICAgICAgICAgICA4MCBTYWxlcwoKMTEgbGluaGFzIHNlbGVjaW9uYWRhcy4KQm9tIChFWElTVFMpOgoKU1FMPiBTRUxFQ1QgZGVwYXJ0bWVudF9pZCwgZGVwYXJ0bWVudF9uYW1lCiAgMiAgRlJPTSBkZXBhcnRtZW50cyBkCiAgMyAgV0hFUkUgRVhJU1RTCiAgNCAgKFNFTEVDVCAxIEZST00gZW1wbG95ZWVzIGUKICA1ICBXSEVSRSBlLmRlcGFydG1lbnRfaWQgPSBkLmRlcGFydG1lbnRfaWQpOwoKREVQQVJUTUVOVF9JRCBERVBBUlRNRU5UX05BTUUKLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KICAgICAgICAgICAxMCBBZG1pbmlzdHJhdGlvbgogICAgICAgICAgIDIwIE1hcmtldGluZwogICAgICAgICAgIDMwIFB1cmNoYXNpbmcKICAgICAgICAgICA0MCBIdW1hbiBSZXNvdXJjZXMKICAgICAgICAgICA1MCBTaGlwcGluZwogICAgICAgICAgIDYwIElUCiAgICAgICAgICAgNzAgUHVibGljIFJlbGF0aW9ucwogICAgICAgICAgIDgwIFNhbGVzCiAgICAgICAgICAgOTAgRXhlY3V0aXZlCiAgICAgICAgICAxMDAgRmluYW5jZQogICAgICAgICAgMTEwIEFjY291bnRpbmcKCjExIGxpbmhhcyBzZWxlY2lvbmFkYXMu” hover_enabled=”0″]UnVpbSAoRElTVElOQ1QpOgoKU1FMPiBTRUxFQ1QgRElTVElOQ1QgZC5kZXBhcnRtZW50X2lkLCBkLmRlcGFydG1lbnRfbmFtZQogIDIgIEZST00gZGVwYXJ0bWVudHMgZCwgZW1wbG95ZWVzIGUKICAzICBXSEVSRSBkLmRlcGFydG1lbnRfaWQgPSBlLmRlcGFydG1lbnRfaWQ7CgpERVBBUlRNRU5UX0lEIERFUEFSVE1FTlRfTkFNRQotLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQogICAgICAgICAgMTAwIEZpbmFuY2UKICAgICAgICAgICA1MCBTaGlwcGluZwogICAgICAgICAgIDcwIFB1YmxpYyBSZWxhdGlvbnMKICAgICAgICAgICAzMCBQdXJjaGFzaW5nCiAgICAgICAgICAgOTAgRXhlY3V0aXZlCiAgICAgICAgICAgMTAgQWRtaW5pc3RyYXRpb24KICAgICAgICAgIDExMCBBY2NvdW50aW5nCiAgICAgICAgICAgNDAgSHVtYW4gUmVzb3VyY2VzCiAgICAgICAgICAgMjAgTWFya2V0aW5nCiAgICAgICAgICAgNjAgSVQKICAgICAgICAgICA4MCBTYWxlcwoKMTEgbGluaGFzIHNlbGVjaW9uYWRhcy4KQm9tIChFWElTVFMpOgoKU1FMPiBTRUxFQ1QgZGVwYXJ0bWVudF9pZCwgZGVwYXJ0bWVudF9uYW1lCiAgMiAgRlJPTSBkZXBhcnRtZW50cyBkCiAgMyAgV0hFUkUgRVhJU1RTCiAgNCAgKFNFTEVDVCAxIEZST00gZW1wbG95ZWVzIGUKICA1ICBXSEVSRSBlLmRlcGFydG1lbnRfaWQgPSBkLmRlcGFydG1lbnRfaWQpOwoKREVQQVJUTUVOVF9JRCBERVBBUlRNRU5UX05BTUUKLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KICAgICAgICAgICAxMCBBZG1pbmlzdHJhdGlvbgogICAgICAgICAgIDIwIE1hcmtldGluZwogICAgICAgICAgIDMwIFB1cmNoYXNpbmcKICAgICAgICAgICA0MCBIdW1hbiBSZXNvdXJjZXMKICAgICAgICAgICA1MCBTaGlwcGluZwogICAgICAgICAgIDYwIElUCiAgICAgICAgICAgNzAgUHVibGljIFJlbGF0aW9ucwogICAgICAgICAgIDgwIFNhbGVzCiAgICAgICAgICAgOTAgRXhlY3V0aXZlCiAgICAgICAgICAxMDAgRmluYW5jZQogICAgICAgICAgMTEwIEFjY291bnRpbmcKCjExIGxpbmhhcyBzZWxlY2lvbmFkYXMu[/et_pb_dmb_code_snippet]