Início / dba / Perguntas / 48109
Accepted
haki
Asked: 2013-08-14 23:01:35 +0800 CST

Por que o oracle não está usando um índice para consulta distinta?

  • 772

Este é o cenário

SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true)

PL/SQL procedure successfully completed.

SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / 1024 MB , BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('TM', 'TM_LD_IX');

SEGMENT_NAME            SEGMENT_TYPE        MB        BLOCKS
------------------------------------------ ---------- ----------
TM                      TABLE                 296      37888
TM_LD_IX                INDEX                  46       5888

SQL> select index_name , column_name from user_ind_columns where index_name = 'TM_LD_IX';

INDEX_NAME   COLUMN_NAME
------------ ------------------------------
TM_LD_IX     LD

SQL> explain plan for select distinct LD from TM;

Explained.

SQL> @ex

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4241255022

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   1 |  HASH UNIQUE       |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   2 |   TABLE ACCESS FULL| TM              |  2549K|    14M|  7486   (3)| 00:01:30 |
--------------------------------------------------------------------------------------

9 rows selected.

SQL> explain plan for select /*+ index(x , TM_LD_IX) */ distinct LD from TM x;

Explained.

SQL> @ex

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4241255022

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   1 |  HASH UNIQUE       |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   2 |   TABLE ACCESS FULL| TM              |  2549K|    14M|  7486   (3)| 00:01:30 |
--------------------------------------------------------------------------------------

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Como você pode ver, o oracle não está usando o índice LDe escolhe uma varredura completa da tabela. Não consigo nem fazê-lo usar o índice com um hist.

Na consulta simples acima, eu esperaria uma varredura completa rápida do índice de arquivos TM_LD_IX. my db_file_multiblock_read_countestá definido como 32, então estou esperando um custo de cerca de 5888/32 = 184 (usando o índice, também posso economizar o custo de um hash exclusivo).

Então, o que estou perdendo aqui?

oracle optimization

1 respostas

  1. Best Answer

    A razão para esse comportamento é que as linhas em que LD é NULL não podem ser encontradas no índice. Portanto, o Oracle precisa verificar a tabela completa. Se a tabela for criada com LD como uma coluna NOT NULL, o otimizador usará essas informações e fará um INDEX FAST FULL SCAN. Se você adicionar uma restrição "CHECK(LD is not null)" à tabela que não tem NOT NULL definido para a coluna LD, o otimizador não usará as informações fornecidas pela restrição e fará uma varredura completa da tabela novamente, mesmo se você lhe deu uma dica. Jonathan Lewis escreveu sobre esse comportamento.

    Os scripts a seguir demonstram esse comportamento para Oracle 11.2.0.3.0

    *create_table.sql* insere dados na tabela e cria índices e estatísticas

    desative o rastreamento automático
drop table objetos
/
criar objetos de tabela (
  número de object_id,
  proprietário varchar2(30),
  object_name varchar2(128),
  object_type varchar2(19)
)
/

inserir em objetos (
  object_id,
  proprietário,
  object_name,
  Tipo de objeto
)
selecionar
  object_id,
  proprietário,
  object_name,
  Tipo de objeto
de dba_objects
/


criar índice idx_object_id em objects(object_id);

exec dbms_stats.gather_table_stats(user,'objects', cascade=>true)

    Agora execute o seguinte script:

    saída de carretel
desativar o feedback
definir tamanho da linha 300
ativar o trimout
ligar o carretel

@criar a tabela
definir autotrace traceonly explicar

pronto
prompt 1. planeje a consulta sem restrições:
selecione object_id distinto
de objetos;
rem -------------------------------------------------- --

@criar a tabela
alterar objetos da tabela adicionar restrição nn_object_id check(object_id não é nulo) validar;
definir autotrace traceonly explicar

pronto
prompt 2. planejar a consulta com restrição de verificação
selecione object_id distinto
de objetos;
rem -------------------------------------------------- --

@criar a tabela
alterar objetos de tabela modificar object_id não nulo;
definir autotrace traceonly explicar

pronto
prompt 3.plano para consulta com coluna NOT NULL
selecione object_id distinto
de objetos;
rem -------------------------------------------------- --

