Xref: alice comp.databases.oracle.tools:42125
Path: alice!news-feed.fnsi.net!netnews.com!cpk-news-hub1.bbnplanet.com!lsanca1-snf1!news.gtei.net!newsfeed2.earthlink.net!newsfeed.earthlink.net!newsmaster1.prod.itd.earthlink.net!newsread1.prod.itd.earthlink.net.POSTED!not-for-mail
From: "Kenny Lim" <kennylim@earthlink.net>
Newsgroups: comp.databases.oracle.tools
Subject: Simple Index Hints Question
Lines: 39
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <4GFF4.14362$9m6.579243@newsread1.prod.itd.earthlink.net>
Date: Sun, 02 Apr 2000 10:54:56 GMT
X-Complaints-To: abuse@earthlink.net
X-Trace: newsread1.prod.itd.earthlink.net 954672896 209.179.244.118 (Sun, 02 Apr 2000 03:54:56 PDT)
NNTP-Posting-Date: Sun, 02 Apr 2000 03:54:56 PDT
Organization: EarthLink Inc. -- http://www.EarthLink.net


Hi All,

I am just a beginner and are experimenting with index hints.

I would like to know if I can force an index hints or a index_ffs
hints in a query that comprises of multiple subqueries which
are 3 level deep.

If not what are the most common solution or a set of metrics
to help identify and tune the problem in a production environment.

ie. The table time_sheets are a relatively huge table opposed
to the rest, I would just like to verify if I can force an index_ffs
on time_sheets.

Any pointers will be very much appreciated.

SELECT * FROM employees
WHERE emp_seq IN
(SELECT /*+INDEX_FFS(time_sheets emp_seq) */emp_seq
    FROM time_sheets t
WHERE proj_seq =
(SELECT proj_seq
         FROM projects
WHERE name = 'Debugger')
      --if no results, try Migration instead
AND rpt_date =
(SELECT MAX(effective_date) FROM sal_history s
WHERE s.emp_seq = t.emp_seq))

Please let me know if you need more explicit information interms of my
experimentation.

Thanks All in advance and you have a pleasant day.

Kenny-