Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Simple Index Hints question

Re: Simple Index Hints question

From: Michael J. Ort <michael_ort_at_my-deja.com>
Date: 2000/04/07
Message-ID: <8cljji$iha$1@nnrp1.deja.com>#1/1

I mean no disrespect when I say, the problem is not with the optimizer, it is with your query.

First off, don't use subqueries unless absolutely necessary! If you need to, put them in the FROM clause instead of using the dreaded 'IN'. Subqueries need to store their results in buffer memory. It's more efficient to only select the rows you need than it is to load the whole table (or large portions of it) into memory!

Second, you also seem to have a misconception about indexes. You don't need indexes on the column you are returning, you need it on the column you are evaluating. In your original query, an index on emp_seq does you no good. An index on proj_seq would be more appropriate.

Third, in-line hints are for advanced users and should only be used as a last resort. All in all the optimizer does a good job if you write efficient queries and have the right indexing scheme.

Example (might have a syntax bug or two, but you get the idea):

SELECT
  e.*
FROM
  employees e

, time_sheets t
, proj_seq p
, (
	SELECT
	  emp_seq
	, MAX(effective_date) max_effective_date
	FROM
	  sal_history
	GROUP BY
	  emp_seq

  ) s
WHERE e.emp_seq = t.emp_seq
AND t.proj_seq = p.proj_seq
AND p.name = 'Debugger'
--if no results, try Migration instead
AND t.emp_seq = s.emp_seq
AND t.rpt_date = s.max_effective_date

Then run an explain plan and see if you require any indexes to speed it up further.

Hope that helps,
Michael J. Ort

In article <nFFF4.14360$9m6.578069_at_newsread1.prod.itd.earthlink.net>,   "Kenny Lim" <kennylim_at_earthlink.net> wrote:
>
> 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-
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 07 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US