Re: Random Sample Using SQL?

In article <33BC2640.1368_at_iol.ie>,
  cellis_at_iol.ie wrote:
>
> Kendall Willets (TV DBA) wrote:
> >
> > Brian Ewins wrote:
> > >
> > > Kendall Willets (TV DBA) wrote:
> > > How about using a random function of ROWNUM? That would provide you
> > > > (snip)
> > > >
> > >
> > > This won't work. Consider: until Random_fn(n)<=.10, ROWNUM will never
> > > reach n+1. And unless Random_fn(1)<=.10 you generate *nothing* at all.
> >
> > Correct. Another person emailed me with this info and suggested a
> > solution: use a view which includes rownum as a column (haven't tested
> > this yet).
> >
> > (The problem is that rownum is only incremented when a row is returned -
> > not each time one is tested as above)
> >
> > (time granularity is to the second). However, creating a numeric
> > > hash from a string representation of a rowid, and the time, could
> > > give you a pretty random number. I'll leave this as an excercise ;o)
> > >
> > I like the rowid suggestion. One related idea which might work with
> > oracle and other db's supporting hashed tables:
> >
> > (snip)
>
> There is a simple and elegant solution to this problem.
> I (along with many others) have a PL/SQL random number generator
> (reurning a number between 0 and 1 - 1.0E38).
> Create an overlaid definition of the function which is defined to take
> an argument of datatype rowid. This argument does not need to be used
> (or even referenced) in the overlaid definition, which merely returns
> the random value generated by the original function.
>
> Now a predicate of the form:
>
> where random_function(rowid) < 0.1
>
> may be used on any table to return (approximately) 10% of the rows
> without the function needing to reference any column values or datatypes
> or locations. The function must be evaluated for every row returned by
> the select statement because it references a database object (rowid),
> although the object is not used.
>
> Chrysalis.

I believe the problem is not in forcing the function to evaluate each row, but rather in obtaining a seed for each row. Procedural implementations of random functions usually use the result of the current call as the seed for the next call. That's OK in PL/SQL programs, but ORACLE will not allow that for functions used in basic SQL--due to side effects. It won't allow you to save the seed in a package variable to be used on the next call.

What seed does your random function use? What is an "overlaid" function?  If that means simply one function calling another, and the original function violates the side effect rule, it still won't work. Oracle checks the first function and any that it calls, and so on.

Thanks.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet