Re: Developers and SQL

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