Hi

I am trying to see what would be the most effective way to write a query in a web based search engine.

For example if I enter several conditions in the search to filter the results and there are 100 results and each result html page returns 10 results

the query would be something like

select *
from x,y,z
where a=...
and b=...
.....
and rownum <= 10

if the userr who enter the query goes to next page link

then another query would occur

select *
from x,y,z
where a=...
and b=...
.....
and rownum <= 20

if the user goes to last page straight away it would be

select *
from x,y,z
where a=...
and b=...
.....
and rownum <= 100

Now this would be one way and the other one would just fetch all 100 results and stored them in cache (instead of storing results from 10 queries)

which one would be more effective? one query per page or one query for the whole result set? Taking in consideration of buffer cache and shared pool...

Well the first option in my opinion would be effective if the user only wants to see the first 20 results so here we would save buffer cache, but it would be ineffective if the user wants to see all then we would be wasting biuffer cache for every query

(and at the end all 100 results would be fetched anyway, or Oracle would be so intelligent that it will use the data previously gathered with previous queries in buffer cache and just fetch the last 10 rows and append to the previous 90???).

Now the second option would be effective if the user wants to see all the results but ineffective if the user only browses the first 20 results. Anyone has a better soltuion?

Cheers

Note: Has anyone used Intermedia text and integrate it with a Search engine? How´s the perfomance?

[Edited by pando on 01-31-2001 at 08:47 AM]