{"id":9163,"date":"2015-10-16T11:59:51","date_gmt":"2015-10-16T14:59:51","guid":{"rendered":"http:\/\/www.orabr.com\/sql-tuning-foreign-key-sem-indice-fk-precisa-de-indice-para-ter-melhor-performance\/"},"modified":"2015-10-16T11:59:51","modified_gmt":"2015-10-16T14:59:51","slug":"SQL-TUNING-FOREIGN-KEY-SEM-INDICE-FK-PRECISA-DE-INDICE-PARA-TER-MELHOR-PERFORMANCE","status":"publish","type":"post","link":"https:\/\/orabr.virttus.com\/?p=9163","title":{"rendered":"[SQL Tuning] – Foreign key sem \u00edndice. FK precisa de \u00edndice para ter melhor performance."},"content":{"rendered":"

<\/p>\n

\u00a0Existe perda de performance para chave estrangeira que n\u00e3o possui um \u00edndice.<\/p>\n

\u00a0A query abaixo mostra todas as chaves estrangeiras que n\u00e3o possuem um \u00edndice.<\/p>\n

\u00a0<\/p>\n

select<\/span>\u00a0<\/span>case<\/span><\/u><\/u><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>when<\/span>\u00a0b.table_name\u00a0<\/span>is<\/span>\u00a0<\/span>null<\/span>\u00a0<\/span>then<\/span><\/u><\/u><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>‘Sem \u00edndice’<\/span><\/u><\/u><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>else<\/span><\/u><\/u><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>‘Com \u00edndice’<\/span><\/u><\/u><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>end<\/span>\u00a0<\/span>as<\/span>\u00a0status,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.table_name\u00a0<\/span>as<\/span>\u00a0table_name,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.constraint_name\u00a0<\/span>as<\/span>\u00a0fk_name,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.fk_columns\u00a0<\/span>as<\/span>\u00a0fk_columns,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 b.index_name\u00a0<\/span>as<\/span>\u00a0index_name,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0b.index_columns\u00a0<\/span>as<\/span>\u00a0index_columns<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0<\/span>from<\/span>\u00a0(<\/span>select<\/span>\u00a0a.table_name,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 a.constraint_name,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 listagg(a.column_name,\u00a0<\/span>‘,’<\/span>)\u00a0<\/span>within<\/span>\u00a0<\/span>group<\/span>(<\/span>order<\/span>\u00a0<\/span>by<\/span>\u00a0a.position) fk_columns<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>from<\/span>\u00a0dba_cons_columns a, dba_constraints b<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>where<\/span>\u00a0a.constraint_name = b.constraint_name<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>and<\/span>\u00a0b.constraint_type =\u00a0<\/span>‘R’<\/span><\/u><\/u><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>and<\/span>\u00a0a.owner =\u00a0<\/span><\/u>a.owner<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>and<\/span>\u00a0a.owner = b.owner<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>group<\/span>\u00a0<\/span>by<\/span>\u00a0a.table_name, a.constraint_name) a,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (<\/span>select<\/span>\u00a0table_name,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 index_name,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0listagg(c.column_<\/wbr>name,\u00a0<\/span>‘,’<\/span>)\u00a0<\/span>within<\/span>\u00a0<\/span>group<\/span>(<\/span>order<\/span>\u00a0<\/span>by<\/span>\u00a0c.column_position) index_columns<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>from<\/span>\u00a0dba_ind_columns c<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>where<\/span>\u00a0c.index_owner =\u00a0<\/span><\/u>c.index_owner<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>group<\/span>\u00a0<\/span>by<\/span>\u00a0table_name, index_name) b<\/u><\/u><\/span><\/p>\n

where<\/span>\u00a0a.table_name = b.table_name(+)<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0<\/span>and<\/span>\u00a0b.index_columns(+)\u00a0<\/span>like<\/span>\u00a0a.fk_columns ||\u00a0<\/span>‘%’<\/span><\/u><\/u><\/p>\n

order<\/span>\u00a0<\/span>by<\/span>\u00a0<\/span>1<\/span>\u00a0<\/span>desc<\/span>,\u00a0<\/span>2<\/span>;<\/span><\/p>\n

\u00a0<\/p>\n

Fonte:\u00a0http:\/\/www.dba-oracle.com\/t_find_foreign_keys_with_no_index.htm<\/a><\/p>\n

<\/HTML><\/HTML><\/p>\n","protected":false},"excerpt":{"rendered":"