Re: Developers and SQL

Perhaps I was too quick in tweeting the words "apparently competent" to you!

Cary

On Wed, Jan 27, 2010 at 9:32 AM, Adam Musch <ahmusch_at_gmail.com> wrote:

> Shazbot!
>
> The SQL Antipattern's book's got to two gonzo errors in the randomness
> excerpt:
>
> "In Oracle, you can [select a random row] this way:"
>
> $offset = $pdo->query("SELECT dbms_random.value(1,
> (SELECT COUNT(*) FROM Bugs) AS offset FROM dual)")->fetch();
> $stmt = $pdo->prepare("SELECT * FROM Bugs WHERE ROWNUM = :offset" );
> $stmt->execute( $offset );
> $rand_bug = $stmt->fetch();
>
> Error the first: select dbms_random.value(1, select count(*) from
> [table]) from dual does not return an integer value, at least not in
> 10gR2.
>
> Error the second: select * from [table] where rownum = :offset will
> only ever work if :offset= 1.
>
> The query'd need to be rewritten as
>
> select * from (select a.*, rownum as rn from [table] a where rownum <=
> :offset) where rn = :offset
>
> to pull only the specific row. The performance of such a query is
> problematic to boot, as it's in my test case, it's going to full scan
> the table to get that one row. If it's a big table, that's not good.
> It'd might be better to find the primary key value at some random
> offset in the table, and probe the table for that.
>
> The other excerpt I looked at (Naive Trees) seemed to be completely
> unaware of the concept of a heirarchical query, or even ANSI recursive
> queries using the WITH clause.
>
> I weep for the future if that book receives widespread adopting in the
> developer community, and with the distinctive aroma of patterns on it,
> I'm sure that it will.
>
> On Tue, Jan 26, 2010 at 7:00 PM, Jared Still <jkstill_at_gmail.com> wrote:
> > Here's some interesting reading material that will be
> > of interest both to DBAs and Developers.
> > I am not trying to start any controversy, but just providing
> > links to what should be interesting reading
> > Bridging the developer/DBA divide
> > SQL Antipatterns : Avoiding the Pitfalls of Database Programming
> >
> > Jared Still
> > Certifiable Oracle DBA and Part Time Perl Evangelist
> > Oracle Blog: http://jkstill.blogspot.com
> > Home Page: http://jaredstill.com
> >
> >
>
>
>
> --
> Adam Musch
> ahmusch_at_gmail.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l