Início / dba / Perguntas / 181500
Accepted
jpmc26
Asked: 2017-07-22 20:23:35 +0800 CST

Por que o Oracle não usa o índice espacial ao fazer JOIN em tabelas? (Ou: Por que minha consulta com um JOIN espacial é tão lenta no Oracle?)

  • 772

Tenho a seguinte tabela:

CREATE TABLE MY_DATA (
  MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
  GEOM SDO_GEOMETRY
);

Configurei seus metadados SDO e criei um índice espacial:

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)
VALUES ('MY_DATA', 'GEOM', 4326, MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180.0000,180.0000,0.00000000001),MDSYS.SDO_DIM_ELEMENT('Y',-90.0000,90,0.00000000001)));

CREATE INDEX SPIX_MY_DATA ON MY_DATA (GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

A tabela tem cerca de 150.000 linhas.

Eu preciso encontrar linhas onde as geometrias cruzam uma linha específica. Esta parece ser uma tarefa bastante simples. Tudo o que é preciso é uma JOINe uma WHEREcláusula:

SELECT /*+ INDEX(MD2 SPIX_MY_DATA)*/
    MD2.MY_DATA_ID
FROM MY_DATA MD1
JOIN MY_DATA MD2 ON SDO_RELATE(MD1.GEOM, MD2.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

Mas esta consulta é muito lenta. Leva mais de 85 segundos para buscar apenas 8 linhas relacionadas. Podemos ver no plano de consulta que ele não está usando o índice espacial, apesar da dica de índice:

Plan hash value: 4008712617

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   1 |  NESTED LOOPS                |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00755898 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | MY_DATA       |   379 |  4927 |  1388   (1)| 00:00:17 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MD1"."MY_DATA_ID"=143668)
   4 - filter("MD2"."MY_DATA_ID"<>143668 AND 
              "MDSYS"."SDO_RTREE_RELATE"("MD1"."GEOM","MD2"."GEOM",'mask=ANYINTERACT 
              querytype=window  ')='TRUE')

(A linha 2 é MD1 e a linha 4 é MD2.)

Eu tentei mudar a dica para /*+ INDEX(MY_DATA SPIX_MY_DATA)*/, mas isso também não teve efeito. Tenho certeza de que minhas estatísticas estão atualizadas.

Como posso acelerar esta consulta? Como posso obtê-lo para usar o índice espacial?

Atualmente estou usando o Oracle 11.2.

oracle index

1 respostas

  1. Best Answer

    Comportamento SDO_RELATEe o planejador de consultas

    SDO_RELATEusará apenas o índice espacial do primeiro parâmetro . A documentação não diz isso explicitamente, mas pode ser obtido a partir de alguns detalhes.

    A documentação para os dois argumentos de geometria diz:

    geometry1: especifica uma coluna de geometria em uma tabela. A coluna deve ser indexada espacialmente. O tipo de dados é SDO_GEOMETRY.

    geometry2Especifica uma geometria de uma tabela ou uma instância transitória de uma geometria . (Especificado usando uma variável de ligação ou construtor SDO_GEOMETRY.) O tipo de dados é SDO_GEOMETRY.

    (ênfase minha)

    Isso sugere muito fortemente que SDO_RELATEé projetado especificamente para usar o índice espacial em seu primeiro argumento . No entanto, como você tem um filtro de chave primária MD1em sua WHEREcláusula, o planejador conclui corretamente que usar o índice de chave primária será mais rápido do que usar o índice espacial em MD1. Ele nunca considera reverter os argumentos para a função e, como SDO_RELATEusa o índice no primeiro argumento em vez do segundo, nunca considera realmente usar o índice espacial.

    SDO_FILTERse comporta da mesma maneira, tanto quanto eu posso dizer.

    A solução

    A resposta é tão simples quanto não intuitiva. Basta mudar a ordem dos argumentos para SDO_RELATE:

    SELECT
    MD2.MY_DATA_ID
FROM MY_DATA MD1
JOIN MY_DATA MD2 ON SDO_RELATE(MD2.GEOM, MD1.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

    Isso faz com que ele use o índice espacial agora:

    Plan hash value: 3780744499

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |  1487 | 38662 |   534   (1)| 00:00:07 |
|   1 |  NESTED LOOPS                |               |  1487 | 38662 |   534   (1)| 00:00:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00755898 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |  1487 | 19331 |   534   (1)| 00:00:07 |
|*  5 |    DOMAIN INDEX              | SPIX_MY_DATA  |       |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MD1"."MY_DATA_ID"=143668)
   4 - filter("MD2"."MY_DATA_ID"<>143668)
   5 - access("MDSYS"."SDO_RTREE_RELATE"("MD2"."GEOM","MD1"."GEOM",'mask=ANYINTERACT 
              querytype=window  ')='TRUE')

    A consulta agora é quase instantânea, sendo executada em cerca de 50 milissegundos.

    Ignore a ORDEREDparte da dica nos documentos para casos como este

    A documentação continua dizendo mais tarde:

    geometry2pode vir de uma tabela ou ser um objeto SDO_GEOMETRY transitório, como uma variável de ligação ou um construtor SDO_GEOMETRY.

    • Se a geometry2coluna não estiver indexada espacialmente, o operador indexa a janela de consulta na memória e o desempenho é muito bom.

    • Se duas ou mais geometrias de geometry2forem passadas para o operador, a dica do otimizador ORDERED deverá ser especificada e a tabela em geometry2deverá ser especificada primeiro na cláusula FROM.

    A partir disso, podemos pensar que também podemos forçar o planejador a usar o índice colocando MD2primeiro na lista e especificando a ORDEREDdica:

    SELECT /*+ ORDERED INDEX(MD2 SPIX_MY_DATA)*/
    MD2.MY_DATA_ID
FROM MY_DATA MD2
JOIN MY_DATA MD1 ON SDO_RELATE(MD1.GEOM, MD2.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

    que nos dá um plano de execução diferente:

    Plan hash value: 3307523706

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |  1487 | 38662 | 14806   (1)| 00:02:58 |
|   1 |  NESTED LOOPS                    |               |  1487 | 38662 | 14806   (1)| 00:02:58 |
|*  2 |   TABLE ACCESS FULL              | MY_DATA       |   148K|  1887K|  4752   (1)| 00:00:58 |
|   3 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   4 |    BITMAP AND                    |               |       |       |            |          |
|   5 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|   6 |      SORT ORDER BY               |               |       |       |            |          |
|*  7 |       DOMAIN INDEX               | SPIX_MY_DATA  |     1 |       |     0   (0)| 00:00:01 |
|   8 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|*  9 |      INDEX RANGE SCAN            | SYS_C00755898 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MD2"."MY_DATA_ID"<>143668)
   7 - access("MDSYS"."SDO_RTREE_RELATE"("MD1"."GEOM","MD2"."GEOM",'mask=ANYINTERACT 
              querytype=window  ')='TRUE')
   9 - access("MD1"."MY_DATA_ID"=143668)

    Isso força o índice a ser usado, mas é usado de maneira errada. Ele ainda está fazendo uma verificação completa da tabela no MD2. Em vez de filtrar MD2pela geometria solitária do MD1conjunto de resultados de , ele pega quase todas as linhas da tabela MD2(observe a contagem de 148K na linha 2 do plano), realiza duas pesquisas MD1(uma pela chave primária e outra comparando todas as linhas usando MD2o índice espacial) e bitmap e o resultado. Isso realmente piora o desempenho, fazendo uma varredura completamente desnecessária no índice espacial e trabalho extra comparando essa varredura com a chave primária.

    Como regra geral, você deseja colocar a tabela com o menor conjunto de geometrias (depois de outros filtros) primeiro em sua FROMcláusula e deixá-la usar o índice espacial na segunda tabela.

    Apenas especificar a ORDEREDdica na consulta original não altera o plano do original.

    • 2

relate perguntas