[Dica] Select das FK´s sem índice

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Esse compara os campos das FK´s com os campos dos índices.

Selecionar tudo

select decode( b.table_name, NULL, '****', 'ok' ) Status,
       a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_name,
         max(decode(position, 1,     cname,NULL)) ||
         max(decode(position, 2,', '||cname,NULL)) ||
         max(decode(position, 3,', '||cname,NULL)) ||
         max(decode(position, 4,', '||cname,NULL)) ||
         max(decode(position, 5,', '||cname,NULL)) ||
         max(decode(position, 6,', '||cname,NULL)) ||
         max(decode(position, 7,', '||cname,NULL)) ||
         max(decode(position, 8,', '||cname,NULL)) ||
         max(decode(position, 9,', '||cname,NULL)) ||
         max(decode(position,10,', '||cname,NULL)) ||
         max(decode(position,11,', '||cname,NULL)) ||
         max(decode(position,12,', '||cname,NULL)) ||
         max(decode(position,13,', '||cname,NULL)) ||
         max(decode(position,14,', '||cname,NULL)) ||
         max(decode(position,15,', '||cname,NULL)) ||
         max(decode(position,16,', '||cname,NULL)) columns
    from (select substr(column_name,1,30) cname,
                 substr(table_name,1,30) table_name,
                 substr(constraint_name,1,30) constraint_name,
                 position
            from user_cons_columns) a,
         user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by a.table_name, a.constraint_name ) a,
