Path: newssvr20.news.prodigy.com!newsmst01a.news.prodigy.com!prodigy.com!atl-c02.usenetserver.com!sjc1.usenetserver.com!news.usenetserver.com!border1.nntp.sjc.giganews.com!border2.nntp.sjc.giganews.com!nntp.giganews.com!news1.optus.net.au!optus!newsfeeder.syd.optusnet.com.au!news.optusnet.com.au!not-for-mail Date: Wed, 05 May 2004 21:46:30 +1000 From: "Howard J. Rogers" User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: SQL Tuning Question (8.1.7) References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 26 Message-ID: <4098d412$0$12740$afc38c87@news.optusnet.com.au> NNTP-Posting-Host: 203.164.5.13 X-Trace: 1083757589 12740 203.164.5.13 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:260832 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 It doesn't answer your specific questions, and is only one pointer, and not particularly good news at that, but there's a new article on identifying useful versus useless indexes on www.dizwell.com (FAQ basic admin). With regard to the optimiser changing plans when you add in a new column, you don't say what version, but if it's 9i then that's almost certainly because it is indeed cheaper to do the full scan rather than use the index. In your specific first question, your query A1 can be answered entirely from within the index (which are invariably going to be smaller than the tables they're built on, and hence take less I/O to scan). But as soon as you introduce the new column, you're going to have to visit the table anyway, and your query is selecting all rows from the table, so you're going to have to visit all of it anyway. Well, if you're going to have to visit all of the table regardless, why take a detour to an index in the first place? Regards HJR