db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From drv <drvyv...@hotmail.com>
Subject Re: Limitations of Table Functions vs. old VTIs?
Date Wed, 13 Mar 2013 14:09:06 GMT
Hi Rick - further thoughts below.

(0 - New issue...)
I have found another big issue for us by the way: It appears that Table
Functions do not recognize the IQualifyable and Pushable interfaces anymore
- this is understandable given that RestrictedVTI was intended to replace
them. However, the methods on the 2 old interfaces passed a VTIEnvironment
object which RestrictedVTI.initScan() does not. This object crucially holds
the "orginalSQL" string which we need *at execution time* in GaianDB.
Methods on the VTICosting interface also provide the VTIEnvironment, but
they are only called once by Derby (and on a separate instance of the TF
class), when it fetches stats for the table function. This means that we
would only ever know the original SQL of the FIRST SQL query that referenced
the table function - which is not very useful.
By the way - we need the original SQL for many reasons: e.g.: to extract
HINTs from it and to extract complex predicates (involving functions etc -
which cannot be expressed with a Restriction) - through shallow parsing.

Also - In response to your comments:

Rick> The static typing of table functions is a feature of the ANSI/ISO 
syntax. Each possible table shape needs to be bound by its own CREATE 
FUNCTION statement. I think you have discovered that you can hide the 
messy arguments by wrapping the table function invocations in helper 
views. You may find that varargs routines (to be introduced by Derby 
10.10) will help you get more mileage if you want to write a single Java 
method which generates multiple, independently declared table functions.

=> .. yes, we already wrapped VTI invocations for standardization, e.g, for
"logical table" LT0:
create view LT0 as SELECT * from new com.ibm.db2j.GaianTable('LT0') T

=> By the way - given that this standardization approach works - why has the
support for VTIs been removed? Does it really matter that the nasty syntax
is available? VTIs already provide dynamic "table shapes" - which is a
really nice feature.

=> ... and yes, there are many constructors for the GaianTable class, with
up to 4 arguments - and yes, I already tried declaring the table function
static method using a varargs construct ( Object... args ) which didn't
work, so that would help with version 10.10.

Rick> Are you trying to make the single Java method smart enough to figure
at run time which table shape to return? What about giving the Java 
method an argument which encodes the table shape?

=> That is what I have had to do, yes - but it makes the syntax
repetitive/nasty, e.g:
"select * from table ( lt0('lt0') ) T" ; instead of preferred syntax:
"select * from table ( lt0() ) T".

=> So is there no means of getting the table function name from inside the
java method?

Rick> ... you shouldn't need to implement all of the getXXX() 
methods if you use the building-block table functions which appear in 
Derby's public api: 

=> .. I see what you mean - but I was referring to the methods that we
actually USE: With IFastPath, I only had to implement ONE method. Whereas
with ResultSet (or VTITemplate), I would need to implement/override all the
methods we need, for all the types, as well as the next() method...
IFastPath was really handy in avoiding this and making the code simpler to

Rick> Restrictions are passed through views into RestrictedVTIs, but 
projections aren't yet. It's a tricky problem tracked by 

=> OK. Hopefully there will be a fix for this at some point - the impact on
memory + performance can be considerable.

(Capability to define a SYNONYM on a table function)
Rick> Afraid I'm not following this point. Do you need this as a workaround 
for (4) or do you have some other use case in mind? 

=> This relates to 1) and 4). If we could define a SYNONYM for a table
function (given a fixed set of arguments), then this could replace the
overhead of the VIEW - and yes it would solve 4), because we would no longer
be wrapping it with "SELECT *", e.g:
CEATE SYNONYM LT0_P for TABLE( LT0('with_provenance') ) T

What is the feasibility of this in future?

6) (Issue from my 2nd post - conversion of a Restriction tree to a
Rick> The code for converting a predicate to CNF can be found in

=> Thank you - I'll take a look if/when I'm allowed to (!) - but I'll
probably just code it.

Rick> You are welcome to file additional enhancement requests for table 
functions. We do want to make table functions even more useful. 

In conclusion again - the main issues are the new one (0) and 1). Next is
issue 4), and the others are lower priority.
What is the process to file enhancement requests? What time frame would we
be looking at?
Do you expect VTIs to be removed altogether at some point?

View this message in context: http://apache-database.10148.n7.nabble.com/Limitations-of-Table-Functions-vs-old-VTIs-tp127988p128078.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.

View raw message