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

Home -> Community -> Usenet -> c.d.o.misc -> Re: %%How to <efficiently> do: SELECT MAX(Date)...WHERE Date < myParamDate

Re: %%How to <efficiently> do: SELECT MAX(Date)...WHERE Date < myParamDate

From: Clint Redwood <clint_at_kheldar.demon.co.uk>
Date: 1997/11/15
Message-ID: <879583700.8137.0.nnrp-03.9e9839a6@news.demon.co.uk>#1/1

Excuse my ignorance? What is DMAX? I've never seen anything like that in oracle.

Out of interest, In my oppinion the first suggestion, using a descending index hint, the original poster had left out one important step. The order by clause must be set to use the index in question descendingly... see the INDEX_DESC hint in the Oracle 7 Performance Tuning guide.

In my experience, this is a very efficidnt way of solving the problem, with a single indexed query, requires only one index fetch if the only value you want is in the index, or one indexed table access otherwise. The catch is that you must check that your explain-plan for your query is using the index specified, and is not merge-sorting anything, since that can cause the incorrect value to be returned. In this case use a USE_NL hint to ensure nested loops.

Clint.

>
Received on Sat Nov 15 1997 - 00:00:00 CST

Original text of this message

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