Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> INDEX help
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
How do I get the prior 499???????
Thank you for your time.
MATT Received on Mon Dec 14 1998 - 15:11:08 CST