SQL Tuning Question (8.1.7)

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