I prepare a query, pressed "Explain Plan" button and got error: (translated to Engish by me, sorry) "Impossible to generate execution plan for this expression".
Ofcouse I can explain plan in sql*plus for this query.
I hope, that my message will be helpfull for you.
P.S. I attach my query to this message, may be it will help.
Code: Select all
WITH jammed_distance AS
(SELECT distinct location_id, first_value (corridor_id) over (partition by location_id order by distance asc) corridor_id
FROM gis.gp_corridor_to_gp_location ),
my_corridors AS
(SELECT /*+ MATERIALIZE */ c2l.title_km, c2l.corridor_id,c.DESCR corridor_descr,c2l.location_id
FROM gis.gp_corridor c
JOIN gis.gp_corridor_to_gp_location c2l
ON c.corridor_id = c2l.corridor_id
JOIN jammed_distance jd
ON jd.location_id = c2l.location_id
AND jd.corridor_id = c2l.corridor_id
)
SELECT f.facility_id, f.location_id, lpu.lpu_id, f.obj_cls_id, f.topology_id, f.classifier_id,
l.LAND_LEVEL, l.TUBE_HEIGHT, f.descr, f.service_id, r2l.route_id, r. pipe_id, r2l.line_coord,
gis.km_calc.km_calc(f.location_id, r2l.line_coord, r2l.route_id, r2l.km) km,
l.producer, l.date_create, f.external_link,
f.facility_id as mi_prinx, f.mi_style,l.geoloc,
f.user_insert, f.date_insert, f.user_update, f.date_update, c.title_km, c.corridor_id,c.corridor_descr, l.source_id, l.ACCURACY
FROM gis.gp_location l
JOIN gis.gp_lpu_view lpu
ON lpu.lpu_id = l.lpu_id
JOIN gis.gp_facility f
ON l.location_id = f.location_id
LEFT OUTER JOIN gis.gp_route_to_gp_location r2l
ON l.location_id = r2l.location_id
LEFT OUTER JOIN gis.gp_route r
ON r2l.route_id = r.route_id
LEFT OUTER JOIN my_corridors c
ON l.location_id = c.location_id
WHERE f.obj_cls_id NOT IN ('C1300041', 'C1000043');