Optimize SQL query using ST_GEOMETRY

JeffPace1

Looking for help to optimize a SQL query in ORACLE.  What this does is

Take two polygon layers, overlay/intersect them, and where they intersect, summarize the attributes of the overlay to a aggregate list if the area of intersection is more than 15% of the source poly.  

SELECT

hashid,

--this is an aggregate function that sorts and groups overlayed floodzones by parcel
LISTAGG (TO_CHAR (fld_zone), '','') WITHIN GROUP (ORDER BY fld_zone) FLOODZONE
FROM MCGIS.FEMA_2014_FLOOD_ZONES e, parcel.parcels_mv p
WHERE

--get matching polygons

st_intersects (p.shape, e.shape) = 1
AND

--use polygon if more than 15% overlay

(st_area (st_intersection (p.shape, e.shape))) / st_area (p.shape) >.15

--summarize by hashid
GROUP BY hashid

It works perfectly, but takes like 3 hours.  i have 30 of these to do every night.  Anything i can do to speed it up? Both layers have spatial indexes, and there are attribute indexes on both HASHID and FLD_ZONE fields.

This produces results like

HASHID    FLOODZONE

124345      AE,X

124346      X

124347      AE,B,X

instead of 

HASHID    FLOODZONE

124345      AE

124345      X

124346      X

124347      AE

124347      B

124347      X

bixb0012

Take two polygon layers, overlay/intersect them, and where they intersect, summarize the attributes of the overlay to a aggregate list if the area of intersection is more than 15% of the source poly.  

You have already laid out the overall geoprocessing steps, you just need to script them out.  The most expensive step will be the intersection at the start, knowing how long it takes to Intersect—Help | ArcGIS Desktop the two layers will tell you whether it is worth pursuing scripting it out vs using SQL.  So, how long does it take?