Hello,
here is a follow up question to Using st_geomfromwkb in PL/SQL:
I created a PL/SQL function that translates sdo_geometry to sde.st_geometry:
<SPAN class="keyword token">create</SPAN> <SPAN class="operator token">or</SPAN> replace
<SPAN class="keyword token">function</SPAN> tostgeom<SPAN class="punctuation token">(</SPAN>sdogeom <SPAN class="operator token">in</SPAN> sdo_geometry<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">return</SPAN> sde<SPAN class="punctuation token">.</SPAN>ST_GEOMETRY
<SPAN class="operator token">is</SPAN>
<SPAN class="keyword token">BEGIN</SPAN>
<SPAN class="keyword token">return</SPAN> SDE<SPAN class="punctuation token">.</SPAN>ST_GEOMETRY_OPERATORS<SPAN class="punctuation token">.</SPAN>st_geomfromwkb_f<SPAN class="punctuation token">(</SPAN>sdo_geometry<SPAN class="punctuation token">.</SPAN>get_wkb<SPAN class="punctuation token">(</SPAN>sdogeom<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">25832</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
EXCEPTION
<SPAN class="keyword token">WHEN</SPAN> OTHERS <SPAN class="keyword token">THEN</SPAN>
<SPAN class="keyword token">return</SPAN> <SPAN class="token boolean">null</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">END</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
I need this function because some geometries are "broken" and can't be translated to st_geometry. (Testing the sdo_geometry with sdo_util functions reveals no problem)
Then I call this function in SQL:
<SPAN class="keyword token">create</SPAN> <SPAN class="keyword token">table</SPAN> test <SPAN class="keyword token">as</SPAN>
<SPAN class="keyword token">select</SPAN> id<SPAN class="punctuation token">,</SPAN>tostgeom<SPAN class="punctuation token">(</SPAN>GEOMETRIE<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> geometrie <SPAN class="keyword token">from</SPAN> my_polygons
<SPAN class="keyword token">where</SPAN> rownum<SPAN class="operator token"><</SPAN><SPAN class="number token">8000</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
The results are as expected, but the execution time ist very slow and gets slower (relatively) the more rows I include via "rownum".
For example:
1000 rows take 1.7 seconds
2000 rows take 3.6 seconds
4000 rows take 7.5 seconds
8000 rows take 17.5 seconds
As you can see, the execution "time per row" gets gradually slower for more rows.
If I use it on the full ~2 millon rows, it never finishes.
What could be the problem here?
I would expect the time per row to get better instead of worse for bigger datasets, beause of lesser overhead?!?
Thanks for any thoughts!
