Índices compostos: coluna mais seletiva primeiro?

Ao escolher a ordem das colunas do índice, a preocupação primordial é:

Existem predicados (de igualdade) contra esta coluna em minhas consultas?

Se uma coluna nunca aparecer em uma cláusula where, não vale a pena indexar(1)

OK, então você tem uma tabela e consultas em cada coluna. Às vezes mais de um.

Como você decide o que indexar?

Vejamos um exemplo. Aqui está uma tabela com três colunas. Um contém 10 valores, outro 1.000, os últimos 10.000:

create table t(
  few_vals  varchar2(10),
  many_vals varchar2(10),
  lots_vals varchar2(10)
);

insert into t 
with rws as (
  select lpad(mod(rownum, 10), 10, '0'), 
         lpad(mod(rownum, 1000), 10, '0'), 
         lpad(rownum, 10, '0') 
  from dual connect by level <= 10000
)
  select * from rws;

commit;

select count(distinct few_vals),
       count(distinct many_vals) ,
       count(distinct lots_vals) 
from   t;

COUNT(DISTINCTFEW_VALS)  COUNT(DISTINCTMANY_VALS)  COUNT(DISTINCTLOTS_VALS)  
10                       1,000                     10,000     

Estes são números deixados preenchidos com zeros. Isso ajudará a entender a compactação mais tarde.

Então você tem três consultas comuns:

select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  few_vals = '0000000001';

select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  lots_vals = '0000000001';

select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  lots_vals = '0000000001'
and    few_vals = '0000000001';

O que você indexa?

Um índice em apenas alguns_vals é apenas marginalmente melhor do que uma varredura completa da tabela:

select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  few_vals = '0000000001';

select * 
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));

-------------------------------------------------------------------------------------------  
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT     |          |      1 |        |      1 |00:00:00.01 |      61 |  
|   1 |  SORT AGGREGATE      |          |      1 |      1 |      1 |00:00:00.01 |      61 |  
|   2 |   VIEW               | VW_DAG_0 |      1 |   1000 |   1000 |00:00:00.01 |      61 |  
|   3 |    HASH GROUP BY     |          |      1 |   1000 |   1000 |00:00:00.01 |      61 |  
|   4 |     TABLE ACCESS FULL| T        |      1 |   1000 |   1000 |00:00:00.01 |      61 |  
-------------------------------------------------------------------------------------------

select /*+ index (t (few_vals)) */
       count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  few_vals = '0000000001';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));

-------------------------------------------------------------------------------------------------------------  
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                       |          |      1 |        |      1 |00:00:00.01 |      58 |  
|   1 |  SORT AGGREGATE                        |          |      1 |      1 |      1 |00:00:00.01 |      58 |  
|   2 |   VIEW                                 | VW_DAG_0 |      1 |   1000 |   1000 |00:00:00.01 |      58 |  
|   3 |    HASH GROUP BY                       |          |      1 |   1000 |   1000 |00:00:00.01 |      58 |  
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |   1000 |   1000 |00:00:00.01 |      58 |  
|   5 |      INDEX RANGE SCAN                  | FEW      |      1 |   1000 |   1000 |00:00:00.01 |       5 |  
-------------------------------------------------------------------------------------------------------------

Portanto, é improvável que valha a pena indexar por conta própria. Consultas em Lots_vals retornam algumas linhas (apenas 1 neste caso). Portanto, definitivamente vale a pena indexar.

Mas e as consultas em ambas as colunas?

Você deve indexar:

( few_vals, lots_vals )

OU

( lots_vals, few_vals )

Pergunta capciosa!

A resposta é nenhum dos dois.

Claro que few_vals é uma string longa. Então você pode obter uma boa compressão dele. E você (pode) obter uma varredura de salto de índice para as consultas usando (few_vals, Lots_vals) que possuem apenas predicados em Lots_vals. Mas não estou aqui, embora tenha um desempenho notavelmente melhor do que uma varredura completa:

create index few_lots on t(few_vals, lots_vals);

select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  lots_vals = '0000000001';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));

-------------------------------------------------------------------------------------------  
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT     |          |      1 |        |      1 |00:00:00.01 |      61 |  
|   1 |  SORT AGGREGATE      |          |      1 |      1 |      1 |00:00:00.01 |      61 |  
|   2 |   VIEW               | VW_DAG_0 |      1 |      1 |      1 |00:00:00.01 |      61 |  
|   3 |    HASH GROUP BY     |          |      1 |      1 |      1 |00:00:00.01 |      61 |  
|   4 |     TABLE ACCESS FULL| T        |      1 |      1 |      1 |00:00:00.01 |      61 |  
-------------------------------------------------------------------------------------------  

