Re: [cx-oracle-users] Slow spatial query execution

[Meyer M. <Mat...@ge...>]

Hi Jessica,
Have you looked at the explain plan of the query? Did you create any indexes or partitions on the tables?

The query itself is slow because (as you said) it's complex and because the data set is large, there is no other answer to this question.

Also, you are doing the spatial connection of the 2 tables first and then selecting the records you want. Try it the other way around. First filter out all the records you are interested in and then make the spatial connection. The complex thing is the spatial connection, the less you have to do that, the faster your query. Filtering out records is easy.

Kind regards,

Matthias Meyer



________________________________
Von: Jessica Clarke [mailto:Jes...@fo...]
Gesendet: Montag, 22. Juli 2013 02:38
An: cx-...@li...
Betreff: [cx-oracle-users] Slow spatial query execution

Hi,
I've been banging my head for a week trying to figure out why this query is running so slow.
The query is complex and the data set is quite large.
Simpler queries also take a long time,  but this one in particular takes 11 hours. So I thought if I could figure this one out, I could figure out the formula for the rest.

SELECT count(*)
from (select b.* from PI.PITYPE b, coupe.base_pc_a c where c.provcoupe = 'coupe1' AND SDO_RELATE(b.shape, SDO_GEOM.SDO_BUFFER(c.shape, -1, 1), 'MASK=ANYINTERACT')='TRUE') a
 WHERE a.PITYPE like 'ER5%'
 or a.PITYPE like 'ER6%'
 or a.PITYPE like 'coER5%'
 or a.PITYPE like 'coER6%' OR
 (SUBSTR(a.concisepi,1,1) IN ('A','B','D','G','H')
 AND SUBSTR(a.concisepi,4,1) in ('1','2','3','6','7')
 and SUBSTR(a.concisepi,5,1) in ('A','B','C','D'))

Thanks,

Jess Clarke


[cid:RCEKJYWWPKKC.51e01260.jpg]<http://www.facebook.com/forestrytasmania>[cid:YDFEAHZPMCCK.51e01261.jpg]<https://twitter.com/Forestrytasmnia>[cid:ZESQEASPCCDM.FT_YT.jpg]<http://www.youtube.com/user/Forestrytas>
Forestry creates regional employment:
http://youtu.be/UBIUGiCiX00
Growing tomorrow's high conservation value forests:
http://www.youtube.com/watch?v=wP6XCailI-A
---------------------------------------------------------------
This transmission is intended solely for the person or organisation to whom it is addressed. It is confidential and may contain legally privileged information. If you have received this transmission in error, you must not keep, forward, copy, use, save or rely on this communication. Any such action is unauthorised and prohibited. Please reply to this email to notify the sender of its incorrect delivery, and then delete both it and your reply. No liability is accepted for any unauthorised use of the information contained in this transmission. Despite our use of anti-virus software, Forestry Tasmania cannot guarantee that this transmission is virus-free.


________________________________
GEHE Informatik Services GmbH & Co. KG, Sitz: Stuttgart, AG Stuttgart, HRA 12167, UST-IdNr. DE 811 655 252
Pers?nlich haftende Gesellschafterin: GEHE Pharma Handel GmbH, Sitz: Stuttgart, AG Stuttgart, HRB 14591,
Gesch?ftsf?hrer: Andr? Bl?mel (Vorsitzender), Rainer Baumg?rtner, Dr. Peter Schreiner
Weitere Gesellschafter: GEHE Informatik Services-Verwaltungs GmbH, Sitz: Stuttgart, AG Stuttgart, HRB 15843
Gesch?ftsf?hrer: Rainer Baumg?rtner, Dr. Stefan Grill

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________