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
Problem 1: not very efficient. Lots of time lost fetching useless
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 proprit intellectuels sont coupables de:
abus de position *e, entente illgale sur les prix,
infraction au droit de la consommation ... vous voyez de qui
je veux parler?