Slow Query Using SDO_FILTER The Optimizer Choosing Bitmap Conversion To Rowids (Doc ID 1189964.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 10.2.0.4 to 11.2.0.1 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 30-Oct-2014***

Symptoms

Query using SDO_FILTER is very slow

Explain plan shows "bitmap conversion from rowids"
Tables don't have bitmap index and _b_tree_bitmap_plans parameter is set to false

SELECT AD.LOCATION AS GEOM
FROM SCOTT.PARTIES_GEO P JOIN SCOTT.ADDRESS AD
ON P.ID = AD.PARTY_ID WHERE AD.LOCATION IS NOT NULL
AND SDO_FILTER(AD.LOCATION, SDO_GEOMETRY(2003,8307,NULL,
SDO_ELEM_INFO_ARRAY(1,1003 ,3),
SDO_ORDINATE_ARRAY(-12.66,47.2,-12.54,47.31))
) = 'TRUE'
AND P.LAYER_ID = 3
AND ID IN (SELECT PARTY_ID FROM SCOTT.ALERT_USER WHERE ALERT_ID IN (19))
/


Bad execution plan shows BITMAP CONVERSION FROM ROWIDS

... 
7 2 BITMAP CONVERSION TO ROWIDS
8 7 BITMAP AND
9 8 BITMAP CONVERSION FROM ROWIDS
10 9 SORT ORDER BY
11 10 DOMAIN INDEX NNS.SIDX_GIS_ADDRESS 97 0 1 0
12 8 BITMAP CONVERSION FROM ROWIDS
13 12 INDEX RANGE SCAN SCOTT.GIS_ADDRESS_PARTYID_FK 97 2 344


The good execution plan should show:

ID PID Operation Name Rows Bytes Cost CPU Cost IO Cost Temp space IN-OUT PQ
Dist PStart PStop
0 SELECT STATEMENT 104 15K 46800 485M 46767
1 0 HASH JOIN 104 15K 46800 485M 46767
2 1 NESTED LOOPS 2087 97K 29 451758 29
3 2 INDEX RANGE SCAN SCOTT.ALERTUSER_ID_PK1 26 572 2 16443 2
4 2 TABLE ACCESS BY INDEX ROWID SCOTT.PARTIES_GEO 82 2132 2 16743 2
5 4 INDEX UNIQUE SCAN SCOTT.PRTY_GEO_PK 1 1 9021 1
6 1 TABLE ACCESS BY INDEX ROWID SCOTT.GIS_ADDRESS 10442 1101K 46770 475M
46738
7 6 DOMAIN INDEX SCOTT.SIDX_GIS_ADDRESS

This problem reproduces with super merge 9715664 applied. This was the latest available at the time. (For the latest Spatial super merge, see Note 1073203.1)

Entry in 10053 trace file:

BITMAP_TREE(@"SEL$304F545E" "AD"@"SEL$1" AND(("GIS_ADDRESS"."PARTY_ID")
"SIDX_GIS_ADDRESS"))

indicates AND operation between index on column "GIS_ADDRESS"."PARTY_ID"
(index GIS_ADDRESS_PARTYID_FK) and spatial index "SIDX_GIS_ADDRESS"

That AND operation causes the BITMAP CONVERSION FROM ROWIDS that you see in
the bad execution plan.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms