Utilidade de um índice multiatributo

Por exemplo, pegue uma tabela T com um índice nos atributos a e b. O índice é útil para a consulta:

select * from T where a='foo'

Há duas perguntas aí:

• Um índice pode (a,b)ser usado para esta consulta?

  A resposta para isso geralmente é sim. Talvez nem todos os bancos de dados tenham essa capacidade, mas a maioria dos principais tem o AFAIK. Substitua a seleção por select a,b from ...e o mecanismo não só pode usar o índice, mas também não acessar a tabela real para responder à consulta.

• O otimizador escolherá usar o índice?

  Isso vai depender do sistema de banco de dados e quanta informação o otimizador tem sobre os dados. Se puder determinar que a primeira coluna é "seletiva o suficiente", provavelmente a usará. Se não, provavelmente não.

Aqui está uma ilustração no Oracle XE 11g.

SQL> create table T
  2  as select object_name a, rownum b, rownum c
  3  from all_objects;
Table created.
SQL> create index T_ab on T(a,b);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T');
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from T where a = 'foo';

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    27 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    27 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | T_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

A all_objectsvisão contém informações sobre todos os objetos (tabelas, visões, procedimentos, etc.) presentes no banco de dados. Os object_namecampos não são únicos, mas não há muitas duplicatas (pelo menos neste banco de dados), de modo que o próprio campo inicial é muito seletivo.
O custo de fazer uma varredura de intervalo no índice e, em seguida, procurar a linha estimada por rowid será muito menor do que fazer uma varredura completa da tabela, portanto, o otimizador segue esse caminho.
Esta é uma situação e um plano bastante comuns, você provavelmente se deparará muito com isso (ou algo semelhante em outros mecanismos de banco de dados).

Agora, aqui está um cenário diferente em que o otimizador pode usar uma imagem detalhada do conteúdo real das colunas para otimizar as coisas de maneira diferente:

SQL> create table Q
  2  as select 'a' a, rownum b, rownum c
  3  from all_objects;
Table created.
SQL> create index Q_ab on Q(a,b);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'Q');
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from Q where a = 'a';

Execution Plan
--------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  | 18085 |   176K|    15   (7)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| Q    | 18085 |   176K|    15   (7)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select * from Q where a = 'z';

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| Q    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | Q_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

A tabela é intencionalmente completamente distorcida, a primeira coluna tem 'a'como único valor. Se o otimizador souber disso, ele poderá optar por caminhos diferentes com base no valor real da chave consultada, como visto acima.
Se 'a'for solicitado, usar o índice é uma má jogada - você precisa percorrer todo o índice e toda a tabela para obter todas as linhas, o que é (potencialmente muito) mais caro do que apenas digitalizar a tabela.
Se o valor solicitado não for 'a', a varredura do índice é muito mais eficiente, pois provavelmente não retornará nenhuma linha.

Aqui está algo talvez um pouco mais surpreendente: o índice on (a,b)pode realmente ser usado quando a wherecláusula filtra bsomente on.

SQL> select * from Q where b = 1000;

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| Q    |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN       | Q_AB |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Isso funciona porque a coluna inicial tem poucos valores distintos, mas a segunda coluna é essencialmente única. Pense nisso como o otimizador particionando o índice pela primeira coluna e fazendo uma pesquisa binária em cada partição. Isso será eficiente se o número de partições for pequeno e os outros critérios forem bastante seletivos. (ou seja, não funciona para a tabela Tneste exemplo.)

Sim, mas depende de qual chave de índice você pesquisa.

Pense nisso como uma daquelas velhas listas telefônicas de "páginas em branco". Em uma lista telefônica, as pessoas são ordenadas nas páginas na ordem Sobrenome, Nome. Isso significa que há dois componentes no índice da lista telefônica.

Se você está procurando por todas as pessoas com o sobrenome "Smith", basta encontrar o primeiro "Smith" (fácil de fazer, pois está em ordem), continue lendo até encontrar alguém que não seja um "Smith" .

Mas se você estiver procurando por todas as pessoas com o nome "William", terá dificuldades. Você terá que escanear cada entrada na lista telefônica, coletando respostas, mesmo que FirstName esteja no "índice" da lista telefônica.

Índices de banco de dados (conceitualmente) funcionam exatamente da mesma maneira.

Experiência PostgreSQL.

Temos tabela com 3 colunas

table test with columns
 id, first, second

então temos dois índices

first_second_idx (first, second)
first_idx (first)

então, se usarmos a declaração (1)

 select * from test where first = 'whatever';

o planejador de consulta deve usar o índice first_idx (o valor 'qualquer' é, por exemplo, 5% de todos os registros). É mais rápido porque o índice é menor, então há menos leitura.

Para consulta (2)

select * from test where first = 'whatever' and second = 'something'

é óbvio que o índice first_second_idx será usado

Mas se removermos first_idx em ambas as consultas, a plaina deve usar first_second_idx.

Se não houver nenhum índice para esta tabela, a varredura completa da tabela será acionada.

Portanto, se você tiver os dois tipos de consultas do aplicativo:

1. use apenas first_second_idx se
   • a velocidade da (1) consulta é gratificante
   • há muitas atualizações de inserções e o segundo índice retardará essas operações
   • teste de tabela tem muitos registros e espaço em disco e/ou memória (sempre é usado mais memória quando lido de ambos os índices) é importante
2. use ambos os índices se
   • velocidade para ambas as consultas é crucial
   • velocidade de atualização de inserção não é tão importante
   • espaço em disco e memória não é tão importante