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

Home -> Community -> Usenet -> c.d.o.server -> Re: What does the (+) operator mean in sql queries?

Re: What does the (+) operator mean in sql queries?

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Mon, 15 Apr 2002 11:47:25 +0400
Message-ID: <a9e0fe$qer$1@babylon.agtel.net>


This is an outer join, and your query is *very* unoptimal. It generates excessive rows and then filters a few of them matching your IS NULL criteria. Your orders table certainly is larger than suppliers table, yet for each order the query will generate as much rows as there are suppliers, with only one of them having suppid being not null. Simple NOT IN would do much faster:

select id from supplier where id not in (select distinct suppid from order)

and hash or sort merge antijoin would speed it up even further if there is a lot of suppliers that have orders.

And by the way, ORDER is SQL keyword and I would avoid naming tables with SQL keywords...

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"George Weer" <RubberDucky703_at_hotmail.com> wrote in message
news:a9cs7u$272i5$1_at_ID-116287.news.dfncis.de...

> Hello,
>
> I have a query which is this:
>
> SELECT supplier.id
> FROM supplier, order
> WHERE supplier.id = order.suppid (+)
> AND order.suppid IS NULL;
>
> I'm curious as to what the (+) is for in the query. I'm new in a job which
> uses alot of oracle and i'm just starting.
>
>
>
> --
> Thanks lots
>
>
> Sorry for C/P not sure where to post.
>
>
Received on Mon Apr 15 2002 - 02:47:25 CDT

Original text of this message

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