The table survey_files_with_lat_long has about 11.5 million rows with a shape type of 2D point.
I'm running this on 12.2 - with all objects set to degree default. So with this SQL:
SELECT /*+ ORDERED PARALLEL */
s.well_number,
'10 MILE' AS radius,
sdo_nn_distance (1) distance_in_feet,
s.latitude,
s.longitude,
s2.well_number neighboring_well_number,
s2.latitude neighboring_latitude,
s2.longitude neighboring_longitude
FROM insite.survey_files_with_lat_long s,
insite.survey_files_with_lat_long s2
WHERE s.md = 0
AND s2.md = 0
AND s.well_number <> s2.well_number
AND sdo_nn (s2.geo_point,
s.geo_point,
'distance=52800 unit=foot sdo_max_memory=1000000',
1) = 'TRUE'
ORDER BY sdo_nn_distance (1);
I get the expected resulting plan:
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 53959 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 306 | 73440 | 53959 (1)| 00:00:03 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 306 | 73440 | 53959 (1)| 00:00:03 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 306 | 73440 | 53958 (1)| 00:00:03 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 306 | 73440 | 53958 (1)| 00:00:03 | Q1,00 | P->P | RANGE |
| 6 | NESTED LOOPS | | 306 | 73440 | 53958 (1)| 00:00:03 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | SURVEY_FILES_WITH_LAT_LONG | 554 | 66480 | 592 (1)| 00:00:01 | Q1,00 | PCWP | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| SURVEY_FILES_WITH_LAT_LONG | 1 | 120 | 53958 (1)| 00:00:03 | Q1,00 | PCWP | |
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | | Q1,00 | PCWP | |
| 11 | BITMAP AND | | | | | | Q1,00 | PCWP | |
| 12 | BITMAP CONVERSION FROM ROWIDS | | | | | | Q1,00 | PCWP | |
| 13 | SORT ORDER BY | | | | | | Q1,00 | PCWP | |
|* 14 | INDEX RANGE SCAN | IDX_SURVEY_FILES_WITH_LL_MDWN | 554 | | 4 (0)| 00:00:01 | Q1,00 | PCWP | |
| 15 | BITMAP CONVERSION FROM ROWIDS | | | | | | Q1,00 | PCWP | |
| 16 | SORT ORDER BY | | | | | | Q1,00 | PCWP | |
| 17 | DOMAIN INDEX (SEL: 0.100000 %)| SURVEY_FILES_WITH_LAT_LO_SIDX | 554 | | 122 (0)| 00:00:01 | Q1,00 | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access(:Z>=:Z AND :Z<=:Z)
filter("S"."MD"=0)
14 - access("S2"."MD"=0)
filter(("S"."WELL_NUMBER"<>"S2"."WELL_NUMBER" AND "S2"."MD"=0))
Note
-----
- Degree of Parallelism is 96 because of table property
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
However, when I go to insert the results in a table with this added on (with or without parallel or append or memory parameter), it always runs serial:
insert /+* APPEND */
into insite.grid_calc_result_wellbores2 ( well_number,
radius,
distancein_feet,
latitude,
longitude,
neighboring_well_number,
neighboring_latitude,
neighboring_longitude)
SELECT /+* ORDERED PARALLEL */
s.well_number,
'10 MILE' AS radius,
sdo_nn_distance (1) distance_in_feet,
s.latitude,
s.longitude,
s2.well_number neighboring_well_number,
s2.latitude neighboring_latitude,
s2.longitude neighboring_longitude
FROM insite.survey_files_with_lat_long s,
insite.survey_files_with_lat_long s2
WHERE s.md = 0
AND s2.md = 0
AND s.well_number <> s2.well_number
AND sdo_nn (s2.geo_point,
s.geo_point,
'distance=52800 unit=foot sdo_max_memory=1000000',
1) = 'TRUE'
ORDER BY sdo_nn_distance (1);
Plan:
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 86634 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | GRID_CALC_RESULT_WELLBORES2 | | | | |
| 2 | SORT ORDER BY | | 306 | 73440 | 86634 (1)| 00:00:04 |
| 3 | NESTED LOOPS | | 306 | 73440 | 86633 (1)| 00:00:04 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| SURVEY_FILES_WITH_LAT_LONG | 554 | 66480 | 560 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_SURVEY_FILES_WITH_LL_MDWN | 555 | | 5 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| SURVEY_FILES_WITH_LAT_LONG | 1 | 120 | 86633 (1)| 00:00:04 |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 8 | BITMAP AND | | | | | |
| 9 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 10 | SORT ORDER BY | | | | | |
|* 11 | INDEX RANGE SCAN | IDX_SURVEY_FILES_WITH_LL_MDWN | 554 | | 4 (0)| 00:00:01 |
| 12 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 13 | SORT ORDER BY | | | | | |
| 14 | DOMAIN INDEX (SEL: 0.100000 %)| SURVEY_FILES_WITH_LAT_LO_SIDX | 554 | | 122 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("S"."MD"=0)
11 - access("S2"."MD"=0)
filter(("S"."WELL_NUMBER"<>"S2"."WELL_NUMBER" AND "S2"."MD"=0))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
So the question is - how can I run SDO_NN in parallel for this essentially N*N problem to get good performance - and store the results?
Bryan