Slow Query Using SDO_FILTER With the Optimizer Choosing Bitmap Conversion To Rowids
(Doc ID 1189964.1)
Last updated on JANUARY 30, 2022
Applies to:Oracle Spatial and Graph - Version 10.2.0.4 to 22.214.171.124 [Release 10.2 to 11.2]
Information in this document applies to any platform.
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
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")
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.
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