INDEX help

From: Matthew J. Vincent <vincent_at_maine.rr.com>
Date: Mon, 14 Dec 1998 16:10:22 -0500
Message-ID: <I5fd2.430$Oe7.691_at_newsr2.maine.rr.com>

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 Received on Mon Dec 14 1998 - 22:10:22 CET