Início / dba / Perguntas / 248884
Accepted
eckes
Asked: 2019-09-17 07:23:26 +0800 CST

Oracle DISTINCT com colunas NULLable

  • 772

Há um problema de longa data(?) com o Oracle que, se você quiser usar um DISTINCT, selecione em uma coluna que possui um índice B*-Tree, mas é anulável, que o índice não é usado. (Como outras respostas sugerem, isso é verdade mesmo se houver uma restrição de verificação adicionada após o fato (sem nulos presentes)).

Existem várias maneiras de contornar isso (incluindo usar um índice BITMAP ou adicionar uma segunda coluna NON NULL ou constante ao índice). Porém acabei de notar que se eu fizer o SELECT DISTINCT com WHERE NOT NULL, o Oracle consegue usar o índice (index fast full scan).

Minha pergunta: desde qual versão do Oracle esse comportamento está presente, é confiável (quando o índice de duas colunas é preferido) e por que não é mencionado com mais frequência (por exemplo , esta boa resposta não o menciona)?

Pequeno reprodutor

drop table SCOTT.T;
-- will not work with short rows (SELECT OWNER,SUBOBJECT_NAME ...)
create table SCOTT.T AS SELECT * FROM ALL_OBJECTS;
create index SCOTT.IDX_T_OWNER on SCOTT.T(OWNER); -- NOT NULL
-- (subobject_name,1) or (subobject_name,namespace) is NULL, NOT NULL
create index SCOTT.IDX_T_SUBOBJ on SCOTT.T(subobject_name); -- NULL
exec  dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'T', cascade=>true );
desc SCOTT.T;

set autotrace on explain
-- fast index scan:
select distinct OWNER from SCOTT.T;
-- full table scan:
select distinct SUBOBJECT_NAME from SCOTT.T;
-- fast index scan:
select distinct SUBOBJECT_NAME from SCOTT.T where SUBOBJECT_NAME IS NOT NULL;

Aparência (em 18c) semelhante a:

select distinct subobject_name from T;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   715 |  1430 |   436   (3)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |   715 |  1430 |   436   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    | 81426 |   159K|   428   (1)| 00:00:01 |
---------------------------------------------------------------------------


select distinct subobject_name from T where SUBOBJECT_NAME is not null;

------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   624 |  1248 |     5  (20)| 00:00:01 |
|   1 |  HASH UNIQUE          |              |   624 |  1248 |     5  (20)| 00:00:01 |
|*  2 |   INDEX FAST FULL SCAN| IDX_T_SUBOBJ |  1459 |  2918 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------
oracle index

1 respostas

  1. Best Answer

    desde qual versão do Oracle esse comportamento está presente?

    Eu não tenho idéia, mas espero que esteja lá por um loooongo tempo.

    é confiável?

    100% confiável

    quando o índice de duas colunas é preferido?

    Quando você deseja trabalhar com registros nulos também.

    select distinct SUBOBJECT_NAME from SCOTT.T;pode usar o seguinte índice

    create index SCOTT.IDX_T_SUBOBJ on SCOTT.T(1, subobject_name);

    E por que não é mencionado com mais frequência (por exemplo, esta boa resposta não a menciona)?

    Toda a lógica fica bem clara quando você sabe que os índices não contêm registros totalmente nulos e que as restrições não são usadas pelo otimizador (como parece ser o caso).

    Se o seu índice não contiver nulos e você solicitar distinct na coluna anulável, verá que esse índice não pode ser usado (faltam nulos). Se você selecionar where col is not null, o índice poderá ser usado (todos os valores não nulos estarão lá).

    • 1

relate perguntas