select /*+ index_ss (t few_lots) */count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  lots_vals = '0000000001';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));

----------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  
----------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                       |          |      1 |        |      1 |00:00:00.01 |      13 |     11 |  
|   1 |  SORT AGGREGATE                        |          |      1 |      1 |      1 |00:00:00.01 |      13 |     11 |  
|   2 |   VIEW                                 | VW_DAG_0 |      1 |      1 |      1 |00:00:00.01 |      13 |     11 |  
|   3 |    HASH GROUP BY                       |          |      1 |      1 |      1 |00:00:00.01 |      13 |     11 |  
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |      1 |      1 |00:00:00.01 |      13 |     11 |  
|   5 |      INDEX SKIP SCAN                   | FEW_LOTS |      1 |     40 |      1 |00:00:00.01 |      12 |     11 |  
----------------------------------------------------------------------------------------------------------------------

Você gosta de jogos de azar? (2)

Portanto, você ainda precisa de um índice com Lots_vals como a coluna inicial. E, pelo menos neste caso, o índice composto (poucos, muitos) faz a mesma quantidade de trabalho que apenas (muitos)

select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  lots_vals = '0000000001'
and    few_vals = '0000000001';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));

-------------------------------------------------------------------------------------------------------------  
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                       |          |      1 |        |      1 |00:00:00.01 |       3 |  
|   1 |  SORT AGGREGATE                        |          |      1 |      1 |      1 |00:00:00.01 |       3 |  
|   2 |   VIEW                                 | VW_DAG_0 |      1 |      1 |      1 |00:00:00.01 |       3 |  
|   3 |    HASH GROUP BY                       |          |      1 |      1 |      1 |00:00:00.01 |       3 |  
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |      1 |      1 |00:00:00.01 |       3 |  
|   5 |      INDEX RANGE SCAN                  | FEW_LOTS |      1 |      1 |      1 |00:00:00.01 |       2 |  
-------------------------------------------------------------------------------------------------------------  

create index lots on t(lots_vals);

select /*+ index (t (lots_vals)) */count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  lots_vals = '0000000001'
and    few_vals = '0000000001';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));

----------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  
----------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                       |          |      1 |        |      1 |00:00:00.01 |       3 |      1 |  
|   1 |  SORT AGGREGATE                        |          |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |  
|   2 |   VIEW                                 | VW_DAG_0 |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |  
|   3 |    HASH GROUP BY                       |          |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |  
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |  
|   5 |      INDEX RANGE SCAN                  | LOTS     |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |  
----------------------------------------------------------------------------------------------------------------------  

Haverá casos em que o índice composto economiza 1-2 IOs. Mas vale a pena ter dois índices para essa economia?

E há outro problema com o índice composto. Compare o fator de agrupamento para os três índices, incluindo LOTS_VALS:

create index lots on t(lots_vals);
create index lots_few on t(lots_vals, few_vals);
create index few_lots on t(few_vals, lots_vals);

select index_name, leaf_blocks, distinct_keys, clustering_factor
from   user_indexes
where  table_name = 'T';

INDEX_NAME  LEAF_BLOCKS  DISTINCT_KEYS  CLUSTERING_FACTOR  
FEW_LOTS    47           10,000         530                
LOTS_FEW    47           10,000         53                 
LOTS        31           10,000         53                 
FEW         31           10             530    

Notice that the clustering factor for few_lots is 10x higher than for lots and lots_few! And this is in a demo table with perfect clustering to begin with. In real world databases the effect is likely to be worse.

So what's so bad about that?

The clustering factor is one of the key drivers determining how "attractive" an index is. The higher it is, the less likely the optimizer is to choose it. Particularly if lots_vals aren't actually unique, but still normally have few rows per value. If you're unlucky this could be enough to make the optimizer think a full scan is cheaper...

OK, so composite indexes with few_vals and lots_vals only have edge case benefits.

What about queries filtering few_vals and many_vals?

Single columns indexes only give small benefits. But combined they return few values. So a composite index is a good idea. But which way round?

If you place few first, compressing the leading column will make that smaller

create index few_many on t(many_vals, few_vals);
create index many_few on t(few_vals, many_vals);

select index_name, leaf_blocks, distinct_keys, clustering_factor 
from   user_indexes
where  index_name in ('FEW_MANY', 'MANY_FEW');

