Wrong EXPLAIN PLAN ordering

[Christo Kutrovsky]

PL/SQL developer's explain plan query ordering is not correct. Sometimes the order of the plan will not be correct.

For example, when having a HASH-JOIN subkey, the tables will be replaced. (the hashed table will be second...).

The query you are using is:

select level, plan_table.* from plan_table where statement_id = 'TEST'
connect by prior id = parent_id and statement_id = 'TEST'
start with id = 0 and statement_id = 'TEST'
;

The proper query to use would be:

select level, plan_table.* from plan_table where statement_id = 'TEST'
connect by prior id = parent_id and statement_id = 'TEST'
start with id = 0 and statement_id = 'TEST'
order by id, position
;

I have some ideas on improving explain plan. If you are willing to do changes on the entire window please let me know.

I am also willing to throw in some developement help if you are interested.

 

[Marco Kalter]

We'll check it out. Feel free to share your explain plan ideas.

 

[whip]

I think it is either "ORDER BY ID" or "ORDER SIBLINGS BY POSITION".

POSITION gives the order of rows with the same PARENT_ID. ID is unique for a given plan.

 

[Marco Kalter]

I think it should be ORDER BY ID. That's how we have modified it for 6.0.4.