Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of gcaddd-derby-dev@m.gmane.org
 designates 80.91.229.2 as permitted sender)
To: derby-dev@db.apache.org
From: David Pickett <David.Pickett@PHLX.com>
Subject: Re: new create-statement
Date: Tue, 19 Jul 2005 17:24:04 +0000 (UTC)
Lines: 34
Message-ID: <loom.20050719T190123-833@post.gmane.org>
References: <22581.1121778791@www23.gmx.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: Loom/3.14 (http://gmane.org/)
Sender: news <news@sea.gmane.org>

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