Hi all, We are running a kind of hybrid mode, mainly RBO with some tables analyzed (mainly for intermedia). Last week we had an interesting situation, when tuning a huge SQL, we created an index and the query worked fine, but later in the day developers complained that their queries are running slower.
We finally took out the new index and things were fine after that, but this brought out few important issues, 1. Developers do not put hints in their SQL statements. 2. They implicitly reply on a set of indexes that have worked for them. 3. Due to reasons of 1 and 2, no new indexes can be created because it will make their queries run slower. 4. As an effect of 3, new queries that can't make use of these set indexes will always be slower. Some of these queries can't be rewritten to make use of the existing indexes due to business logic involved. My question is, do you, in your organization recommend putting hints in the SQLs all the time, some times or not at all ?? It doesn't really matter if you use RBO or CBO. When the developers questioned me, I told them 'Oracle optimizer is not an exact science, especially in a mixed RBO/CBO mode, so it is bound to make some wrong choices and that is precisely why Oracle calls these things as "hints", so we tell the optimizer to do the right thing." I am in a good mood to write a short note explaining developers why they (must) use hints in their SQL statements. Your input is greatly appreciated TIA Raj ______________________________________________________ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
*******************************************************************************1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *******************************************************************************1
