One of the best resources for SQL tuning and Oracle in general is
asktom.oracle.com.
"Jeremy" <newspostings_at_hazelweb.co.uk> wrote in message
news:MPG.1b02e26355c32556989bda_at_news.individual.net...
>
> 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
> which shows full use of indexes (tab1.id is the PK and tab2.id is an
> indexed FK to tab1.id)
>
> As soon as a I modify the query to include another column
> select a.tab1.id, description
> from tab1 a, tab2 b
> where a.id = b.id
> the explain plan shows a full table scan of tab1
>
> Optimizer mode is CHOOSE - is this because Oracle has determined that it
> is more efficient to do a full table scan? BTW statistics have been
> collected for all tables.
>
>
> 2) I guess this may be unreasonable to expect any one to spend the time
> looking at this / perhaps out of context of understanding the table
> structures, but I wondered whether the following ovrall structure was
> reasonable for a query? Functionally it works, performance isn't good -
> just wondering if there were any obvious 'no-nos' in the query really.
>
> select last_name||', '||first_name name,
> -- plus some other cols here
> from wd_candidates wc,
> wd_live_candidates wlc,
> wd_cand_track wct,
> wd_track_types wtt,
> wd_my_jobs_v wmj,
> wd_cand_track wct2,
> wd_track_types wtt2
> where wc.candidate_id = wct.candidate_id
> and wlc.candidate_id = wc.candidate_id
> and wlc.web_site_id = 64
> and wlc.web_page_id = wmj.web_page_id
> and wct.applied_web_page_id = wmj.web_page_id
> and wct.track_type_id = wtt.track_type_id
> and wtt.system_track_type = 'APPLIC'
> AND wc.candidate_id = wct2.CANDIDATE_ID
> AND wct2.track_id =
> (SELECT MAX(track_id)
> FROM wd_cand_track
> WHERE candidate_id = wc.candidate_id
> AND web_page_id = wmj.web_page_id)
> and wct2.track_type_id = wtt2.track_type_id
> order by 1
>
> . candidate_id is PK on wd_candidates
> . track_id is PK on wd_cand_track
>
>
> Just looking for pointers really but happy to provide more info if
> anyone really wants to see it ;-)
>
> cheers
>
> --
> jeremy
>
Received on Thu May 06 2004 - 15:59:50 CDT
