db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Felix Beyer <s4224...@mail.inf.tu-dresden.de>
Subject Explain functionality, final results & feedback
Date Thu, 01 Mar 2007 16:50:08 GMT
Hi Derby Community,

some time ago I proposed some extensions to Derby, which allow easy 
query explanation. Now I´m back with a backward-compatible, extensible, 
efficient and working solution for my proposals.

To give you a quick and short overview of the code extensions, please 
look at the attached xplainClasses.pdf.
To use the extension my solution adapts the old runtimestatistics 
facility. By default the old contract still holds and captures 
resultsetstatistics(stats) after activation through
These stats are still available via

In my extension i`ve included some new system procedures which allow the 
new persistent and common capture of the same stats in 6 new created 
system tables. Their phyiscal implementation can be seen in the physical 
XPLAIN schema picture attached. In order to achieve flexible explanation 
I extended the old stats with a visitor pattern to be able to traverse 
the stats. For the persistent explanation of queries I´ve created a 
special visitor which first traverses the stats after their creation and 
second creates the different system catalog row descriptors to fill the 
new system catalogs via the DataDictionary. (more details are coming, if 
you`re interested)

To use the persistent style of explanation you have to activate it via 
the new system procedure
CALL SYSCS_UTIL.SYSCS_SET_XPLAIN_STYLE(1) (btw 0 is default and means 
old explanation style)

Now one can send statements to derby which get explained into the new 
system catalogs.
If you only wish to have a look at the query plan without execution then 
use the procedure
CALL SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE(1). That toggles Derby into a 
sandbox mode, which only captures the stats without executing DQL 
queries. This behaviour is the method to get fast feedback.
The parameter 1 means "explain only without execution" and
the parameter 0(default) means "explain and execute" the query.

After explanation the user can query the new system catalogs and browse 
through the explained queries using plain SQL. (see schema picture)

Finally I`ve included a screen shot of our demo application, which was 
extended to be able to print out the query plan in a DB2-similiar way 
and color code. The picture should answer all arising questions about 
the possibilities of the new explain functionality.

I`d like to contribute the explain functionality and a cut version of 
the demo (without the sampling stuff) to the Derby community.
What do I have to do to successfully provide my code?
By the way, the extension is so modular that the integration would be 
only one day work (was a design requirement)

Any feedback?

Felix Beyer

View raw message