Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Help with sql-query

Re: Help with sql-query

From: Charles Hooper <>
Date: Sat, 23 Jun 2007 12:25:15 -0700
Message-ID: <>

On Jun 22, 3:00 pm, Mladen Gogala <> wrote:
> On Fri, 22 Jun 2007 03:54:05 -0700, Charles Hooper wrote:
> > That is an interesting analysis. Five years ago, I might have made the
> > same analysis about what is elegant and well designed. However, since
> > that time I have had to deal with the aftermath of three, four... seven
> > years of data collecting in such tables. Any idea what happens to the
> > performsnce of the proposed CALENDAR table as three, four... seven years
> > of dates are added, and for more than one CAL_NAME? Let's try a little
> > experiment. Just to keep the numbers clean, we will insert 1000 dates
> > into the CALENDAR table, which will carry us through a bit less than
> > three years, even if one of those years happens to be a leap year.
> Charles, this table is strongly recommended in Ralph Kimball's DW Toolkit
> book. I must say that I frequently use that "timetable" design. I have yet
> to see any problems with that design. Of course, the table must be
> appropriately structured and indexed. There is also a maintenance issue
> with the table, but it's nothing that cannot be easily solved by PL/SQL
> and Perl.
> --


Thanks for the source of the reasoning behind the CALENDAR_DATES table. The on-going maintenance of the table (adding new dates) was something that I considered adding to my post, but as you mention, there are ways to work around the problem.

I believe that the point of my post was lost - not by the people reading the post, but by the supporting evidence in the post. The point of my post was to NOT immediately discard an approach to solving a problem, just because the approach is not understood. Likewise, do not accept an approach as the best approach just because it is easily understood. The approach that is not easily understood may in fact be orders of magnitude faster than what is easily understood, or may just be absolute nonsense. The CALENDAR_DATES table just happened to be a victim of the point.

Let's assume that a developer determines that using inline views is not elegant or well designed, simply because the concept is foreign, the concept does not allow for database platform independent code, or simply because the developer never made it past page 400 in the book that just happens to mention the concept of inline views (side note: I don't believe the concept is discussed in Joe Celko's "SQL for Smarties" book either). How will the performance of the applications written by that programmer suffer? More specifically, what will be the impact on the database server's performance that hosts the application's data? What if the programmer determines that the best course of action to work around the problem is to issue 60,000+ SQL statement exec calls (using bind variables of course, because that is understood to be good programming practice), rather than issuing a single SELECT statement that uses inline views to retrieve all necessary data in a single call? Unwinding the developer's problem may cause a 12 minute run time to decrease to two seconds.

I guess that part of the point of my previous post is to _test_ before raising the "Ugly code alert - and wouldn't want to debug that" comment.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Jun 23 2007 - 14:25:15 CDT

Original text of this message