( select table_name, index_name,
         max(decode(position, 1,     cname,NULL)) ||
         max(decode(position, 2,', '||cname,NULL)) ||
         max(decode(position, 3,', '||cname,NULL)) ||
         max(decode(position, 4,', '||cname,NULL)) ||
         max(decode(position, 5,', '||cname,NULL)) ||
         max(decode(position, 6,', '||cname,NULL)) ||
         max(decode(position, 7,', '||cname,NULL)) ||
         max(decode(position, 8,', '||cname,NULL)) ||
         max(decode(position, 9,', '||cname,NULL)) ||
         max(decode(position,10,', '||cname,NULL)) ||
         max(decode(position,11,', '||cname,NULL)) ||
         max(decode(position,12,', '||cname,NULL)) ||
         max(decode(position,13,', '||cname,NULL)) ||
         max(decode(position,14,', '||cname,NULL)) ||
         max(decode(position,15,', '||cname,NULL)) ||
         max(decode(position,16,', '||cname,NULL)) columns
    from ( select substr( column_name, 1, 30 ) cname,
                  substr( table_name, 1, 30 ) table_name,
                  substr( index_name, 1, 30 ) index_name,
                  column_position position
             from user_ind_columns  ) user_ind_columns
   group by table_name, index_name ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'

Exemplo:

Selecionar tudo

9.2 oraprod:TFG> /

STAT TABLE_NAME                     COLUMNS                                                      COLUMNS
---- ------------------------------ ------------------------------------------------------------ --------------------------------------------------------
**** NAMED_SQL                      FK_GROUP
ok   TB_ACESSORIO                   CD_PESSOA, CD_ESTUDO, CD_ENDOSSO, CD_ITEM                    CD_PESSOA, CD_ESTUDO, CD_ENDOSSO, CD_ITEM, CD_ACESSORIO
**** TB_ACESSO_USUARIO              CD_PESSOA_UNIDADE, CD_DEPARTAMENTO, CD_SETOR
ok   TB_ACESSO_USUARIO              NM_USUARIO                                                   NM_USUARIO, CD_PESSOA_UNIDADE, CD_DEPARTAMENTO, CD_SETOR
ok   TB_AGENDA_VISITA               CD_PESSOA                                                    CD_PESSOA, FL_TIPO_PESSOA, DT_INICIO, DT_FIM
ok   TB_AGENDA_VISITA               CD_PESSOA                                                    CD_PESSOA
ok   TB_AGENDA_VISITA               NM_USUARIO                                                   NM_USUARIO
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Com uma pequena modificação, fiz uma coluna com o CREATE INDEX dos índices que faltam. :-D

Selecionar tudo

select --decode( b.table_name, NULL, '****', 'ok' ) Status,
      a.table_name, x.num_rows, --a.constraint_name, a.columns, b.columns,
      'create index '||a.constraint_name||'_I on '||a.table_name||' ('||a.columns||');' cre_ind
from
 user_tables x
,
( select a.table_name, a.constraint_name,
         max(decode(position, 1,      cname,NULL)) ||
         max(decode(position, 2,', '||cname,NULL)) ||
         max(decode(position, 3,', '||cname,NULL)) ||
         max(decode(position, 4,', '||cname,NULL)) ||
         max(decode(position, 5,', '||cname,NULL)) ||
         max(decode(position, 6,', '||cname,NULL)) ||
         max(decode(position, 7,', '||cname,NULL)) ||
         max(decode(position, 8,', '||cname,NULL)) ||
         max(decode(position, 9,', '||cname,NULL)) ||
         max(decode(position,10,', '||cname,NULL)) ||
         max(decode(position,11,', '||cname,NULL)) ||
         max(decode(position,12,', '||cname,NULL)) ||
         max(decode(position,13,', '||cname,NULL)) ||
         max(decode(position,14,', '||cname,NULL)) ||
         max(decode(position,15,', '||cname,NULL)) ||
         max(decode(position,16,', '||cname,NULL)) columns
    from (select substr(column_name,1,30) cname,
                 substr(table_name,1,30) table_name,
                 substr(constraint_name,1,30) constraint_name,
                 position
            from user_cons_columns) a,
         user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by a.table_name, a.constraint_name ) a,
( select table_name, index_name,
         max(decode(position, 1,     cname,NULL)) ||
         max(decode(position, 2,', '||cname,NULL)) ||
         max(decode(position, 3,', '||cname,NULL)) ||
         max(decode(position, 4,', '||cname,NULL)) ||
         max(decode(position, 5,', '||cname,NULL)) ||
         max(decode(position, 6,', '||cname,NULL)) ||
         max(decode(position, 7,', '||cname,NULL)) ||
         max(decode(position, 8,', '||cname,NULL)) ||
         max(decode(position, 9,', '||cname,NULL)) ||
         max(decode(position,10,', '||cname,NULL)) ||
         max(decode(position,11,', '||cname,NULL)) ||
         max(decode(position,12,', '||cname,NULL)) ||
         max(decode(position,13,', '||cname,NULL)) ||
         max(decode(position,14,', '||cname,NULL)) ||
         max(decode(position,15,', '||cname,NULL)) ||
         max(decode(position,16,', '||cname,NULL)) columns
    from ( select substr( column_name, 1, 30 ) cname,
                  substr( table_name, 1, 30 ) table_name,
                  substr( index_name, 1, 30 ) index_name,
                  column_position position
             from user_ind_columns  ) user_ind_columns
   group by table_name, index_name ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
  and a.table_name = x.table_name
order by
  decode( b.table_name, NULL, '****', 'ok' )
  , x.num_rows desc nulls last

Selecionar tudo

9.2 oraprod:TFG> /

TABLE_NAME                       NUM_ROWS CRE_IND
------------------------------ ---------- --------------------------------------------------------------------------------------------
TB_EMBARQUE                      61651609 create index CE_EMBARQUE9_I on TB_EMBARQUE (CD_PROCESSO, SQ_PROCESSO, NU_ANO_PROCESSO);
TB_EMBARQUE                      61651609 create index CE_EMBARQUE5_I on TB_EMBARQUE (DS_UF_ORIGEM, CD_CIDADE_ORIGEM);
TB_RATEIO_MOVIMENTO                293500 create index RAMO_GRDE_FK_I on TB_RATEIO_MOVIMENTO (CD_GRUPO_DESPESA);
TB_COMISSAO                        259390 create index CE_COMISSAO1_I on TB_COMISSAO (CD_PESSOA, CD_ESTUDO, CD_ENDOSSO, CD_PARCELA);
TB_COMISSAO                        259390 create index CE_COMISSAO4_I on TB_COMISSAO (CD_GAP_ESTORNO, NU_ANO_GAP_ESTORNO);
TB_COMISSAO                        259390 create index CE_COMISSAO6_I on TB_COMISSAO (CD_MOVIMENTO_ESTORNO, NU_ANO_MOVIMENTO_ESTORNO);
TB_COMISSAO                        259390 create index CE_COMISSAO2_I on TB_COMISSAO (CD_PESSOA_COMISSAO);
TB_COMISSAO                        259390 create index CE_COMISSAO3_I on TB_COMISSAO (CD_GAP_COMISSAO, NU_ANO_GAP_COMISSAO);
TB_MOVIMENTO_FINANCEIRO            232620 create index TB_MOV_FIN_TB_BOLETO_FK_I on TB_MOVIMENTO_FINANCEIRO (CD_BOLETO);
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 19 visitantes