Temat: Oracle Spatial
Krzysztof P.:
Kamil Stawiarski:
Wracając do tematu, to taka ciekawostka:
http://www.goldenline.pl/forum/2937348/indexy-spatialo...
Nie ma takieeej stroooony, nie ma takieeej stroooony :)
Coś chyba krzywo klikasz, bo u mnie działa :) No chyba, że się nie dołączyłeś członków grupy "Optymalizacja Oracle" ... Ale specjalnie dla Ciebie, Krzysztof - post jeszcze raz tutaj :)
--------------------------------------------------------------
Witam!
Ostatnio bawiłem się z optymalizacją jednego zapytanka, korzystającego z indeksów spatialowych i chciałem się podzielić spostrzeżeniami:
Były dwa zapytania:
Zapytanie 1:
select *
from tabela
where mdsys.sdo_filter
(shape,
mdsys.sdo_geometry(2003,
2180,
NULL, mdsys.sdo_elem_info_array(1,3,3),
mdsys.sdo_ordinate_array(200872.16707840492, 610397.420979471, 201887.68583438097, 611344.5935072843)),
'mask=ANYINTERACT querytype=window') = 'TRUE'
and (otype, subtype) in ((4022006,0));
-----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 5860 | |
| 1 | TABLE ACCESS BY INDEX ROWID | TABELA | 28K | 1641K | 5860 | 166:26:45 |
| 2 | DOMAIN INDEX | RTREE_IDX| | | 1 | 00:02:43 |
-----------------------------------------------------+-----------------------------------+
Wykonanie: 0.5 sek.
oraz Zapytanie 2:
select *
from tabela
where mdsys.sdo_filter
(shape,
mdsys.sdo_geometry(2003,
2180,
NULL, mdsys.sdo_elem_info_array(1,3,3),
mdsys.sdo_ordinate_array(200872.16707840492, 610397.420979471, 201887.68583438097, 611344.5935072843)),
'mask=ANYINTERACT querytype=window') = 'TRUE'
and (otype, subtype) in ((4022500,0));
-----------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1629 | |
| 1 | TABLE ACCESS BY INDEX ROWID | TABELA | 1258 | 71K | 1629 | 46:16:54 |
| 2 | INDEX RANGE SCAN | BTREE_IDX| 138K | | 68 | 02:56:53 |
-----------------------------------------------------+-----------------------------------+
Wykonanie: 120 sek.
Oczywiście wystarczyło zajrzeć na plan wykonania zapytania, żeby stało się jasne co jest problemem - w przypadku zapytania drugiego access nastąpił na poziomie indeksu B-Tree a nie R-Tree, natomiast przy zapytaniu pierwszym (tym szybszym) był elegancko użyty index R-Tree. Zastanawiałem się dlaczego CBO podjął taką a nie inną decyzję - potraktowałem chama trace'em 10053 i oto co zobaczyłem po przebiciu się przez całe tony tekstu:
Zapytanie 1:
Bitmap nodes:
Used RTREE_IDX
Cost = 1.000000, sel = 0.010000
Used BTREE_IDX
Cost = 1547.610041, sel = 0.765717
Access path: Domain index - accepted
Cost: 5860.124212 Cost_io: 5860.120140 Cost_cpu: 236114748.981652 Sel: 0.010000
Not Believed to be index-only
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexDomain
Cost: 5860.12 Degree: 1 Resp: 5860.12 Card: 28973.38 Bytes: 0
Zapytanie 2:
Bitmap nodes:
Used RTREE_IDX
Cost = 1.000000, sel = 0.010000
Used BTREE_IDX
Cost = 67.600436, sel = 0.033249
Access path: Domain index - accepted
Cost: 5860.124212 Cost_io: 5860.120140 Cost_cpu: 236114748.981652 Sel: 0.010000
Not Believed to be index-only
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index:BTREE_IDX
Cost: 1628.60 Degree: 1 Resp: 1628.60 Card: 1258.07 Bytes: 0
Ewidentnie w obydwu przypadkach selektywność oszacowana przez CBO jest na korzyść indeksu R-Tree, jednak w drugim zapytaniu Oracle stwierdził, że nie jest to aż tak duża przewaga i zdecydował się na użycie zwykłego range scan'a... Wydaje mi się, że błąd tutaj wynika z funkcji użytych do oszacowania kosztów indeksów spatialowych - bardzo ładnie widać w raporcie bloki anonimowe wykonywane przez optymalizator w celu znalezienia odpowiednich kosztów:
declare
cost sys.ODCICost := sys.ODCICost(NULL, NULL, NULL, NULL);
obj0 "MDSYS"."SDO_GEOMETRY" := "MDSYS"."SDO_GEOMETRY"(NULL, NULL, NULL, NULL, NULL);
begin
:1 := "MDSYS"."SDO_STATISTICS".ODCIStatsFunctionCost(
sys.ODCIFuncInfo('MDSYS',
'SDO_3GL',
'FILTER',
2),
cost,
sys.ODCIARGDESCLIST(sys.ODCIARGDESC(2, 'TABELA', 'TEST', '"SHAPE"', NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, N
ULL, NULL, NULL))
, obj0, "MDSYS"."SDO_GEOMETRY"(2003,2180,NULL,"MDSYS"."SDO_ELEM_INFO_ARRAY"(1,3,3),"MDSYS"."SDO_ORDINATE_ARRAY"(200872.16707840492,610397.420979471,201887.68583438097,611344.5935072
843)), :5,
sys.ODCIENV(:6,:7,:8,:9));
if cost.CPUCost IS NULL then
:2 := -1.0;
else
:2 := cost.CPUCost;
end if;
if cost.IOCost IS NULL then
:3 := -1.0;
else
:3 := cost.IOCost;
end if;
if cost.NetworkCost IS NULL then
:4 := -1.0;
else
:4 := cost.NetworkCost;
end if;
exception
when others then
raise;
end;
Bind :5 Value 'mask=ANYINTERACT querytype=window'
ODCIEnv Bind :6 Value 0
ODCIEnv Bind :7 Value 0
ODCIEnv Bind :8 Value 0
ODCIEnv Bind :9 Value 3
MDSYS.SDO_STATISTICS.ODCIStatsFunctionCost returned:
CPUCost : 1000
IOCost : 0
NetworkCost : -1
Oczywiście jedno z rozwiązań to hint i po problemie ale chciałem też pokazać alternatywne rozwiązanie, które dostarcza trochę informacji na temat transformatora SQL - jeśli ubierze się zapytanie do postaci CTE, tak że wewnątrz definicji CTE posłużymy się filtrem spatialowym a na zewnątrz dodamy dodatkowe filtry na wartościach prostych, to można by się spodziewać, że Oracle wykona najpierw plan dla zapytania wewnątrz CTE a dopiero obliczy plan całościowy. Jednak zapytanie pozostawione w takiej postaci:
with v_shape as
(
select *
from tabela
where mdsys.sdo_filter
(shape,
mdsys.sdo_geometry(2003,
2180,
NULL, mdsys.sdo_elem_info_array(1,3,3),
mdsys.sdo_ordinate_array(200872.16707840492, 610397.420979471, 201887.68583438097, 611344.5935072843)),
'mask=ANYINTERACT querytype=window') = 'TRUE'
)
select *
from v_shape
where (otype, subtype) in ((4022500,0));
Nie zdaje egzaminu ze względu na to, że transformator SQL wykonuje fazę subquery unnesting, przepisując nam zapytanie do postaci pierwotnej - bez żadnych podzapytań czy CTE. Jeśli jednak zrobimy mały trick:
with v_shape as
(
select rownum, t.*
from tabela t
where mdsys.sdo_filter
(shape,
mdsys.sdo_geometry(2003,
2180,
NULL, mdsys.sdo_elem_info_array(1,3,3),
mdsys.sdo_ordinate_array(200872.16707840492, 610397.420979471, 201887.68583438097, 611344.5935072843)),
'mask=ANYINTERACT querytype=window') = 'TRUE'
)
select *
from v_shape
where (otype, subtype) in ((4022500,0));
Okazuje się, że użycie ROWNUM w składni z CTE, powoduje, że transformator SQL nie przepisuje zapytania (brak fazy "subquery unnesting") - tym samym rozwijany jest najpierw plan dla SQL zdefiniowanego jako CTE (używając indeksu przestrzennego) a dopiero potem odfiltrowywane są wyniki dla otype i subtype.
Oj... mam nadzieję, że nie namieszałem za bardzo :) Tak czy inaczej - wydaje mi się, że sytuacja ciekawa i warta przemyślenia :)
Pozdrawiam!
Kamil.
пропозиції роботи
