db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: SQL/MED
Date Fri, 29 Feb 2008 01:21:55 GMT
Hi Frank,

I'm not aware of any discussion of this topic on derby-dev. I have used 
nabble (http://www.nabble.com/Apache-Database-f105.html) to search the 
email archive for "sql med" and nothing came up. Your proposal sounds 
like a good basis for a first implementation. Here are a couple more 

1) This could grow arbitrarily complicated, depending on how much heavy 
lifting you want the optimizer to do for you. With a little luck, you 
could probably trick the optimizer into treating the foreign tables like 
Table Functions. That would skirt some of the interesting optimizer issues.

2) A later implementation could get the optimizer involved and you could 
build some interesting cost model for remote tables.

3) Alternatively, you might be able to get an optimizer expert like Army 
involved early on. He might be able to help you divide and conquer this one.

You might want to start a wiki page on this topic and take it from there.


Frank Griffin wrote:
> To recap...
> A while back, I asked about accessing foreign data from Derby.  Rick
> very helpfully pointed me to Table Functions, and I've created a
> prototype using Table Functions which accesses my foreign engine.  This
> all works very nicely, within the design capabilities of Table Functions.
> I had pointed out that without some way to push predicates, projection
> and selection would be crippled, and lookup-type activities on large
> result sets would be pretty inefficient.  Rick responded that for that
> type of access to work well, what I probably wanted was SQL/MED.
> So I've gotten the SQL/MED spec and poked through it a bit.  It looks
> like exactly what I'd like.
> Has there been any discussion of implementing SQL/MED in Derby ?  If so,
> was there any resolution ?  From what I can see googling around, I can't
> find any indication that any SQL engine actually provides a reference
> implementation of SQL/MED, in spite of the fact that it's been in the
> spec since 1999 and was enhanced in 2003.
> I would be  interested in working on such an implementation in Derby,
> assuming that the idea hasn't already been discussed and discarded.
> From what I can tell, this would require DDL syntax pretty much like
> what Table Functions required.  CREATE FOREIGN TABLE would be very
> similar to CREATE FUNCTION (TABLE) and the others are pretty simple (as
> they don't require column definitions, and pretty much just specify
> software and connection linkages).
> It would also require walking the SQL parse tree and creating the Value
> Expressions which describe the SELECT elements, the WHERE clause
> components, and so forth. 
> Derby would be required to initialize the MED wrapper and pass a Request
> Handle related to the actual query Derby would like the foreign wrapper
> to execute.  The wrapper uses this Handle to query Derby about the
> individual components of the Request, and accepts those that it can
> handle.  Derby then makes its own arrangements to handle any screening
> that the wrapper can't handle, and generates an execution plan which
> involves calling the wrapper to do the parts of which it has declared
> itself capable.
> Beyond that, the support is very much like what Table Functions already
> supports.  Derby activates the individual query components handled by
> the wrapper, and navigates among the rows using a model pretty much like
> the JDBC ResultSet model used by Table Functions.
> Any interest in this, or am I beating a previously-deceased horse ?

View raw message