Selection analysis of Oracle spatial query

In the previous article, I used a case to demonstrate how the selection is calculated for a field of numeric or string type. It also proves that when the selection of the field value is not the same, it will affect the CBO to choose the final execution plan. For sortable field types, the selection calculation model has been written by many people, but how to calculate the choice of spatial query is seldom studied? Does Oracle produce different choices depending on the scope of the search?

Next, let's look at the problem.

Create tables and use the Sdo_geometry data type to store vector data.

To view the number of records in a table:

To create a spatial index:

CREATE INDEX "Tddcsde". " A3010_ix1 "on" Tddcsde "." DLTB20150705 "(" SHAPE ") Indextype is" Mdsys "." Spatial_index "

Use a rectangle with a latitude-longitude span of 1 as the search condition

Explain plan for SELECT * from DLTB20150705 where Mdsys. Sdo_anyinteract (Shape, Sdo_geometry (2003,4610,null,sdo_elem_info_array (1,1003,3), Sdo_ordinate_array ( 116,32,117,33))) = ' TRUE ';

at this point the execution of the planned cardinality=14300 , and what is the actual number of records returned?

Clearly, it is not the 14300 of the implementation plan .

Now let's make the query larger and set the rectangle with a latitude span of 2 as the search condition.

explain plan for SELECT * from DLTB20150705 where Mdsys. Sdo_anyinteract (Shape, Sdo_geometry (2003,4610,null,sdo_elem_info_array (1,1003,3), Sdo_ordinate_array (116,32,11 8, 34))) = ' TRUE ';

now our query scope is getting bigger, but cardinality still for 14300 . And what is the actual number of records returned at this time?

It is true that more records are returned than rectangles with latitude and longitude spans of 1 , But even so, the cardinality of the execution plan is consistent with the previous.

So far, what we've seen is the result: spatial queries of different spatial scopes, whose cardinality values do not change. Now I can't help but ask, how does this value come to be calculated? Then you have to analyze the 10053 event.

Analysis TDDC_ORA_8438.TRC file, found cardinality The calculation method is as follows:

from the Plsql BLOCK can be found that the spatial query scope as input information into the code snippet, the final choice rate is 1/100 . From the calculation method, it seems that the query scope will affect the result of the final selectivity calculation, which is actually not the case. We changed the range from 1 degrees to 2 degrees, and then we got 10053 's TRC. :

Analysis TDDC_ORA_8525.TRC file

The range becomes larger, but the selection is still 1/100 .

Let's go back to the previous example,

and DLTB20150705 The number of records in the table, based on the previous query, is:

and 14300 = Round (1430023 * 0.01)

Conclusion:

1 , sdo_anyinteract the selection of the operator is constant 1/100 . The selection of other operators remains to be verified.

Selection analysis of Oracle spatial query