My Oracle Support Banner

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

Last updated on FEBRUARY 09, 2019

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

A query using SDO_FILTER is very slow.  The 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 GEO P JOIN 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 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 <spatial index> 97 0 1 0
12 8 BITMAP CONVERSION FROM ROWIDS
13 12 INDEX RANGE SCAN ADDRESS_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 USER_ID_PK1 26 572 2 16443 2
4 2 TABLE ACCESS BY INDEX ROWID GEO 82 2132 2 16743 2
5 4 INDEX UNIQUE SCAN GEO_PK 1 1 9021 1
6 1 TABLE ACCESS BY INDEX ROWID ADDRESS 10442 1101K 46770 475M
46738
7 6 DOMAIN INDEX <spatial index>

This problem reproduces with super merge patch 9715664 applied which 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(("ADDRESS"."PARTY_ID")
"<spatial index>"))

indicates AND operation between index on column "ADDRESS"."PARTY_ID"
(index ADDRESS_FK) and the spatial index.

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

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.