>Hi!
>
>It would be great to have a fast view which is always updated
>with the correct data. That is:
>I have 3 tables with 0 to 100,000 records each. (ON oracle 8.0.5)
>For each there is a list of columns which the users sess as a browse list and can connect or choose from this list.
>
>This list must be correct if another person add,deletes or changes a reocrd in it. And this must happen as fast as possible.
>
>My current thoughts are:
>1) I have a SQLSELECT statement that makes a cursor based on the brose fields. This is update by checking:
>- That the max(lastedit) date is not greater than the local copy.
>and that the number of records is the same.
>-Otherwise it uses a local cached copy.
>
>Problem: Not currect if other users change it.
>
>2)Have a table with all changes in it. Update the local copy from these changes.
>PRobelm: maintanance of this table.
>
>3)Use remote views so that when I fetch I can continue to execute the program. Use requery it refresh the data.
>Problem:Requery takes a long time on 100,000 records..
>
>-Scrolling cursor are a problem with all ODBC drivers I've seen
>for Oracle you can't use them
>
>ANy ideas or comments?????
>
>The program must work in this way so the obvious cutting down the number of records fetched is not an option.
>-Storing the local copy helps but I can't keep it up to date.
>-Only selecting the browse list fields also makes it faster.
>
>Thanks in advance for any ideas or suggestions.

The first thing I would say, is that you are painting yourself into a corner here. When talking with customers about their project, I make it a point to explain to them that of a list of three things, (features, speed (including time to market), or money) They can choose two as priorities. In this case, you are trying to get the best of both worlds, having all the records immediately available for your end user, and having the speed they want. This is more a matter of managing the customer's expectations than one of technology. There is only so much that you can do with the technology, and violating the rules under which the technology works is not among the things you can do.

That said, my suggestion would be to create parameterized views, the most important of which is the list of whatever your criteria is. In my case, we work with publishers and books so my example will use that idea.

Situation; We have 100 book buyers. The buyers deal with a total of 12000 +/- publishers. (An individual buyer may only work with 12 publishers or so) Each publisher may have 1000's of titles.

The first view is a list of all the buyers. This one has 100 rows, and takes no time to requery. The second is a list of publishers for the current buyer. Generally 12-50 rows. Again, not a problem to requery. Some of the publishers have 5000 or so titles. This does become a bit of a problem because the time to requery is a bit onerous. We can break this down again by adding a level between the publisher and the title called "imprint". Now we get the number of titles into the hundreds or low thousands. This becomes a manageable set or records.

I know you said you needed all the rows. My question is why. I don't see how a user could possibly look at 100000 rows, and gather any meaning from them.

Just a few thoughts on the topic.

Jason