INDEX_NAME  LEAF_BLOCKS  DISTINCT_KEYS  CLUSTERING_FACTOR  
FEW_MANY    47           1,000          10,000             
MANY_FEW    47           1,000          10,000   

alter index few_many rebuild compress 1;
alter index many_few rebuild compress 1;

select index_name, leaf_blocks, distinct_keys, clustering_factor 
from   user_indexes
where  index_name in ('FEW_MANY', 'MANY_FEW');

INDEX_NAME  LEAF_BLOCKS  DISTINCT_KEYS  CLUSTERING_FACTOR  
MANY_FEW    31           1,000          10,000             
FEW_MANY    34           1,000          10,000      

With fewer different values in the leading column compresses better. So there's marginally less work to read this index. But only slightly. And both are already a good chunk smaller than the original (25% size decrease).

And you can go further and compress the whole index!

alter index few_many rebuild compress 2;
alter index many_few rebuild compress 2;

select index_name, leaf_blocks, distinct_keys, clustering_factor 
from   user_indexes
where  index_name in ('FEW_MANY', 'MANY_FEW');

INDEX_NAME  LEAF_BLOCKS  DISTINCT_KEYS  CLUSTERING_FACTOR  
FEW_MANY    20           1,000          10,000             
MANY_FEW    20           1,000          10,000   

Now both indexes are back to the same size. Note this takes advantage of the fact there's a relationship between few and many. Again it's unlikely you'll see this kind of benefit in the real world.

So far we've only talked about equality checks. Often with composite indexes you'll have an inequality against one of the columns. e.g. queries such as "get the orders/shipments/invoices for a customer in the past N days".

If you have these kinds of queries, you want the equality against the first column of the index:

select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  few_vals < '0000000002'
and    many_vals = '0000000001';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));

-------------------------------------------------------------------------------------------------------------  
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                       |          |      1 |        |      1 |00:00:00.01 |      12 |  
|   1 |  SORT AGGREGATE                        |          |      1 |      1 |      1 |00:00:00.01 |      12 |  
|   2 |   VIEW                                 | VW_DAG_0 |      1 |     10 |     10 |00:00:00.01 |      12 |  
|   3 |    HASH GROUP BY                       |          |      1 |     10 |     10 |00:00:00.01 |      12 |  
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |     10 |     10 |00:00:00.01 |      12 |  
|   5 |      INDEX RANGE SCAN                  | FEW_MANY |      1 |     10 |     10 |00:00:00.01 |       2 |  
-------------------------------------------------------------------------------------------------------------  

select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from   t
where  few_vals = '0000000001'
and    many_vals < '0000000002';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));

----------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  
----------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                       |          |      1 |        |      1 |00:00:00.01 |      12 |      1 |  
|   1 |  SORT AGGREGATE                        |          |      1 |      1 |      1 |00:00:00.01 |      12 |      1 |  
|   2 |   VIEW                                 | VW_DAG_0 |      1 |      2 |     10 |00:00:00.01 |      12 |      1 |  
|   3 |    HASH GROUP BY                       |          |      1 |      2 |     10 |00:00:00.01 |      12 |      1 |  
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |      2 |     10 |00:00:00.01 |      12 |      1 |  
|   5 |      INDEX RANGE SCAN                  | MANY_FEW |      1 |      1 |     10 |00:00:00.01 |       2 |      1 |  
----------------------------------------------------------------------------------------------------------------------  

Notice they're using the opposite index.

TL;DR

• Columns with equality conditions should go first in index.
• If you have multiple columns with equalities in your query, placing the one with the fewest different values first will give the best compression advantage
• While index skip scans are possible, you need to be confident this will remain a viable option for the foreseeable future
• Composite indexes including near-unique columns give minimal benefits. Be sure you really need to save the 1-2 IOs

1: In some cases it may be worth including a column in an index if this means all the columns in your query are in the index. This enables an index only scan, so you don't need to access the table.

2: If you're licensed for Diagnostics and Tuning, you could force the plan to a skip scan with SQL Plan Management

ADDEDNDA

PS - the docs you've quoted there are from 9i. That's reeeeeeally old. I'd stick with something more recent

O mais seletivo primeiro é útil apenas quando esta coluna está na cláusula WHERE real.

Quando o SELECT é por um grupo maior (menos seletivo) e possivelmente por outros valores não indexados, um índice com colunas menos seletivas ainda pode ser útil (se houver um motivo para não criar outro).

