Error with explain plan

Discussion of open issues, suggestions and bugs regarding database management and development tools for Oracle
Post Reply
Posts: 6
Joined: Tue 23 Oct 2007 06:50

Error with explain plan

Post by trak » Tue 23 Oct 2007 11:31

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,   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');

Devart Team
Posts: 165
Joined: Wed 10 Aug 2005 08:30

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

Post Reply