Eu tenho a seguinte tabela:
CREATE TABLE action (action_id INT NOT NULL,
action_type_id INT NOT NULL,
action_date DATE NOT NULL);
CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, action_type_id);
ALTER TABLE action ADD CONSTRAINT PK_ACTION PRIMARY KEY (action_id)
USING INDEX IDX_ACTION_ACT_TYPE_ACT;
ALTER TABLE action ADD CONSTRAINT UQ_ACTION UNIQUE (action_id,action_type_id)
USING INDEX IDX_ACTION_ACT_TYPE_ACT;
-- I think the line below is irrelevant in the context of my question, but
-- in case I'm wrong...
ALTER TABLE action ADD CONSTRAINT FK_action_type_id FOREIGN KEY(action_type_id)
REFERENCES action_type(action_type_id);
-- Since action_type is a lookup added for normalization sake with no updates/deletes,
-- I don't see any value in having an index on FK column.
É uma tabela pai comum para muitas tabelas de detalhes que armazenam informações extras para uma ação específica. action_type_idfaz parte da restrição exclusiva para permitir que as tabelas de detalhes tenham FK para (action_id,action_type_id) e para impor que cada tabela de detalhes armazene apenas as informações que deveria armazenar.
Gostaria de saber se há alguma penalidade de desempenho por usar o mesmo índice em 2 restrições em comparação com a criação de 2 índices ... Na minha opinião, deveria se comportar melhor do que 2 índices, mas talvez esteja faltando alguma coisa. Estou usando o Oracle 10g, se isso importa.
Obrigado por suas respostas.
Como Nat observou, sua restrição UQ_ACTION duplica a restrição de chave primária. Com base no seu uso de FK nesta tabela, sua chave primária é (action_id, action_type_id). Como tal, é redundante e deve ser removido. Se você deseja que sua chave primária seja apenas action_id, então suas chaves estrangeiras devem referenciar apenas action_id.
Acredito que você estava correto ao modelar action_id como a chave primária. Nesse caso, altere as referências de seu filho. (Ter o ID do tipo de ação na chave estrangeira viola a forma normal e força informações redundantes nas tabelas de referência). As tabelas filhas de sua tabela de ações teriam o ID do tipo de ação em sua chave se se aplicassem a vários tipos.
Você pode querer um Reino Unido em action_id, mas não é necessário. Se você tiver um Reino Unido com um índice correspondente, eu inverteria a ordem no índice que contém (action_type_id, action_id). Isso permitirá uma recuperação rápida apenas por action_id e por action_type_id onde as estatísticas indicam que um índice é útil.
No entanto, para responder à sua pergunta nos casos em que um índice pode ser usado para várias restrições, não há penalidade. Na verdade, o oposto é o caso.
A manutenção de índices pode ser uma operação relativamente cara. Adicionar índices redundantes tem uma penalidade. Cada inserção, atualização ou exclusão que altera a coluna no índice redundante exigirá trabalho adicional para atualizar o índice.
Parece que você reconheceu o valor de não criar índices não utilizados. Embora comuns, os índices de chave estrangeira não são necessários em muitos casos. É uma boa ideia ter um índice se você excluir da tabela referenciada. Um índice substituto começando com a chave estrangeira pode resultar em consultas mais eficientes. Se você fizer pesquisas frequentes por action_type_id, o índice será útil. Pode ser mais útil ter um índice de várias colunas com outros valores usados para filtrar o conjunto de resultados.