Oracle Spatial Zoning Application Study II: Comparison of test reports by county partition and by province partition

1. 1, the purpose of the experiment

In the previous round of experiments, the Oracle 11g R2 version, based on the 87 county and city experimental data, compared the efficiency of the partition and partitioning, and obtained the conclusion that the partition + global index is more efficient (see previous blog post). However, we have not compared the efficiency differences between different partition granularity. This round of experiments focuses on the following purposes:

1. Bring the experimental scene closer to the real-world usage scenario-using Oracle 12c to experiment with a larger amount of data.
2. Compare and analyze the efficiency of query by county partition and by province partition.
3. Continue to compare the query efficiency of the local spatial index and the global Spatial index under different algorithms.

1. 2. Experimental data

The experimental data are 2,531 counties and districts in the country, the total factor is 46982394. According to the different Data organization + index form, 3 different experimental subjects were formed:

• by County partition + local Spatial index
• by County partition + Global Spatial Index
• by Province partition + local Spatial index

1. 3. Experimental methods

At the 1:500, 1:2000, 1:10,000, 1:25,000, 1:50,000, 1:100,000 scale, randomly selects 3 sample ranges from Nationwide, as the query scope of spatial query. In this paper, the spatial query operation of 6*3 sample range with 3 experimental subjects is carried out, and the time of each query is recorded.

The algorithm used in spatial queries is still the same as that of the previous blog, "One of theOracle spatial zoning applications: Comparison of table and partition Performance" , 3 algorithms for partitioning, namely Part_query, Part_query2, Part_ Query3. At the same time, in this experiment, 3 kinds of algorithms are derived by the parallel framework, and 3 kinds of algorithms are obtained, which are labeled Part_query_p, Part_query2_p and part_query3_p.

Therefore, for each experiment subject, 6 kinds of algorithms are needed to perform the query operation in each scale sample. In addition, because the algorithm executes sequentially, the algorithm that executes after the execution is advantageous to the algorithm that is executed first because of the reason of the cache. To avoid this interference, the algorithm performs two sets of experiments in different order of execution.

1. 4. Experimental results
   1. 4.1 First set of experimental results

In the first set of experiments, the Order of algorithm execution is:

Part_query→ part_query2 → part_query3→ part_query_p→ part_query2_p→ Part_query3_p

Execution results such as:

Description: The blue area in the table is the query efficiency of the county partition + local spatial index at different scales and different algorithms; Similarly, the red area represents the county partition + Global Spatial index, and the Green Zone represents the provincial partition + local space index. A yellow patch represents the minimum value of the row.

Depending on where the yellow patches are located, you know:

1. At all scales, partitioning + local spatial indexing is the most efficient, with all the least time-consuming queries occurring in that region.
2. The part_query_p algorithm has the highest query efficiency, with 18 experimental samples and a minimum time-to-hit of 17.

1. 4.2 Second set of experimental results

In the second set of experiments, the Order of algorithm execution is:

Part_query_p→ part_query2_p → part_query3_p→ Part_query→ Part_query2→ Part_query3

Execution results such as:

Depending on where the yellow patches are located, you know:

1. At all scales, partitioning + local spatial indexing is the most efficient, with all the least time-consuming queries occurring in that region.
2. The part_query algorithm has the highest query efficiency, with 18 experimental samples and a minimum time-to-hit of 18.

1. 4.3 Additional instructions

Two kinds of experiments, the most efficient conclusion of part_query_p and part_query algorithm were obtained. This seems contradictory, in fact, as mentioned above, when the algorithm executes in order, it will be cached for the reason. So who is more efficient for part_query_p and part_query?

In two groups of experiments, part_query_p and Part_query were the first algorithms to be executed. The experimental data of part_query_p and Part_query were removed from two sets of experimental results, and the effect of cache was almost completely excluded.

Two algorithms, each hit 9 times. Explain the efficiency of the equivalent. However, it is obvious that the part_query has a higher hit ratio (greater than 1:25,000) in the large scale, and part_query_p in the small scale. This is in line with our understanding that parallelism will be an advantage when it comes to large-task operations.

1. 5. Experimental conclusion
1. In Oracle 12c environments, partitioning + local spatial indexing is more efficient when the number of features is 40 million levels.
2. The part_query algorithm is more efficient when it is organized by partitioning + local spatial data.

(not to be continued)

Oracle Spatial Zoning Application Study II: Comparison of test reports by county partition and by province partition