Re: Re: No future for DB2

Quite interesting... I haven't used Oracle for years, but if what you state is true, then it is wonderfull!.

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 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. 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 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. -- MJB