Se houver uma tabela ADDRESS, com

RUA DA CIDADE DO PAÍS, outra coisa...

A indexação de RUA, CIDADE, PAÍS produzirá as consultas mais rápidas com um nome de rua. Mas, consultando todas as ruas de uma cidade, o índice será inútil e a consulta provavelmente fará uma varredura completa da tabela.

A indexação de PAÍS, CIDADE, RUA pode ser um pouco mais lenta para ruas individuais, mas o índice pode ser usado para outras consultas, selecionando apenas por país e/ou cidade.

Há mais elementos de consulta que contribuem para a decisão final sobre com o que um índice composto deve começar e/ou conter além da seletividade da coluna.

por exemplo:

1. que tipo de operador de consulta está sendo usado: Se as consultas tiverem operadores como
   ">, >=, <, <="
2. Quantas linhas reais são esperadas como resultado da consulta: o resultado da consulta será a maioria das linhas da tabela.
3. Se alguma função está sendo usada na coluna da tabela durante a cláusula Where: Se a consulta tiver qualquer função UPPER, LOWER, TRIM, SUBSTRING usada na coluna que está sendo usada na condição WHERE.

ainda para manter a conversa relevante, minha resposta abaixo se aplica à seguinte situação:

1. "90% do tipo de consultas em uma determinada tabela tem Cláusula WHERE com operador ="
2. "no máximo a consulta está retornando 10% do total de linhas na tabela como resultado"
3. "nenhum tipo de função está sendo usado na coluna da tabela na cláusula WHERE"
4. "na maioria das vezes, as colunas na cláusula WHERE usadas são principalmente do tipo número,
   string"

Na minha experiência, é sobre ambos que o DBA deve estar atento.

Vamos imaginar que a única regra está sendo aplicada:

1) Se eu criar um índice com a coluna mais seletiva sendo a primeira, mas essa coluna não for realmente usada pela maioria das consultas nessa tabela, não será útil para o mecanismo de banco de dados.

2) Se eu criar um índice com a coluna mais usada em uma consulta sendo a primeira no índice, mas a coluna tiver baixa seletividade, o desempenho da minha consulta também não será bom.

Vou listar as colunas que são mais usadas em 90% das consultas à tabela. Em seguida, coloque-os apenas na ordem de maior cardinalidade para menor cardinalidade.

Usamos índices para melhorar o desempenho da consulta de leitura e esse fluxo de trabalho (tipos de consulta de leitura) deve orientar apenas a criação do índice. Na verdade, à medida que os dados crescem (bilhões de linhas), o índice compactado pode economizar armazenamento, mas com certeza prejudica o desempenho da consulta de leitura.

In theory the most selective column yields the fastest search. But at work I just stumbled on a situation where we have a composite index of 3 parts with the most selective part first. (date, author, publishing company lets say, in that order, table monitors thumbs up on posts) and I have a query that uses all 3 parts. Mysql defaults to using the author onlny index skipping the composite index containing company and date despite them being present in my query. I used force index to use the composite and the query actually ran slower. Why did that happen? I shall tell you:

I was selecting a range on the date, so despite the date being highly selective, the fact that we are using it for range scans(even though the range is relatively short, 6 months out of 6 years of data) made the composite harmful for mysql. To use the composite in that particular case, mysql has to grab all articles written since new years then dive into who the author is, and given that the author has not written that many articles compared to other authors, mysql preferred to just find that author.

In another case the query ran much much faster on the composite, the case was when an author was hugely popular and owned most of the records, sorting by date made sense. But mysql did not auto detect that case, I had to force index... So you know, it varies. Range scans could render your selective column useless. The distribution of the data could make cases where columns are more selective for different records...

What I would do differently is shift the date (which again, in theory is the most selective) to the right, since I know I will be performing a range scan on it now and that makes a difference.

Casos diferentes para situações diferentes. Conheça seu objetivo; em seguida, crie seus índices e execute planos de explicação para cada um e você terá sua melhor resposta para sua situação.

From Column order in Index on Ask Tom:

So, the order of columns in your index depends on HOW YOUR QUERIES are written. You want to be able to use the index for as many queries as you can (so as to cut down on the over all number of indexes you have) -- that will drive the order of the columns. Nothing else (selectivity of a or b does not count at all).

Agree, that we have to order columns based on where clause, but the statement "(selectivity of a or b does not count at all)" is not correct.)". The most selective columns should be leading if it is satisfied first role ("where clause")