Qual é o caso de uso pretendido para colunas virtuais com um índice baseado em função?
A razão pela qual eu pergunto (novato):
Eu tenho explorado opções para pré-computar colunas em uma tabela. Por exemplo, em uma tabela de estradas, preencha um campo usando o número da zona de interseção espacial de uma tabela de zonas. Mais informações aqui: Opções para campos de computação.
Minha experiência é que as consultas espaciais geralmente são lentas — seja usando o tipo de dados/funções SDO_GEOMETRY da Oracle ou usando um tipo de objeto definido pelo usuário, como o tipo de dados/funções ST_GEOMETRY da Esri. Então, eu definitivamente quero pré-computar esse cálculo — para evitar fazer cálculos caros constantemente toda vez que a consulta for usada.
No início, fiquei tentado a criar uma coluna virtual com um índice baseado em função. Mas agora (com a entrada de outros), estou começando a me perguntar se pré-computar uma coluna usando um índice baseado em função pode não ser o uso correto para FBIs. Em vez disso, estou querendo saber se simplesmente calcular um campo na tabela usando um gatilho pode ser uma opção melhor. (Estou ciente de que as visualizações materializadas também são uma opção. Mas não tenho privilégios CREATE MATERIALIZED VIEW.)
Então, para encurtar a história, suspeito que uma coluna virtual e um FBI não sejam apropriados para o meu caso de uso. Com isso dito, qual é o caso de uso certo para uma coluna virtual e um FBI?
As colunas virtuais não pré-computam nada por conta própria, você está apenas armazenando a expressão nos metadados. Se você planeja ter filtros para essas expressões, talvez queira uma coluna virtual por alguns motivos - você não precisa escrever a expressão inteira toda vez, o Oracle reunirá estatísticas sobre a coluna para você obter planos melhores ( se suas consultas forem complicadas o suficiente para que essa cardinalidade importe).
Um índice (baseado em função) na coluna virtual torna a filtragem da expressão ainda mais rápida, pois a expressão agora está indexada para que você possa fazer pesquisas rápidas (é um índice). Você não precisa criar a coluna virtual também - você pode apenas criar o índice baseado em função contra a expressão, mas isso significa que você tem que digitar toda a expressão novamente a cada vez (Oracle é bastante tolerante com a forma como você digita ). Você ainda obtém os benefícios das estatísticas, pois o índice terá estatísticas e, em segundo plano, haverá uma coluna invisível que pode ter estatísticas contra ele (embora eu acredite que o padrão é que isso não aconteça).
Se você estiver fazendo isso, tome cuidado para que sua expressão seja determinística, o resultado deve mudar apenas com base na linha. Isso também inclui coisas como parâmetros NLS que podem fornecer resultados diferentes. Houve uma correção de bug algumas versões atrás em que o Oracle desativou a indexação em expressões regex porque elas podem depender das configurações de idioma; se você forçar isso, poderá acabar com dados corrompidos.
Os gatilhos podem funcionar, mas perdi a conta do número de vezes que vi uma suposição sendo feita em um gatilho que acabou causando corrupção. As visualizações materializadas podem permitir que você tenha expressões ainda mais complexas sendo indexadas, mas você precisa armazenar todo o conjunto de resultados e os índices adicionais novamente. Você também precisará certificar-se de que pode atualizar a visualização materializada adequadamente, atualizações rápidas ficam mais difíceis à medida que você adiciona complexidade.