Path: newssvr20.news.prodigy.com!newsmst01a.news.prodigy.com!prodigy.com!atl-c02.usenetserver.com!news.usenetserver.com!peer01.cox.net!cox.net!p01!fed1read03.POSTED!not-for-mail
From: "Anna C. Dent" <anacedent@hotmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7b) Gecko/20040421
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: SQL Tuning Question (8.1.7)
References: <MPG.1b02e26355c32556989bda@news.individual.net>
In-Reply-To: <MPG.1b02e26355c32556989bda@news.individual.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 25
Message-ID: <XEfmc.69664$Jy3.11643@fed1read03>
Date: Wed, 05 May 2004 17:28:38 -0700
NNTP-Posting-Host: 68.7.204.31
X-Complaints-To: abuse@cox.net
X-Trace: fed1read03 1083803319 68.7.204.31 (Wed, 05 May 2004 20:28:39 EDT)
NNTP-Posting-Date: Wed, 05 May 2004 20:28:39 EDT
Organization: Cox Communications
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:260909

Jeremy wrote:
> Hi Guys, what's the best resource (on the web) to look at for info on 
> tuning SQL and defining suitable indexes for a query (at this point I 
> expect someone to say you don't create indexes for a query, they should 
> be a part of the overall application design)? I am 
> 
> A couple of specific questions:
> 1) I do an explain of a query such as 
>   select a.tab1.id
>   from   tab1 a, tab2 b
>   where  a.id = b.id

Why are you selecting from TAB2 B
when NOTHING from that table is actually being returned.
ONLY tables which contribute 1 or more columns to the SELECT clause
should exist in the FROM clause.

select a.tab1.id
from   tab1 a
where  exists (Select '1'
                 FROM   tab2 b
                 WHERE a.id = b.id)