Blz ? Então, antes de tudo vc tem que ter em mente que essas "regras" que vc vê
em livros e na Documentação são coisas *** genéricas *** , os Autores tão
tentando emitir postulados que via de regra funcionem, sim sim ? NADA do que vc
ler nessas fontes é uma DETERMINAçÃO, é uma obrigação, é algo IMPERATIVO, ok ?
O que os autores tão tentando é colocar normas gerais, Mas entenda que essas
normas PODEM (e muitas vezes VÃO, mesmo) ser quebradas nalgum cenários
específico - não fosse assim nem seria necessária a nossa presença como DBAs,
se um conjunto de regras funcionasse SEMPRE em QUALQUER CASO, certinho ??
Isso dito, seguinte : a função básica de um índice no tocante à performance é
indicar que um determinado valor X está presente na linha A (ou de repente nas
linhas A, B, C, D, etc), aí pesquisando o índice o RDBMS encontra a indicação
física de qual/quais blocos do disco contém a(s) linha(s) com o valor desejado
e lê apenas esse(s) blocos....
Assim,fica Óbvio que se vc está restringindo o acesso apenas as linhas aonde
uma coluna contém um valor X (que é a função do WHERE, mas - NÂO ESQUEÇA - é o
que o GROUP BY x faz, vc só quer acessar registros onde o valor x tá presente,
né?), tá patente que muito CERTAMENTE o RDBMS vai poupar muito I/O acessando
pelo índice, já que o índice só contém o valor chave e a posição/bloco em disco
onde esse valor está...
Agora, se vc não tem WHERE, por DEFINIÇÃO vc está acessando a tabela toda,
sim sim ?? Sendo assim, o que que vc acha que é mais vantajoso pra performance
: ler uma a uma TODAS as posições do índice E para cada posição ler um dos
blocos da tabela ** OU **, já que tem que ler udo mesmo, já acessar a tabela
como um todo ??? Fazer DOIS I/Os completos (um no índice completo e na tabela
completa) OU apenas um I/O completo na tabela mesmo ??? Via de regra não tem o
que discutir, fazer um é mais vantajoso que fazer dois.... Sacou ???? E isso
sem contar que um full table scan (ie, ler a tabela inteira) é feito lendo
MÚLTIPLOS BLOCOS de uma vez só, ao passo que a leitura de um índice inteirinho
é feita UM bloquinho de cada vez.... Essas coisas estão EXTENSAMENTE
explicadas/demonstradas em muitas fontes,
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4433887271030
,
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968
e
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1277166300346684168
são apenas algumas das muitas refs...
A ** exceção ** a isso que falei é se o índice já contém TODINHA a informação
que vc precisa, sendo desnecessária a visita à tabela : num caso desses o RDBMS
** não ** precisaria ficar "pulando" entre o índice e a tabela, E como o índice
que conteria a info toda via de regra é muito menor , a leitiura completa do
índice deve demorar muito menos....
No seu caso específico imagino que isso de poder usar só o índice *** NÃO ***
vai acontecer, pois vc está escrevendo :
select l_returnflag, l_linestatus, sum(l_quantity) ...
Se vc tiver um índice em l_quantity ok, o RDBMS poderia obter a contagem só
lendo esse índice integralmente MAS note que vc precisa de ** outras **
informações (l_returnflag e l_linestatus) que não estariam no índice, assim eu
IMAGINO que no seu caso já que vc vai ter que ler a tabela inteira por causa
das informações não presentes no índice o RDBMS já aproveita e recupera tudo da
tabela mesmo, acessar a tabela toda MAIS o índice ia ser "duplicação" de
esforço...
====>>> E antes que vc pergunte : sim, em tese vc poderia ter um índice com **
todas ** as colunas que vc está buscando na SELECT, mas além do GIGANTESCO
overhead que ia ser manter um índice assim grande e complexo E dos limites
internos de um índice (coisas que por si só muitas vezes INVIABILIZAM a
idéia),há a questão da CARDINALIDADE e REPRESENTATIVIDADE - como eu disse, um
índice é Pensado para ser uma lista de valores possíveis junto com a posição em
disco na tabela onde o valor está, quanto mais valores diferentes vc tiver que
acessar pior vai ser o desempenho desse índice, via de regra...
Vc vai testar as possibilidades MAS na regra geral essa idéia não deve dar
certo, não..... Pra mim cv deveria é se ASSEGURAR que essas suas queries tão
lendo a tabela completa com o full table scan o mais Azeitado possível, lendo
de cada vez a maior qtdade possível de dados, nos discos mais rápidos.... E
pelo jeitão é algo tipo DW, então vc **** DEVERIA **** estar usando E abusando
de views materializadas , CACHE de resultados .... NÂO FAZ SENTIDO vc fazer
contagens a toda hora, o CERTO era vc fazer isso numa view materializada com
REFRESH ON COMMIT, aí AUTOMAGICAMENTE quando os dados mudam a view
materializada tem os resultados atualizados, POUPANDO o coitado do RDBMS de ter
que contar tudo de novo, não é não ???
[]s
Chiappa
---Em oracle_br@yahoogrupos.com.br, <neto...@gmail.com> escreveu:
Pessoal,
Tenho várias consultas como exemplo abaixo, e vou precisar executar em
bases com 100gb a 500gb brevemente.
------------------------------------------------------------------------
select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from lineitem
where
l_shipdate <= date '2005-12-01' - interval '117' day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
--------------------------------------------------------------------------
Vários autores (cito 2 exemplos: 1, 2 abaixo) falam que uma coluna boa
candidata a índice deve:
- aparecer na clausula WHERE.
- aparecer em clausulas de GROUP BY ou ORDER BY
- etc...
Mas pergunto, e as colunas que aparecem ANTES do WHERE, ou seja no
SELECT como as colunas com funções SUM, AVG e COUNT, como acima.
Pelo que entendi, um índice ajuda a encontar as linhas da tabela mais
rapidas, e com as linhas encontradas, nao precisaria de um indice para
fazer SUM, AVG, ou COUNT, seria isso a justificativa para os autores
não sugerirem criar índices para essas colunas com SUM, AVG e COUNT ?
e no caso de muitos dados para fazer o SUM, etc?
Mas então porque para o Group By e Order by é sugerido a criação de
índices, pois na verdade eles fazer de forma similar o que faz o SUM,
AVG e COUNT, ou seja eles somente manipulam (ordenação/agrupamento) os
dados e não ajudam a encontrar os mesmo no discos.
Na ref. 1 tem a seguinte afirmacao: " Não indexe colunas que aparecem
em Cláusulas WHERE com funções ou operadores.
Uma cláusula WHERE que usa uma função, diferente de MIN ou MAX ou um
operador com uma chave indexada, não é disponibilizado o caminho de
acesso utilizando o índice, exceto com índices baseados em função."
No entanto ele não fala nada de funcoes ANTES do WHERE. A minha
questão é compensa criar índices para colunas, se baseando nas colunas
que aparecem apos o SELECT?
Obs. Eu sei que criar índices demasiadamente causa gargalos em
atualizações, mas meu ambiente, é mais orientado a consultas com
poucas atualizações.
1- https://docs.oracle.com/cd/E18283_01/server.112/e16638.pdf
https://docs.oracle.com/cd/E18283_01/server.112/e16638.pdf (Pag. 371)
2- http://www.cs.toronto.edu/~alan/papers/icde00.pdf
http://www.cs.toronto.edu/~alan/papers/icde00.pdf (pag 1)
Qualquer ajuda é vinda.
[]`s Neto
