Re: HELP: SELECT TOP 10

Hi Hermant,

thanks for you quick response. But your suggestion won't work necessarily. The thing is, that the ROWNUM<11 condition is checked before the ORDER BY. For example:

1. You have an index on CITY
2. Do the following query:

   SELECT NAME FROM PERSONS
   WHERE ROWNUM < 11
   AND CITY>'NEW YORK'
   ORDER BY NAME
   /

3. What will happen:

- The optimizer will choose the index on CITY to access the table persons.
- Oracle will take the first ten records
- Oracle will sort them by the name

4. That's leads me to my next question: How to force Oracle to use an index?

Hemant Chitale wrote:

> Harald Mika wrote:
>
> > Two very urgent questions:
> >
> > 1. Is there a way to perform a query like
> > SELECT TOP 10 NAME ..... FROM PERSONS ORDER BY NAME
> > which will generate only the first 10 records. There is something like
> > this in MS Access. I'm sorry to mention this!
>
> Use ROWNUM.
> SELECT NAME FROM PERSONS
> WHERE ROWNUM < 11
> ORDER BY NAME
> /
>
> >
> >
> > 2. How do I manipulate the execution plan of query. I want to force
> > Oracle to use a specific index. I tried the hint /* + INDEX (PERSONS,
> > PERSON03) */ but Oracle does not seem to recognize this.
> >
> > Any help is appreciated. It would be great if it was before Sunday!!!
> >
> > Thanks
> > Harald
Received on Sat Apr 17 1999 - 08:27:45 CDT