select query in from clause creates problem

[sunil divyeshvar]

Hi all,

I am facing problem with following query ,
i want to know is that efficient to create index on varchar column ? not in creates problem here and the select query in from clause takes time as i think so.



please check my following query and try to help to tune it takes nearly 400 seconds,Index is created on TERABITZ_ID where ever it uses.




select p.ZIP AS NHOOD,
p.CITY AS CITY,
p.STATE AS STATE,
psub.LATITUDE AS LATITUDE,
psub.LONGITUDE AS LONGITUDE,
COUNT(p.TERABITZ_ID) AS TOTALPROP,
AVG(p.CURR_LISTPRICE) AS AVGPRICE,
psub.NHOODPROPCNT as NHOODPROPCNT

from SCHUBB.property_ptnf p,
SCHUBB.features_ptnf f,

(select distinct ptbl.ZIP,ptbl.CITY,ptbl.STATE,AVG(ptbl.LATITUDE) as LATITUDE,
AVG(ptbl.LONGITUDE) as LONGITUDE,
COUNT(ptbl.TERABITZ_ID) as NHOODPROPCNT
from property_ptnf ptbl FORCE INDEX (idx_lat,idx_long),
features_ptnf ftbl
where ptbl.TERABITZ_ID=ftbl.TERABITZ_ID AND
(ptbl.LATITUDE between 37.913867495923746 and 37.98222771312011) AND
(ptbl.LONGITUDE between -122.11870193481445 and -122.03681945800781) AND
ptbl.LATITUDE<>0 AND ptbl.LONGITUDE<>0 AND
ptbl.BEDS >= 2 AND ptbl.BEDS <= 4 AND
ptbl.BATHS >= 2 AND ptbl.BATHS <= 3 AND
ptbl.STATUS in ('Active','Contingent - Show','Leased/Option','Contingent - Release','Pending','Active Short Sale',
'Pending with Backup','Active Short Cont.','Active Court Cont.','Active Court Appr.','Active Rel. Clause',
'Price Change','Contingent','Back on Market','New','Contingent - Probate') AND
ptbl.DATASOURCE not in ('REINFOLINK','SFAR','ABRMLS','SOCAL','TSMLS','METROLIST','INCLINEVILLAGE')
GROUP BY ptbl.ZIP,ptbl.CITY) as psub

where p.TERABITZ_ID=f.TERABITZ_ID AND
p.ZIP<>'' AND
p.ZIP=psub.ZIP AND
p.CITY=psub.CITY AND
p.STATE=psub.STATE AND
p.BEDS >= 2 AND p.BEDS <= 4 AND
p.BATHS >= 2 AND p.BATHS <= 3 AND
p.STATUS in ('Active','Contingent - Show','Leased/Option','Contingent - Release','Pending','Active Short Sale',
'Pending with Backup','Active Short Cont.','Active Court Cont.','Active Court Appr.','Active Rel. Clause',
'Price Change','Contingent','Back on Market','New','Contingent - Probate') AND
p.DATASOURCE not in ('REINFOLINK','SFAR','ABRMLS','SOCAL','TSMLS','METROLIST','INCLINEVILLAGE')

GROUP BY p.ZIP,p.CITY

HAVING NHOODPROPCNT > 2

ORDER BY TOTALPROP DESC;