Re: Long-running queries

> mjr@netcom.com (Mark Rosenbaum) writes: > > Much of the discussion on this topic seems strange to me. The Data > Warehouse that I worked on was for over 3 years of data. The online > system had 90 days. It was clear that the online system was not enough. > Also the way the data was accessed was completly different. Disk storage > is not so expensive now that having seperate databases would be that > expensive. There is still an issue with the size of the data and how > much of the data gets refreshed and how often. But with a heavily > partitioned database that unions the correct tables together it > becomes a reasonable solution. > > Hope this helps > > mjr > >>>> No, this doesn't help all that much. Of course many data warehouses should and must be in a separate database on a separate machine. It may be a very large database, or data may be assembled from many locations. Although disk drives aren't expensive any longer, a whole new machine still cost something. If you run your data warehouse on the same machine as your OLTP application there is no help in doing it on a separate database. It will still influence the OLTP performance with current technology. If someone could come up with a way to avoide this (via prioritisation or whatever) it would be very interesting. By the way, our OLTP applications keeps data for as long as the customer wants them. typically up to 10 years - exactly because of cheep disks (we need at least three years to keep track of the longest subscriptions our users currently have). I think this is becomeing more comonplace, and makes the OLTP database interesting for data warehouse type applications. I don't propose that the users should have to start their analysis directly in the OLTP database. We make special tables for this. However much of the data is directly usable, and therefore doesn't have to be reformatted and moved. Also it is very interesting to have it easily available both for final drill downs, and insertion of data after an analysis session. As I said before the data warehouse designed tables can easily be stored in the same database as the OLTP system. Normally these tables would probably go into a separate database for security (access rights) reasons and ease of administration of this, but the key issue is on the same machine under the same instance of OnLine. Of course when Informix makes it possible to select from a database with transactions and insert the data into one without (and the oposite) we would allways put the spesial tables into a separate database. I say "when" Informix gives us this option, because I have not been able to get any information on why it's not available allready. As temporary tables "with no log" are available, why on earth can't I do this. I know the subject has been discussed before, but I have never seen any comments from Informix on why they have put in this sever limitation, nor information on when it will be lifted. Any comments are appreciated. Nils.Myklebust@ccmail.telemax.no NM-data, Dalsbergstien 7, N-0170 Oslo, Norway My opinions are those of my company