Plan table option
Brought to you by:
uwe_ewald
Menu
▾
▴
#23 Plan table option
Hi,
Is it possible to implement a graphical view of the
execution plan of DML statement ? (We need this option
on 3 DBMS : Oracle,SQLServer, and DB2)
This option allow to optimize DML query
(insert,select, delete, update) and was used
frequently by programmers.
For example in oracle, a good example of the
implementation of this option is TOAD.
The command is :
EXPLAIN PLAN SET STATEMENT_ID = 'TEST' --into
PLAN_TABLE
FOR SELECT * FROM EMP WHERE DEPTNO = 10;
and use by default PLAN_TABLE which looks like
this:
Name Null? Type
------------------------------- -------- ----
STATEMENT_ID CHAR(30)
TIMESTAMP DATE
REMARKS CHAR(80)
OPERATION CHAR(30)
OPTIONS CHAR(30)
OBJECT_NODE CHAR(30)
OBJECT_OWNER CHAR(30)
OBJECT_NAME CHAR(30)
OBJECT_INSTANCE NUMBER
(38)
OBJECT_TYPE CHAR(30)
SEARCH_COLUMNS NUMBER
(38)
ID NUMBER
(38)
PARENT_ID NUMBER
(38)
POSITION NUMBER
(38)
OTHER LONG
You can after query your plan table with
STATEMENT_ID.
For SQLServer the option is :
SET SHOWPLAN_TEXT ON
SET STATISTICS IO / TIME
select * FROM EMP WHERE DEPTNO = 10;
For DB2 the option is (some like Oracle) :
EXPLAIN ALL SET QUERYNO = 'TEST'
FOR SELECT * FROM EMP WHERE DEPTNO = 10;
and use by default PLAN_TABLE in user schema.
Thanks.
Logged In: YES
user_id=582288
Sure this is a very interesting request; I would rather see
this as a separate plugin that may extend the dbedit.core.
The latest changes in the core which are not yet bundled as
a release lay the basis to have db specific functionality
beyond JDBC. These changes may be needful when establishing
a common model for execution plans.
But because I am the only person working on the Dbedit code
I think this request will not become reality, hard enough to
keep me motivated.
Regards,
Uwe