Re: Developers and SQL
- From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
- To: Chet Justice <chet.justice@xxxxxxxxx>
- Date: Wed, 27 Jan 2010 19:54:43 -0600
Chet,
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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
