Frequently asked question
Is it necessary to use the optimizer hint /*+ ORDERED */ with relational operators in Oracle?
Answer
When writing an Oracle SQL statement that contains an st_geometry relational operator (for example: st_intersects, st_contains, st_within), there is no need to include the optimizer hint ORDERED. The optimizer hint ORDERED influences the optimizer in selecting an execution plan to drive the predicate filter in the order of how the tables are specified in the FROM clause.It is bad practice to hint the optimizer in selecting a specific access path verses considering all other options. The Oracle optimizer is very powerful and intelligent in understanding a table's data distribution and an SQL statement's predicate filter to derive a selectivity based upon the input value. Allowing the optimizer to make the best choice for deriving an execution plan ensures that the SQL statement is executed efficiently and with the least cost and resources consumed. When a hint is specified, it could actually lead to a slower access path than what the optimizer would have selected.
Take, for example, the following SQL statement:
Code:
SELECT chemical_plants.name
FROM wells, chemical_plants
WHERE sde.st_intersects(chemical_plants.shape, sde.st_buffer(wells.shape, 5)) = 1
AND wells.type IN (1,2)
The Oracle optimizer inspects each predicate, derives a selectivity, and the cost for performing the operation. In this case, the comparision of the cost between finding all wells that have a type of value 1 or 2 and the cost of finding all wells that are within a distance of 5 units to chemical_plants. Based on which filter has the lower cost, the predicate filter becomes the access path (by way of the predicate's attribute's index) to the query, and the second predicate becomes a filter applied to the rows that are returned from accessing the index.
Therefore, if the cost, which is derived from the selectivity of the filter, of finding all wells of type 1 or 2 is less than the spatial relational operator cost, then each well's geometry will be passed to the sde.st_buffer operation and intersected with the chemical plants. This access path using the wells type index makes sense when the number of wells that meet the predicate filter's value is a small set. If 90% of the wells type value equals 1 or 2, the cost of executing the statement and the resources consumed by the query would increase. To process the statement, Oracle would use the wells table's type index to discover all rows that satisfy the predicate (type equals 1 or 2) and then pass each shape to the relational operator. Instead, processing the statement would have been faster to perform the relational operator first, comparing all wells in relation to chemical_plans, and then applying the wells type filter against the rows that satisfied the relational operator.
If the SQL statement contained the Oracle hint ORDERED,
Code:
SELECT /*+ ORDERED */ chemical_plants.name
FROM wells, chemical_plants
WHERE sde.st_intersects(chemical_plants.shape, sde.st_buffer(wells.shape, 5)) = 1
AND wells.type IN (1,2)
then the access path would always be to apply the wells predicate filter first, as described above, which might be inefficient and consume more resources than necessary.
The ORDERED hint should not be specified because ArcGIS's st_geometry type has been extended to help the Oracle optimizer calculate a selectivity and cost when using a relational operator. The correct selectivity and cost is critical for the Oracle optimizer to allow it to make the correct decision in selecting an execution plan when parsing an SQL statement.
If ArcGIS did not extend the st_geometry type to work with Oracle's optimizer, Oracle, by default, will set the cost of the relational operator to be 2 and the selectivity of the predicate to be 10%. It is Oracle extensibility that allows a developer of a type and domain index the ability to set cost and selectivity.
However, because the default optimizer cost is underestimated (only in the case when a type, and domain index and operators have not been extended to work correctly with the optimizer), the optimizer will likely select the access path to always be the relational operator first and apply predicate filters second. This can lead to very sub-optimal performance and is why ESRI does not recommend setting the ORDERED hint. This contradicts what other spatial type implementations strongly recommend: to set the hint because of the unexpected performance impact when the relational operator becomes the primary access path for the execution plan.
Article ID:000010255
Software:
- Legacy Products
Get help from ArcGIS experts
Download the Esri Support App
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback