[cx-oracle-users] Slow spatial query execution

[Jessica C. <Jes...@fo...>]

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
 

---------------------------------------------------------------
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.