Bad Performance Spatial Query With Union Takes Exponentially Longer Than Individual Subqueries Combined (Doc ID 1612611.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 11.2.0.2.0 and later
Information in this document applies to any platform.

Symptoms

A query combining 7 queries with 6 UNION operators is taking long time to execute

It runs in 1.19secs in 11.2.0.3
but it takes several minutes in 11.2.0.2

SELECT LIS_ID
FROM
(
(
(
SELECT LIS.LIS_ID
FROM (
(SELECT /*+ USE_HASH (GEO, LIS) INDEX (GEO GADDR_LOCATION_I1) */ GEO.ID
FROM GEO_ADDRESSES GEO
/** User Item (Logical Attribute): GeoLocation */
WHERE (SDO_WITHIN_DISTANCE(GEO.LOCATION, SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-76.9109431621003, 39.0110603400585, NULL), NULL, NULL), 'distance=0.50001113726544 unit=MILE') = 'TRUE'))) GEO,
LISTINGS LIS
WHERE LIS.LIS_ID = GEO.ID
/** User Item (Logical Attribute): LocaleListingStatus */
AND LIS.LLS_ID = 10000069147
/** User Item (Logical Attribute): Type */
AND (LIS.STR_TYPE IN ( 10000734239, 10000001147, 10000001149, 50074309611, 10000001163, 10000001164 ) )
/** User Item (Logical Attribute): ForSale */
AND LIS.MAIN_FOR_SALE_FLAG = 1
/** User Item (Logical Attribute): ListPrice */
AND LIS.MAIN_LIST_PRICE /** User Item (Logical Attribute): Beds */
AND LIS.STR_BEDROOMS >= 2
/** Mand. Logical Where Clause Item (Logical View Column): MAIN_PROPERTY_USE_TYPE */
AND LIS.MAIN_PROPERTY_USE_TYPE = 10000001073
/** Cond. Role-based Logical Where Clause Item (Logical View Column): SUB_SYS_LOC_ID */
AND LIS.SUB_SYS_LOC_ID = 10000065722
/** Mand. Logical Where Clause Item (Logical View Column): SYS_LOC_ID */
AND LIS.SYS_LOC_ID = 50000900598
)
UNION
(
SELECT LIS.LIS_ID
FROM (
(SELECT /*+ USE_HASH (GEO, LIS) INDEX (GEO GADDR_LOCATION_I1) */ GEO.ID
FROM GEO_ADDRESSES GEO
/** User Item (Logical Attribute): GeoLocation */
WHERE (SDO_WITHIN_DISTANCE(GEO.LOCATION, SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-76.9282809611726, 38.9744366565436, NULL), NULL, NULL), 'distance=0.501750960155958 unit=MILE') = 'TRUE'))) GEO,
LISTINGS LIS
WHERE LIS.LIS_ID = GEO.ID
/** User Item (Logical Attribute): LocaleListingStatus */
AND LIS.LLS_ID = 10000069147
/** User Item (Logical Attribute): Type */
AND (LIS.STR_TYPE IN ( 10000734239, 10000001147, 10000001149, 50074309611, 10000001163, 10000001164 ) )
/** User Item (Logical Attribute): ForSale */
AND LIS.MAIN_FOR_SALE_FLAG = 1
/** User Item (Logical Attribute): ListPrice */
AND LIS.MAIN_LIST_PRICE /** User Item (Logical Attribute): Beds */
AND LIS.STR_BEDROOMS >= 2
/** Mand. Logical Where Clause Item (Logical View Column): MAIN_PROPERTY_USE_TYPE */
AND LIS.MAIN_PROPERTY_USE_TYPE = 10000001073
/** Cond. Role-based Logical Where Clause Item (Logical View Column): SUB_SYS_LOC_ID */
AND LIS.SUB_SYS_LOC_ID = 10000065722
/** Mand. Logical Where Clause Item (Logical View Column): SYS_LOC_ID */
AND LIS.SYS_LOC_ID = 50000900598
)
UNION
(
SELECT LIS.LIS_ID
FROM (
(SELECT /*+ USE_HASH (GEO, LIS) INDEX (GEO GADDR_LOCATION_I1) */ GEO.ID
FROM GEO_ADDRESSES GEO
/** User Item (Logical Attribute): GeoLocation */
WHERE (SDO_WITHIN_DISTANCE(GEO.LOCATION, SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-76.9349757548737, 38.962491504551, NULL), NULL, NULL), 'distance=0.494995720795404 unit=MILE') = 'TRUE'))) GEO,
LISTINGS LIS
WHERE LIS.LIS_ID = GEO.ID
/** User Item (Logical Attribute): LocaleListingStatus */
AND LIS.LLS_ID = 10000069147
/** User Item (Logical Attribute): Type */
AND (LIS.STR_TYPE IN ( 10000734239, 10000001147, 10000001149, 50074309611, 10000001163, 10000001164 ) )
/** User Item (Logical Attribute): ForSale */
AND LIS.MAIN_FOR_SALE_FLAG = 1
/** User Item (Logical Attribute): ListPrice */
AND LIS.MAIN_LIST_PRICE /** User Item (Logical Attribute): Beds */
AND LIS.STR_BEDROOMS >= 2
/** Mand. Logical Where Clause Item (Logical View Column): MAIN_PROPERTY_USE_TYPE */
AND LIS.MAIN_PROPERTY_USE_TYPE = 10000001073
/** Cond. Role-based Logical Where Clause Item (Logical View Column): SUB_SYS_LOC_ID */
AND LIS.SUB_SYS_LOC_ID = 10000065722
/** Mand. Logical Where Clause Item (Logical View Column): SYS_LOC_ID */
AND LIS.SYS_LOC_ID = 50000900598
)
UNION
(
SELECT LIS.LIS_ID -- 4th
FROM (
(SELECT /*+ USE_HASH (GEO, LIS) INDEX (GEO GADDR_LOCATION_I1) */ GEO.ID
FROM GEO_ADDRESSES GEO
/** User Item (Logical Attribute): GeoLocation */
WHERE (SDO_WITHIN_DISTANCE(GEO.LOCATION, SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-76.9666472789216, 38.9556171241892, NULL), NULL, NULL), 'distance=0.497805600965028 unit=MILE') = 'TRUE'))) GEO,
LISTINGS LIS
WHERE LIS.LIS_ID = GEO.ID
/** User Item (Logical Attribute): LocaleListingStatus */
AND LIS.LLS_ID = 10000069147
/** User Item (Logical Attribute): Type */
AND (LIS.STR_TYPE IN ( 10000734239, 10000001147, 10000001149, 50074309611, 10000001163, 10000001164 ) )
/** User Item (Logical Attribute): ForSale */
AND LIS.MAIN_FOR_SALE_FLAG = 1
/** User Item (Logical Attribute): ListPrice */
AND LIS.MAIN_LIST_PRICE /** User Item (Logical Attribute): Beds */
AND LIS.STR_BEDROOMS >= 2
/** Mand. Logical Where Clause Item (Logical View Column): MAIN_PROPERTY_USE_TYPE */
AND LIS.MAIN_PROPERTY_USE_TYPE = 10000001073
/** Cond. Role-based Logical Where Clause Item (Logical View Column): SUB_SYS_LOC_ID */
AND LIS.SUB_SYS_LOC_ID = 10000065722
/** Mand. Logical Where Clause Item (Logical View Column): SYS_LOC_ID */
AND LIS.SYS_LOC_ID = 50000900598
)
UNION
(
SELECT LIS.LIS_ID -- 5th
FROM (
(SELECT /*+ USE_HASH (GEO, LIS) INDEX (GEO GADDR_LOCATION_I1) */ GEO.ID
FROM GEO_ADDRESSES GEO
/** User Item (Logical Attribute): GeoLocation */
WHERE (SDO_WITHIN_DISTANCE(GEO.LOCATION, SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-77.003468644278, 38.9526135447932, NULL), NULL, NULL), 'distance=0.496288058061875 unit=MILE') = 'TRUE'))) GEO,
LISTINGS LIS
WHERE LIS.LIS_ID = GEO.ID
/** User Item (Logical Attribute): LocaleListingStatus */
AND LIS.LLS_ID = 10000069147
/** User Item (Logical Attribute): Type */
AND (LIS.STR_TYPE IN ( 10000734239, 10000001147, 10000001149, 50074309611, 10000001163, 10000001164 ) )
/** User Item (Logical Attribute): ForSale */
AND LIS.MAIN_FOR_SALE_FLAG = 1
/** User Item (Logical Attribute): ListPrice */
AND LIS.MAIN_LIST_PRICE /** User Item (Logical Attribute): Beds */
AND LIS.STR_BEDROOMS >= 2
/** Mand. Logical Where Clause Item (Logical View Column): MAIN_PROPERTY_USE_TYPE */
AND LIS.MAIN_PROPERTY_USE_TYPE = 10000001073
/** Cond. Role-based Logical Where Clause Item (Logical View Column): SUB_SYS_LOC_ID */
AND LIS.SUB_SYS_LOC_ID = 10000065722
/** Mand. Logical Where Clause Item (Logical View Column): SYS_LOC_ID */
AND LIS.SYS_LOC_ID = 50000900598
)
UNION
(
SELECT LIS.LIS_ID --6th
FROM (
(SELECT /*+ USE_HASH (GEO, LIS) INDEX (GEO GADDR_LOCATION_I1) */ GEO.ID
FROM GEO_ADDRESSES GEO
/** User Item (Logical Attribute): GeoLocation */
WHERE (SDO_WITHIN_DISTANCE(GEO.LOCATION, SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-76.9936839457917, 38.932452896131, NULL), NULL, NULL), 'distance=0.508337473945916 unit=MILE') = 'TRUE'))) GEO,
LISTINGS LIS
WHERE LIS.LIS_ID = GEO.ID
/** User Item (Logical Attribute): LocaleListingStatus */
AND LIS.LLS_ID = 10000069147
/** User Item (Logical Attribute): Type */
AND (LIS.STR_TYPE IN ( 10000734239, 10000001147, 10000001149, 50074309611, 10000001163, 10000001164 ) )
/** User Item (Logical Attribute): ForSale */
AND LIS.MAIN_FOR_SALE_FLAG = 1
/** User Item (Logical Attribute): ListPrice */
AND LIS.MAIN_LIST_PRICE /** User Item (Logical Attribute): Beds */
AND LIS.STR_BEDROOMS >= 2
/** Mand. Logical Where Clause Item (Logical View Column): MAIN_PROPERTY_USE_TYPE */
AND LIS.MAIN_PROPERTY_USE_TYPE = 10000001073
/** Cond. Role-based Logical Where Clause Item (Logical View Column): SUB_SYS_LOC_ID */
AND LIS.SUB_SYS_LOC_ID = 10000065722
/** Mand. Logical Where Clause Item (Logical View Column): SYS_LOC_ID */
AND LIS.SYS_LOC_ID = 50000900598
)
UNION
(
SELECT LIS.LIS_ID -- 7th
FROM (
(SELECT /*+ USE_HASH (GEO, LIS) INDEX (GEO GADDR_LOCATION_I1) */ GEO.ID
FROM GEO_ADDRESSES GEO
WHERE (SDO_WITHIN_DISTANCE(GEO.LOCATION, SDO_GEOMETRY(2001,8265,SDO_POINT_TYPE(-77.0244542476106, 38.9369928800159, NULL), NULL, NULL), 'distance=0.50393590906975 unit=MILE') = 'TRUE'))) GEO,
LISTINGS LIS
WHERE LIS.LIS_ID = GEO.ID
AND LIS.LLS_ID = 10000069147
AND (LIS.STR_TYPE IN ( 10000734239, 10000001147, 10000001149, 50074309611, 10000001163, 10000001164 ) )
AND LIS.MAIN_FOR_SALE_FLAG = 1
AND LIS.MAIN_LIST_PRICE
AND LIS.STR_BEDROOMS >= 2
AND LIS.MAIN_PROPERTY_USE_TYPE = 10000001073
AND LIS.SUB_SYS_LOC_ID = 10000065722
AND LIS.SYS_LOC_ID = 50000900598
)
)
)
WHERE ROWNUM <= 252;

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