Hi,
I have a spatial table (POLO) with about 600.000 points and a view (VW_POLO_ALIMENTACAO) that join this table with other tables and views and apply some filters. I have another spatial table (AREA_ESTUDO) with about 300.000 polygons.
If we try to compare the points (VW_POLO_ALIMENTACAO) with the polygons (AREA_ESTUDO), the query returns 35 lines and is really fast : 0.1s.
The query is the following:
SELECT /*+ ORDERED */ x.*
FROM (SELECT geom
FROM onmaps_app.area_estudo
WHERE cod_area_estudo IN (467,608,731,1708)
) area,
(
SELECT geom
FROM dados_estat.vw_polo_alimentacao
WHERE (cod_categoria = 4 or cod_categoria_pai = 4)
) x
where SDO_ANYINTERACT(x.geom, area.geom)= 'TRUE';
The explain plan is the following:
Plan hash value: 3525819569
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1695 | 854K| 7253 (6)| 00:00:02 |
|* 1 | HASH JOIN | | 1695 | 854K| 7253 (6)| 00:00:02 |
| 2 | VIEW | | 185 | 740 | 2 (50)| 00:00:01 |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 4 | INDEX FULL SCAN | IDX_POLO_CATEGORIA_COD_NOME | 185 | 1295 | 1 (0)| 00:00:01 |
|* 5 | INDEX SKIP SCAN | IDX_POLO_CATEGORIA_COD_NOME | 1 | 20 | 1 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 1667 | 833K| 7251 (6)| 00:00:02 |
| 7 | INDEX FULL SCAN | PK_REGIAO_GEOGRAFICA | 5 | 15 | 1 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 1667 | 828K| 7249 (6)| 00:00:02 |
| 9 | TABLE ACCESS FULL | GC_STATE | 27 | 162 | 3 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 1667 | 818K| 7245 (6)| 00:00:02 |
| 11 | INDEX FULL SCAN | IDX_MESORREGIAO_UF_NOME_MESO | 137 | 959 | 1 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 1667 | 807K| 7243 (6)| 00:00:02 |
| 13 | INDEX FAST FULL SCAN | IDX_MR_IBGE_MESO_NOME_MR_IBGE | 558 | 5022 | 3 (0)| 00:00:01 |
|* 14 | HASH JOIN | | 1667 | 792K| 7238 (6)| 00:00:02 |
| 15 | TABLE ACCESS FULL | GC_MUNICIPALITY | 5570 | 55700 | 339 (12)| 00:00:01 |
|* 16 | HASH JOIN | | 1667 | 776K| 6895 (5)| 00:00:01 |
| 17 | INDEX FULL SCAN | IDX_POLO_CATEGORIA_COD_NOME | 185 | 1295 | 1 (0)| 00:00:01 |
|* 18 | HASH JOIN | | 15015 | 6891K| 6889 (5)| 00:00:01 |
| 19 | INDEX FAST FULL SCAN | IDX_POLO_REDE_CATE_RED_ALIAS | 34533 | 303K| 97 (20)| 00:00:01 |
| 20 | NESTED LOOPS | | 15015 | 6759K| 6769 (5)| 00:00:01 |
| 21 | INLIST ITERATOR | | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | AREA_ESTUDO | 4 | 464 | 10 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | AREA_ESTUDO_PK | 4 | | 6 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | POLO | 3754 | 1264K| 6769 (5)| 00:00:01 |
|* 25 | DOMAIN INDEX | IDX_POLO_GEOM_USAR | | | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COD_CATEGORIA"="C"."COD_CATEGORIA")
3 - access("COD_CATEGORIA_PAI"=PRIOR "COD_CATEGORIA")
filter("COD_CATEGORIA"= (SELECT "COD_CATEGORIA" FROM "DADOS_ESTAT"."POLO_CATEGORIA" "POLO_CATEGORIA" WHERE
"NOME_CATEGORIA"='Alimentação'))
5 - access("NOME_CATEGORIA"='Alimentação')
filter("NOME_CATEGORIA"='Alimentação')
6 - access("S"."COD_REGIAO_GEOGRAFICA"="COD_REGIAO_GEOGRAFICA")
8 - access("MS"."STATE_ID"="S"."STATE_ID")
10 - access("MR"."COD_MESORREGIAO"="MS"."COD_MESORREGIAO")
12 - access("M"."COD_MICRORREGIAO_IBGE"="MR"."COD_MICRORREGIAO_IBGE")
14 - access("P"."UFMUN_COD"="M"."UFMUN_COD")
16 - access("B"."COD_CATEGORIA"="D"."COD_CATEGORIA")
filter("B"."COD_CATEGORIA"=4 OR "D"."COD_CATEGORIA_PAI"=4)
18 - access("P"."COD_REDE"="B"."COD_REDE")
23 - access("COD_AREA_ESTUDO"=467 OR "COD_AREA_ESTUDO"=608 OR "COD_AREA_ESTUDO"=731 OR "COD_AREA_ESTUDO"=1708)
24 - filter(("P"."COD_TIPO_POLO"=1 OR "P"."COD_TIPO_POLO"=2) AND "P"."EXCLUIDO"='N')
25 - access("MDSYS"."SDO_ANYINTERACT"("P"."GEOM_USAR","GEOM")='TRUE')
When this same query comes from my application, MapViewer changes it by adding SDO_FILTER and it is very slow: 140s.
The query generated by MapViewer is the following:
SELECT *
FROM (SELECT /*+ ORDERED */ x.*
FROM (SELECT geom
FROM onmaps_app.area_estudo
WHERE cod_area_estudo IN (467,608,731,1708)
) area,
(
SELECT geom
FROM dados_estat.vw_polo_alimentacao
WHERE (cod_categoria = 4 or cod_categoria_pai = 4)
) x
where SDO_ANYINTERACT(x.geom, area.geom)= 'TRUE')
WHERE MDSYS.SDO_FILTER(geom, MDSYS.SDO_GEOMETRY(2003, 8307, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 3), MDSYS.SDO_ORDINATE_ARRAY(-46.91496,-23.83387,-46.13864,-23.41195)), 'querytype=WINDOW') = 'TRUE'
The explain plan is the following:
Plan hash value: 4257225256
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 8772 | 6909 (5)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 8772 | 6909 (5)| 00:00:01 |
| 2 | VIEW | | 185 | 740 | 2 (50)| 00:00:01 |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 4 | INDEX FULL SCAN | IDX_POLO_CATEGORIA_COD_NOME | 185 | 1295 | 1 (0)| 00:00:01 |
|* 5 | INDEX SKIP SCAN | IDX_POLO_CATEGORIA_COD_NOME | 1 | 20 | 1 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 17 | 8704 | 6907 (5)| 00:00:01 |
|* 7 | HASH JOIN | | 17 | 8653 | 6907 (5)| 00:00:01 |
| 8 | TABLE ACCESS FULL | GC_STATE | 27 | 162 | 3 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 17 | 8551 | 6903 (5)| 00:00:01 |
| 10 | INDEX FULL SCAN | IDX_MESORREGIAO_UF_NOME_MESO | 137 | 959 | 1 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 17 | 8432 | 6902 (5)| 00:00:01 |
| 12 | NESTED LOOPS | | 17 | 8279 | 6898 (5)| 00:00:01 |
|* 13 | HASH JOIN | | 17 | 8109 | 6880 (5)| 00:00:01 |
| 14 | INDEX FULL SCAN | IDX_POLO_CATEGORIA_COD_NOME | 185 | 1295 | 1 (0)| 00:00:01 |
|* 15 | HASH JOIN | | 150 | 70500 | 6878 (5)| 00:00:01 |
| 16 | NESTED LOOPS | | 150 | 69150 | 6769 (5)| 00:00:01 |
| 17 | INLIST ITERATOR | | | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | AREA_ESTUDO | 4 | 464 | 10 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | AREA_ESTUDO_PK | 4 | | 6 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | POLO | 38 | 13110 | 6769 (5)| 00:00:01 |
|* 21 | DOMAIN INDEX | IDX_POLO_GEOM_USAR | | | | |
| 22 | INDEX FAST FULL SCAN | IDX_POLO_REDE_CATE_RED_ALIAS | 34533 | 303K| 97 (20)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | GC_MUNICIPALITY | 1 | 10 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | GC_MUNICIPALITY_PK | 1 | | 0 (0)| 00:00:01 |
| 25 | INDEX FAST FULL SCAN | IDX_MR_IBGE_MESO_NOME_MR_IBGE | 558 | 5022 | 3 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_REGIAO_GEOGRAFICA | 1 | 3 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COD_CATEGORIA"="C"."COD_CATEGORIA")
3 - access("COD_CATEGORIA_PAI"=PRIOR "COD_CATEGORIA")
filter("COD_CATEGORIA"= (SELECT "COD_CATEGORIA" FROM "DADOS_ESTAT"."POLO_CATEGORIA" "POLO_CATEGORIA" WHERE
"NOME_CATEGORIA"='Alimentação'))
5 - access("NOME_CATEGORIA"='Alimentação')
filter("NOME_CATEGORIA"='Alimentação')
7 - access("MS"."STATE_ID"="S"."STATE_ID")
9 - access("MR"."COD_MESORREGIAO"="MS"."COD_MESORREGIAO")
11 - access("M"."COD_MICRORREGIAO_IBGE"="MR"."COD_MICRORREGIAO_IBGE")
13 - access("B"."COD_CATEGORIA"="D"."COD_CATEGORIA")
filter("B"."COD_CATEGORIA"=4 OR "D"."COD_CATEGORIA_PAI"=4)
15 - access("P"."COD_REDE"="B"."COD_REDE")
19 - access("COD_AREA_ESTUDO"=467 OR "COD_AREA_ESTUDO"=608 OR "COD_AREA_ESTUDO"=731 OR "COD_AREA_ESTUDO"=1708)
20 - filter(("P"."COD_TIPO_POLO"=1 OR "P"."COD_TIPO_POLO"=2) AND "P"."EXCLUIDO"='N' AND
"MDSYS"."SDO_ANYINTERACT"("P"."GEOM_USAR","GEOM")='TRUE')
21 - access("MDSYS"."SDO_FILTER"("P"."GEOM_USAR","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"MDSYS"."SDO_ELEM_INFO_ARR
AY"(1,1003,3),"MDSYS"."SDO_ORDINATE_ARRAY"((-46.91496),(-23.83387),(-46.13864),(-23.41195))),'querytype=WINDOW')='T
RUE')
24 - access("P"."UFMUN_COD"="M"."UFMUN_COD")
26 - access("S"."COD_REGIAO_GEOGRAFICA"="COD_REGIAO_GEOGRAFICA")
The spatial index was created with LAYER_GTYPE=POINT and the POLO table was reorganized (I did a reclustering of the table rows based on proximity of the geometry data in the location column to avoid/minimize random I/O).
I'm using Oracle 11.2.0.3 and Red Hat.
Have you got an idea why it is so fast without SDO_FILTER and so slow with SDO_FILTER and how to improve it?
Thank you!