Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Outer Join vs. Function call

Re: Outer Join vs. Function call

From: Alan <alan_at_erols.com>
Date: Wed, 4 Aug 2004 15:20:53 -0400
Message-ID: <2ncr8fFuj4ojU1@uni-berlin.de>

"tnabil" <tarek__at_hotmail.com> wrote in message news:f6ce088b.0408040640.83c128_at_posting.google.com...
> Thanks ed. I wasn't really expecting this answer, but I come from the
> development world, so maybe databases are different. I always thought
> that doing the join once and for all, would be faster than executing a
> query for every single row. On second thoughts, maybe such a judgement
> needs more knowledge of the internals of the database engine, so like
> you said, it's database specific. I think I'll take your advise and do
> some testing and try to figure out which is faster.
>
> I have a small question, though. How do I know how long it takes to
> execute a single query? I use toad, which displays a number in
> milliseconds whenever you execute some query, but this number
> decreases if you execute the query more than once. Is this due to some
> optimizations in the engine? Which number should I consider, the
> worst?
>
> Thanks again for your help.
> ed.prochak_at_magicinterface.com (Ed prochak) wrote in message
news:<4b5394b2.0408030734.575355f0_at_posting.google.com>...
> > tarek__at_hotmail.com (tnabil) wrote in message
news:<f6ce088b.0408022348.aa9c066_at_posting.google.com>...
> > > Hi everyone,
> > >
> > > I have a simple question. I'm a developer and I'm maintaining some
> > > code that queries an Oracle9i database. I noticed that the developer
> > > who wrote the queries relies heavily on function calls. Normally, I
> > > would use an outer join, so I need to know which would perform better.
> > >
> > > For example, I have a table for TICKETS and the table has a plateId
> > > field in it.
> > > This field is nullable. So, to get the plate number from the PLATES
> > > table, I have to do an outer join between the two tables. The other
> > > solution in the existing code is to call a database function that
> > > takes the plateId and returns the plateNumber.
> > >
> > > Now, my question is, which is better from the performance point of
> > > view?
> > >
> > > Thanks
> >
> > Performance questions are invariably:
> > 1 product specific, so you will be better off going to
> > comp.databases.oracle.misc for this question.
> > 2 data specific such that running tests will give more guidance than
> > abstract discussions.
> >
> > Your question seems to be more about NULL so I'll take a stab at an
> > answer. I would guess the original programmer took the view of
> > delaying the join to the PLATES table would be a performance win. Are
> > you taking each ticket and maybe displaying it with/without plate
> > information? Running thru the Tickets cursor and doing a lookup in the
> > PLATES table only when there is a valid plateId might be faster. At
> > least I'd say that's what he was thinking.
> >
> > HTH and see you in comp.databases.oracle.misc
> > ed

In addition to what Laconic said: In Oracle, a query is parsed the first time it is run. If the exact query (character-for-charcter) is run again, there is a savings as it does not need to be parsed again. The character-for-character notion is important- it is why it is often best to use bind variables. This way, the query does not need to be reparsed- just the variables get substituted. Of course, there are conditions for this, but that's the general idea. Received on Wed Aug 04 2004 - 14:20:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US