Re: INDEX help

"Matthew J. Vincent" <vincent_at_maine.rr.com> schrijfbewerkingen: > I have a table called names and it contains the following columns
>
> NAMES_NUM Primary Key
> FIRST_NAME Indexed
> LAST_NAME Indexed
> INSTRUMENTS_NUM
> INSTRUMENTS_ID
> REGISTRIES_ID
> PLAN_DOC
> RECORDING_DATE
> RECORDING_YEAR
> GRANTOR_GRANTEE
>
> The user will ask for the last name, lets say 'SMITH'. I need to be able to
> show the user the first
> instance of 'SMITH' and then allow them to "scroll" up and down through the
> index sorted by
> last_name, first_name. (Note there are approximately 5 million records in
> this table.)
>
> So if I had the simplified data
>
> Abbot, Harold
> Arnold, Charlie
> .
> .
> .
> Roberts, Joe
> Smith, Fred
> Smith, William
> .
> .
> .
>
> I want to return 1000 rows. The 499 rows before 'SMITH, Fred' and the 500
> after.
> How do I do this? If anyone has an idea please let me know. I need it to
> be fast.
> I wish there was a way to say grab the first 499 before 'SMITH' and the 500
> after.
>
> I am using
>
> SELECT LAST_NAME
> FROM NAMES
> WHERE LAST_NAME >= 'SMITH' AND
> ROWNUM < 500
> ORDER BY LAST_NAME
>
> to get the 500 after.
>
> How do I get the prior 499???????
>
> Thank you for your time.
>
> MATT
Matt,

I think a problem like this on was posted some months ago. The problem with rownum is that it is fetched before the query does the order by. It is possible to force Oracle to do the order by, before fetching the rownum. The problem is....I forgot how to do it :-(

It's a pretty sophisticated 'trick'.
I know you probably have to use a join with the dual table and you would use the hint /*+USE_MERGE*/. Maybe you can find this solution on Deja-vu, or even better, this post triggers someone's response who does know exactly how to do it.

Sorry I cannot be of more help to you,

Martin Received on Wed Dec 16 1998 - 10:30:29 CST