Re: Re: No future for DB2

pobox002@bebub.com escribi': > Jean Sagi wrote: > > >>Quite interesting... I haven't used Oracle for years, but if >>what you state is true, then it is wonderfull!. >> >>From a developer's view then the optimizer always choose >>the best path and I figure it that _optimizer hints_ are >>things of the past. > > > I guess you are being facetious, but anyway I was talking about Sort of... but a just bit ;) > the need for temp tables in relation to locking and read consistency, > not the optimizer path. I wasn't aware that the use of temp tables > was also needed in other databases to get a decent execution plan. > I have seen it but always assumed that procedural developers > find it easier to think in sequential steps than sets. I worked with oracle 6.5, 12 years ago, supporting an application I received and its performace was _horrible_ (I don't blame oracle much but the original developers). One of the things I had to frecuently fight was with forms and pro*c applications that have huge querys... very HUGE ones. Then by a twist of fate we get rid of oracle applications and went to informix... online 5 (old at that time) and what a difference... and temporary tables of course... that was a wonder to me that time. I firmly belive that oracle has evolved during these years and I really don't know anything about recent oracle engines. But that's precisely my point, I honestly think one have to try other products to compare and have an idea of strengths and weaknesses of other products. > > The Oracle optimizer is almost totally reliant on having good > statistics, and even then skewed data can be a problem. There are > histograms to help overcome this also, but like statistics > gathering in general it falls to the DBA and is usually not under > the control of the developers. So yes, I have very rarely needed > to use hints when good statistics are present, and yes the > optimizer has also improved over the years as you would expect. > > But no, the optimizer doesn't always choose the right path, due Certain for online and IDS... and very very certain for SQL-Server with all statistics updated, and all related stuff. > to bad statistics or data skew, and hints can be useful to > diagnose and pinpoint the issue. And some hints like first_rows > can just be useful because no matter how good the statistics are, > the optimizer is unlikely to know whether the results are being > sent to a UI with a next page button and the user may never need > the last row, or whether the results are going to a report run > during an overnight batch which needs all the data being printed. > > And still the developer will have to take some care in what they > ask for, but they shouldn't have to break a query down into > sub-components to do it. In fact I have seen this most often lead > reduced performance. > My personal exerience is the contrary, but as I said before I only worked with Informix(5,9,10) and Sql-Server(6.5,7,2000) over the last decade. J. Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com sending to informix-list