A sua pergunta foi se é viável indexar colunas que não fazem parte do WHERE (ou de outras cáusulas de filtro/agrupação/restrição de linhas, como GROUP BY e ANALYTICS) : a primeira parte da minha resposta se mantém, ie, se vc não tem WHERE ** POR DEFINIÇÃO ** vc VAI TER QUE acessar a fonte completa dos dados, então o uso básico de índices , que é recuperar via índice fisicamente ordenado o endereço em disco de algumas poucas linhas da tabela e acessar apenas esses blocos, tá IMPOSSIBILITADO....
Um efeito ** SECUNDÁRIO ** é que , VIA DE REGRA, o índice é muito menor do que a tabela, então nesse tipo de acesso completo *** PODE SER *** que seja mais performático se fazer um FULL INDEX SCAN (ler o índice todinho) no conjunto de blocos menor do índice do que fazer o FULL TABLE SCAN (ler os blocos todos da tabela)... Notar que eu disse *** PODE ***, por duas razões : a) dependendo do tipo de acesso (digamos, se é necessário agrupar/achar o maior valor em todo o índice) o INDEX SCAN pode necessitar ser feito via single-block reads, via de regra MUITO menos eficientes do que os MULTIBLOCK I/Os feito pelo FULL TABLE SCAN e b) ao contrário das tabelas, onde praticamente *** TODOS *** os blocos não-vazios contém dados, num índice vc tem ** DIVERSOS ** blocos que não contem dados, e portanto após o SCAN de cada extent (se for se for I/O multiblock) os blocos lidos tem que serem PESQUISADOS/analisados para ver se contém dados ou não., E se for I/O single os blocos sem dados tem que serem pulados percorrendo a estrutura do índice.... A estrutura de um índice é uma árvore, onde o bloco "mestre" (root) contem info geral, e CADA grupo de valores possíveis está dividido num ramo, iniciado por blocos "tronco" (branch blocks), que registram que para os valores tal a qual os dados finais estão no bloco 'folha' (LEAF) tal... Dá um look na imagem da clássica apresentação https://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf : neste exemplo temos o bloco root é o bloco zero, e ele mostra que para encontrar os valores a partir de NULL até imediatamente antes de 'MOVE' tenho que ir pro branch block B1, e pra encontrar de 'MOVE' em diante tenho que ir pro branch block B2. Uma vez que li o branch block B1, nele estão as refs para os LEAF blocks (os blocos de dados 'reais') que contém cada valor que eu quero : se eu precisasse acessar o valor ACDC seria o leaf block L1.... Neste cenário completamente SIMPLIFICADO, que só serve de exemplo, se o RDBMS acessasse via I/O multiblock (o chamado FAST FULL INDEX SCAN, na nomenclatura Oracle) eu teria (desconsiderando o root block) apenas DOIS blocos a desconsiderar, o B1 e o B2 - na vida real isso pode não ser assim... ==> Para escolher entre INDEX SCAN (single-block) versus FAST FULL INDEX SCAN (multiblock) o RDBMS tenta fazer a melhor estimativa do esforço de balancear/mensurar o ganho ao se ler múltiplos blocks de uma só vez contra o overhead de pular blocos sem dados, mas nem sempre consegue fazer isso adequadamente.... Então aí vem as suas respostas : "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." ==> FIQUE CLARO, textos que falam isso estão se refereindo a buscas FILTRADAS, ie, vc tem uma condição de WHERE, um Grupo, alguma coisa que diga 'eu NÂO vou precisar ler a tabela inteirinha, só os registros que tenham o valor tal' : quando vc escreve SUM(colunaqualquer) sem indicar nenhum filtro, POR DEFINIÇÃO vc VAI TER que acessar os regsitros TODINHOS para recuperar TODOS os valores contidos em COLUNAQUALQUER para poder os somar, tá claro ? Seja RESTRITIVO ou não o valor contido em COLUNAQUALQUER, se vc não filtrar por ele de nada adianta.... Aí o uso básico do índice ficou IMPOSSIBILITADO... Como espero ter deixado CLARO acima, o benefício secundário de um índice (ie, de via de regra ele é MENOR do que a tabela, então a leitura completa dele TALVEZ custe menos) não é garantido de sobrepujar a performance do FULL TABLE SCAN, vc vai ter que testar e ver.... *** IMPORTANTE *** : notar que estou falando aqui de casos genéricos, casos deviantes mas que PODEM acontecer (por exemplo, índice com NULL permitido e que contém montes de NULOS e vc só quer acessar os não-nulos) PODEM (e muitas vezes Vão) mudar COmpletamente esse tipo de comparação, EM ESPECIAL se vc usar índices bitmap e/ou índice de função indexando apenas os não-nulos.... "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?" ==> Como eu disse acima, dependendo do tipo de acesso pode ser exigido esforço ADICIONAL num INDEX FULL SCAN e ele PODE ou NÃO ser feito com leitura multiblock , via de regra MUITO mais eficiente do que leitura single-block.... "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." ==> Me parece aqui que vc está com a impressão que o FULL TABLE SCAN é *** sempre *** o Vilão da má-performance, e que QUALQUER tipo de acesso via índice é "melhor" que FTS : como espero ter demonstrado, NEM SEMPRE isso é o caso, seja pelo efeito de o RDBMS ter que ficar "pulando" de um bloco do indice para um bloco da tabela e depois de volta pro indice e depois de volta da tabela (caso em que a info toda não pode ser extraída só do índice), QUANTO pelo caso do I/O multibloco do FTS sem 'pular' nada ser eventualmente mais eficiente do que o acesso full no índice..... Além das URLs que te passei, olhe https://www.percona.com/blog/2012/11/23/full-table-scan-vs-full-index-scan-performance/ , https://richardfoote.wordpress.com/2008/05/12/index-scan-or-full-table-scan-the-magic-number-magic-dance/ , http://allthingsoracle.com/investigating-indexes/ e http://www.toadworld.com/platforms/oracle/w/wiki/11001.oracle-b-tree-index-from-the-concept-to-internals para algumas refs... Então SIM, crie o índice , extraia o plano REAL (nada de explain, plano REAL com TODAS as estatísticas de execução!!) e se eventualmente não obter plano acessando o índice em full coloque um HINT e reteste, comparando a performance ..... Como eu disse, VIA DE REGRA se o RDBMS tiver estatísticas adequadas ele consegue fazer comparação razoável do custo envolvido em ler em full o índice VERSUS o eventual benefício do FTS multibloco, mas dado que nem sempre isso acontece - o CBO é um software que tenta chegar num caminho determinístico com Informações incompletas - quase NUNCA é viável ele realmente acessar todos os indíces completamente para comparar custos, por isso as Estatísticas que servem de amostra... []s Chiappa
