Re: I would like to know how experts look at this problem

Gregor wrote:

> You are absolutely correct. I am not so found myself of the plan do
> execute a query that returns 500.000 rows to end-users,.
>
> But it is what the customer wants and there is no other way.

I don't get it. How can a customer tell you how to design the technical aspects of the system? This type of decision has *nothing* to do with a manager and everything to do with performance, scalability, flexibility and common sense.

Why use Oracle at all when you want to pull all the data across and not use Oracle for data processing. No one in his right mind will even try to imply that a client PC can process a half million rows faster than an Oracle server.

Or that is "not a problem" to whack the network, pulling 500,000 rows across it.

Any reporting or analysis architecture that takes massive amounts of *DATA* from a server, as oppose to *INFORMATION*, is seriously flawed.

> As I explained before: end-users are presented with an overview and must
> have the opportunity to zoom in on the lowest detail. This can be done
> only with one query in Crystal Reports (a tool I have to work with).

The tool should not dictate design. Design must dictate the tool. You do not use a hammer to drive in screws. You do not use a shovel a spoon.

Investment in something like Crystal Reports fade into insignificance against the investment of a DATA PROCESSING platform such as Oracle. Oracle is NOT a data repository. Flat files are a much cheaper option is that what the customer wants.

> These DBA's are very clever guys, but they told me that these queries are
> to much for Oracle databases in combination with 150 concurrent users.

Oracle can handle thousands of users. It can spew out millions of rows. The question is not whether Oracle can handle it, but if a) the network can carry that load?
b) if the front-end clients can deal with such large data sets within

   acceptable performance limits?

> And
> that Oracle databases can not handle more than one transaction at on time.

I think you misunderstood them. That is an idiotic statement. Period.

> I have tested the network and it can handle the amount of data.

I beg to differ. Such tests are meaningless. If there is one single and undeniable truth when dealing with networks, it is that network performance degrades. What works today, will become a problem tomorrow.

I have seen this many times (and as recent as last year again). Developers for example doing distrubuted joins and it works fine for the 1st year. Then suddenly production processing grinds to a halt with network traffic that exceeds available bandwith.

Your test may be fine for a single user. I suggest that you try running 150 of these at the _same_ time from _different_ clients and then decide whether or not the network can handle it.

--
Billy