new create-statement

[Anja Klein <Ja...@gmx.de>]

Hi! 
 
For my thesis (diploma) i have to implement an extension of derby. I would 
like to implement a further CREATE-statement for the production of data 
samples. Thus for example the instruction "CREATE SAMPLE ON TABLE x" 
should create a sample of the data from table x. Furthermore I would like 
to create a new Catalog-Table like SYSSAMPLES. 
 
I added the new Tokens i need to the existing parser, which creates the 
associate querytree. My problem is, where and how this querytree is to be 
executed. 
 
I tried to follow the way of execution of a normal create-table-statement, 
but i couldn´t understand it. There are so many functions/classes 
etc., that i don´tknow where to look. 
 
Could someone give me a hint or explain the structure of derby´s execution 
part? I would be grateful also for an overview of the general structure of 
derby. 
 
Thanx a lot! 
Bye, Anja 
 
 

-- 
Anja Klein

GMX DSL = Maximale Leistung zum minimalen Preis!
2000 MB nur 2,99, Flatrate ab 4,99 Euro/Monat: http://www.gmx.net/de/go/dsl

Re: new create-statement

[David Pickett <Da...@PHLX.com>]

PS: Oracle uses the sample function to estimate cardinality for index tuning 
(Once: ALTER TABLE x ESTIMATE STATISTICS, now done with a procedure).  If Derby 
gets a sample function, it'd be nice to make a tuneup tool that sets 
cardinality stats based on a sample.  Of course, in the case of unique columns, 
you extrapolate them as unique, in the case of one value, you extrapolate one 
value, and in between, something appropriately in between!

I think the Oracle sample or something like allowed a dumb sample of the first 
N rows.  One less invasive sample speedup would to be to get N of the first M 
rows.  The sampling user would presumably know something of the randomness of 
the data sampled, so their time column samples might be low, but others might 
be uniform.

For faster, better samples on really big tables, you really need the block 
(page) sample, where you can look at n% of the rows in n% of the table scan 
time!

Re: new create-statement

[David Pickett <Da...@PHLX.com>]

Anja,

I'm no expert there, but it sounds like you could do this in two pieces:

 - A 'create table TABLENAME as select . . . .' to build tables out of queries, 
a very nice and established feature of many RDBMS.  It might be easier to do 
this as a call first, and then figure out how to integrate it into the syntax 
and update the docs to match.  The powers that be might prefer to keep it out 
of the syntax, if it is not DB2 or SQL-92 or whatever compliant.

 - A boolean 'sample' stateful function you can put in the 'where' clause to 
reduce the row count according to your algorythm.  You might think it needs 
some way to tell which state to use, like a detected query number or something, 
but if you do 5 querys of 15%, the '15% state' can be reused wth no ill effect, 
so you just need one state per sample rate, and a discard function for aged 
sample rates, so the state does not grow for every different sample rate used.  
A look at the Oracle SAMPLE featuremight be worthwhile.  They have a block 
option so you can sample whole blocks (pages) in very large tables, making the 
sample grow more quickly, but a feature that would be much more invasive to the 
server code.  As I remember, you can sample by percentage or total output rows, 
but of course it all boils down to a fraction.  I think many RDBMS have a row 
count of some quality in a control table to change count into the fraction.  It 
simplifies things if the sample request of N rows does not guarantee N rows, 
but rather a close approximation!  I am not favoring their semantic style, just 
pointing to existing feature capabilities.  A where clause boolean function is 
less SQL language invasive, and seems quite sufficient.  It seems like the SQL 
language has a COBOL-esque traditional flavor, where every feature invades the 
syntax, but modern languages have chosen to work through available syntax where 
possible, and add extensibility not a new key word every month (or worse yet, a 
new meaning to an old key word).

Good Luck!

David