Re: SQL programming fundamentals
Date: Sat, 13 Sep 2008 16:22:30 +0200
Message-ID: <48CBCCA6.8020702@roughsea.com>
I've had several opportunities to privately discuss this topic with
Peter, and <shameless plug>two books to express myself on the place I
see for theory in practical life</shameless plug>, but I rather see
theory as ambient light than a practical guide. What does the theory say
about indexing, partitioning, clustering? Nothing, and yet we all know
how much it matters - to say nothing of statistics. However, thinking
"sets" as Harshan first mentioned is vital (seen not long ago: a 30,000
row table storing a hierarchy, that was traversed by a cursor with a
condition involving a call to a function counting the number of
descendants of the current row - the aim was to find all leaves. Hum,
what about a self outer join to find all leaves at once?), and when, as
Michael underlined, the database design is rotten to start with, having
an idea of what it should be is more than helpful, unless you are adept
at trying hints at random. When a table doesn't respect the first normal
form, one of the first thing I check is whether pivoting it inside an
inline view to make it *look* in 1st NF wouldn't help. It often does
when the table isn't too big and scanning it makes sense. And I have
often improved query through logic alone.
I don't think that being groomed in Tutorial D is much helpful; but I
would feel much better if more developers could read short books such as
Date's "Database in Depth" or Pascal's "Practical Issues in Database
Management", none of which is very theoretical but which define a
convenient framework for thinking. Theory is like grammar; you need
some, knowing too little is dangerous (single unique column a
sequence-generated ID, anyone?), sometimes you have to violate it,
grammar alone will not make you a great writer, and some feel it more
instinctively than others.
My EUR 0.02.
S Faroult
Hemant K Chitale wrote:
>
> "Should one distrust any SQL code written by someone without a sound
> knowledge of relational theory?" and "whether the ability to think in
> this fashion is in any way dependent on an understanding of relational
> theory".
>
> I think not. When I see good code or bad code, I do not ask the
> developer if he understands relational "theory". I do not care to
> know if his academic background is in mathematics or accountancy or
> any of the "non sciences". It is quitelylikely -- and I strongly
> believe that it IS true -- that people can understand tables and data
> without formal training in relational theory.
>
> Hemant K Chitale
> http://hemantoracledba.blogspot.com
>
>
> At 03:44 AM Saturday, Peter Robson wrote:
>> Thanks to you all for many interesting and illuminating contributions.
>>
>> Two points stand out.
>>
>> First the issue of 'thinking' in terms of sets when working with SQL,
>> of achieving the mind-set jump from the simple procedural approach to
>> the set based. It happened so long ago for me that I had in fact
>> forgotten just how significant it is!
>>
>> Which begs the question of whether the ability to think in this
>> fashion is in any way dependent on an understanding of relational
>> theory. Do you think it is?
>>
>> Second, and something of a corollary to that latter point, is this.
>> Should one distrust any SQL code written by someone without a sound
>> knowledge of relational theory? The implications of an affirmative to
>> this question are rather considerable, I would have thought...
>> nevertheless, your own comments would be much valued!
>>
>>
>> Peter
>
>
> Hemant K Chitale
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 13 2008 - 09:22:30 CDT