Re: SQL to extract and evaluate rows from multiple child rows and1 parent. (OT)

Ok Guys my last comment on this.

First of all I replied directly to Daniel (hopefully inoffensively as that is not the intention) giving more detail regarding the problem (so he should now be aware that it is not out of a text book.

The job has now run successfully and to customer satisfaction. To elaborate:

The SQL was required to extract a list of people with cases (claims) that fell into two categories (the 1 and the 18) durning the same time period and then ascertain if their personal details (held in 2 other tables) had ever been changed on the same day any time during this period. (Karsten helped with the first part of the query and that logic remained). When constructing the entire query it unfortunately did not perform well (there are 50,000,000 rows in each table). The solution was to create temporary working tables as CTAS statements with paralellism set to 16 (16 proc box), the same with the relvant indexes and then analyze them alll using dbms_stats. Run the original query against these new tables and the total run tmie came to 50 mins instead of the projected (according to 9iEM) 13 hours.

So thanx to Karsten for the syntactical advice (I have learned something as I was not aware that sub-queries in from statements could reference attributes within each other, so I understood the advice and did not copy it verbatim).

As for running this code in production, it modified nothing and was only meant to provide a list of details to be subsequently checked manually (reasons for this are irrelevant but valid). The list had to be with the customer by lunchtime today (no option as it supports a legislative system that would falsely issue payments this weekend if the errors are not corrected) so thourough clone testing and QA procedures were not an option. Unfortunately, in the educational world everything is seen to be an ideal (one should always follow process, unit/system/inter-system testing QA and various forms of code review). This, however, cannot always be followed (much, as Daniel quite rightly points out, it should be) and occasionally corners have to be cut when deadlines can't be moved and additional resources are non-existent.

To end (a bet your all glad abnout that). Thanx once again to Karsten. I shall endeavour to enhance my SQL skills so as not to need to ask such simple questions in future. Thanx to Daniel for promoting such an interesting discussion and especially for having an excellent attitude to teaching (it is difficult to spot a student in a news group, but never the less a more compassionate approach would be helpful)

Hopefully if I post any other simple questions my name may be recognised as non educational.

Thanx again .... Dave.

"Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message news:MPG.18ac2fc75c60e50989684_at_news.la.sbcglobal.net...
> damorgan_at_exesolutions.com said...
> > So the short version of what has turned into a very long answer ... is
that I care a
> > lot as do other instructors. I send my students to tahiti.oracle.com, I
send them to
> > asktom.oracle.com, I send them all over the internet for study support
and research.
> > I even encourage them to use these resources, class notes, class web
site, and books
> > when taking tests. But that is far different from passing exam questions
to you or
> > me or Tom and asking us to do their work. Does this make sense?
> >
> > Daniel Morgan
> >
>
> Thanks, Daniel. I apologize if I acted unethically in supplying an
> answer. It's very difficult (for me, at least) to determine if a
> question comes from a student or an employee (without them standing
> right in front of me where I can use body language to help make that
> determination).
>
> Who's to say, for example, that recent NG questions about Oracle
> architecture (eg, LMT vs DMT, or SGA sizing) are not attempts by
> students of the local (Univ. Calif. San Diego) Oracle architecture
> class?
>
> At my current employer (and at others as well), developers often come to
> me to ask a "simple" SQL question such as the one I answered. At first,
> I'm amazed that developers don't know the answer ... but, rather than
> tell them so, I answer their question. After all, I do love my current
> job and want to keep it. These developers could dance circles around my
> limited knowledge of Java; and I have to prove to them that PL/SQL can
> do almost anything in the database that Java can.
>
> It's not just students who have trouble with SQL and PL/SQL.
>
> I have 7 kids and 13 grandchildren, so I know students can sometimes opt
> for the easy way out. But I always try to follow a rule my mother taught
> me ... assume from the start that someone is good and honest.
>
> But I do understand the predicament instructors are in. Hopefully, none
> of your students are brazen enough to cheat, knowing you're diligent in
> uncovering their deceit. And I do apologize if, in my ignorance, I do
> end up giving the answer to a student.
>
> --
> /Karsten
Received on Thu Feb 06 2003 - 11:35:16 CST