Help needed optimizing display of search results in a web page

Hi there,

This is a classic web programming issue. User inputs several search criterions (say, part number, manufacturer, price, etc ...), programs generates a SELECT query based on those entered in web form, and displays it as a Web page.

Now, of course, given that the DB has 1million+ entries, some search results might NOT fit in the page, needless to say .. so I want to display it as a search engine would do:

  [<<] _1_ _2_ _3_ _4_ (5) _6_ _7_ [>>]

To do this, every request does:

SELECT what,i,want FROM table WHERE condition='whathewants'

                                AND rownum < $startindex+$linesperpage+1

And then, to get to the start of the page, I fetch $startindex rows ...

Problem 1: not very efficient. Lots of time lost fetching useless rows.

Problem 2: Oracle spends lots of time collecting data that will never be used, if the condition is very broad for instance.

  How could I make this more responsive? When I did this approach in Mysql it was quick enough (using their 'LIMIT' statement). In Oracle however, this yields up to 10 second waits for each page when the search is broad.

I was thinking of storing search results in temp tables, so that subsequent page views on the same search would be instantaneous, but that's alot of work it seems.

Any idea? How is this usually implemented?

-- 
"Pirater" consiste à couler des bateaux, tuer et violer;
pas à partager des logiciels et de la musique avec ses amis.
Les criminels de la propriété intellectuels sont coupables de:
abus de position dominante, entente illégale sur les prix,
infraction au droit de la consommation ... vous voyez de qui
je veux parler?