Re: Outer Join vs. Function call

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
Received on Wed Aug 04 2004 - 09:40:55 CDT