@criar a tabela
criar índice de bitmap bidx_object_type em objetos (object_type)
/
definir autotrace traceonly explicar

pronto
prompt 4.plan para consulta com índice de bitmap
selecione object_type distinto
de objetos;
rem -------------------------------------------------- --


bobina fora

    Isso dá a seguinte saída

    1. planeje a consulta sem restrições:

Plano de execução
-------------------------------------------------- --------
Valor de hash do plano: 4077265613

-------------------------------------------------- -----------------------
| ID | Operação | Nome | Linhas | bytes | Custo (%CPU)| Tempo |
-------------------------------------------------- -----------------------
| 0 | SELECIONE A DECLARAÇÃO | | 59063 | 288K| 139 (3)| 00:00:02 |
| 1 | HASH ÚNICO | | 59063 | 288K| 139 (3)| 00:00:02 |
| 2 | ACESSO À MESA COMPLETO | OBJETOS | 59063 | 288K| 136 (0)| 00:00:02 |
-------------------------------------------------- -----------------------


2. planejar a consulta com restrição de verificação

Plano de execução
-------------------------------------------------- --------
Valor de hash do plano: 4077265613

-------------------------------------------------- -----------------------
| ID | Operação | Nome | Linhas | bytes | Custo (%CPU)| Tempo |
-------------------------------------------------- -----------------------
| 0 | SELECIONE A DECLARAÇÃO | | 59063 | 288K| 139 (3)| 00:00:02 |
| 1 | HASH ÚNICO | | 59063 | 288K| 139 (3)| 00:00:02 |
| 2 | ACESSO À MESA COMPLETO | OBJETOS | 59063 | 288K| 136 (0)| 00:00:02 |
-------------------------------------------------- -----------------------


3. planeje a consulta com a coluna NOT NULL

Plano de execução
-------------------------------------------------- --------
Valor de hash do plano: 4172758181

-------------------------------------------------- -------------------------------------
| ID | Operação | Nome | Linhas | bytes | Custo (%CPU)| Tempo |
-------------------------------------------------- -------------------------------------
| 0 | SELECIONE A DECLARAÇÃO | | 59063 | 288K| 40 (8)| 00:00:01 |
| 1 | HASH ÚNICO | | 59063 | 288K| 40 (8)| 00:00:01 |
| 2 | ÍNDICE VARREDURA COMPLETA RÁPIDA | IDX_OBJECT_ID | 59063 | 288K| 37 (0)| 00:00:01 |
-------------------------------------------------- -------------------------------------


4. planeje a consulta com índice de bitmap

Plano de execução
-------------------------------------------------- --------
Valor de hash do plano: 2970019208

-------------------------------------------------- -----------------------------------------------------
| ID | Operação | Nome | Linhas | bytes | Custo (%CPU)| Tempo |
-------------------------------------------------- -----------------------------------------------------
| 0 | SELECIONE A DECLARAÇÃO | | 43 | 387 | 6 (34)| 00:00:01 |
| 1 | HASH ÚNICO | | 43 | 387 | 6 (34)| 00:00:01 |
| 2 | ÍNDICE BITMAP RÁPIDA VARREDURA COMPLETA | BIDX_OBJECT_TYPE | 59063 | 519K| 4 (0)| 00:00:01 |
-------------------------------------------------- -----------------------------------------------------

    Resumo

    Se houver um índice B*-tree normal na coluna Valores NULL são possíveis na coluna, então o otimizador não pode confiar apenas nas informações do índice para fazer o 'selecionar distinção' e fazer um TABLE ACCESS FULL.

    Se houver um índice de árvore B* normal e uma restrição de verificação NOT-NULL na coluna, o otimizador também não depende das informações do índice e faz um TABLE ACCESS FULL.

    Se houver um índice de árvore B* normal e a coluna for definida como NOT NULL, o otimizador dependerá das informações do índice e fará um INDEX FAS FULL SCAN.

    Se houver um índice de bitmap na coluna, o otimizador saberá que todas as informações estão no índice e fará um BITMAP INDEX FAST FULL SCAN

    • 10

relate perguntas