Error with explain plan

Error with explain plan

Postby trak » Tue 23 Oct 2007 11:31

Hi!
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');
trak
 
Posts: 6
Joined: Tue 23 Oct 2007 06:50

Postby Alexz » Thu 25 Oct 2007 08:23

In fact, in the current version we allow generating plan only for SELECT, UPDATE, INSERT, DELETE, CREATE TABLE/INDEX or ALTER INDEX ... REBUILD statements. But only if these keywords are the first in the statement.
We'll fix this bug in the next build.
Alexz
Devart Team
 
Posts: 165
Joined: Wed 10 Aug 2005 08:30


Return to dbForge for Oracle