Chiappa, tks pela resposta. Mas esse caso que mostrei concordo que sendo uma tabela somente, um indice para fazer o somatorio nao viria a trazer beneficios, pois tem varias outras colunas da tabela que deveriam ser acessadas, e por custo, seria mais barato um "FUll table Scan". Mas tenho muitas outras queries, que envolvem muitas tabelas, com 3 tabelas realmente grandes: lineitem, Orders e partsupp. Da para ver que os predicados sao bem restritivos, e segundo regras basicas de tuning, se voce tem predicados restritivos (retorno entre 5 -10%) ai compensaria utilizar indices. Bom o que acham, olhando a primeira query, o campo l_extendedprice e l_discount, sera que um indice em cada coluna ou multiplo ali, traria beneficio, pensando que o predicado (where) seria bem restritivo?
E na query 2, aquele select interno " Select min(ps_supplycost)" veja que pega 1 campo somente, e o FROM seria em 4 tabelas, neste caso um indice para pegar o valor minimo, seria uma boa aposta, o que acham? Estou batendo nessa tecla, pois vejo somente indicacoes para criacao de indices em coluna que aparecem no WHERE, mas se voce tem todas as chaves num indice, seria perfeitamente possivel fazer a soma de uma campo, sem acesar a tabela, somente utilizando o indice e isso parece que muitos deixam passar desapercebido. Essas queries sao de um benchmark publico que estou usando ( http://www.tpc.org/tpch). ---------------------------------------------------------------------- select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01' and o_orderdate < date '1995-01-01' + interval '1' year group by n_name order by revenue desc ---------------------------------- select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from part, supplier, partsupp, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 25 and p_type like '%STEEL' and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE' and ps_supplycost = ( select min(ps_supplycost) from partsupp, supplier, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE' ) order by s_acctbal desc, n_name, s_name, p_partkey []`s Neto Em 23 de abril de 2017 18:45, jlchia...@yahoo.com.br [oracle_br] < oracle_br@yahoogrupos.com.br> escreveu: > > > 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 (Pag. 371) > 2- http://www.cs.toronto.edu/~alan/papers/icde00.pdf (pag 1) > > Qualquer ajuda é vinda. > []`s Neto > > >
