Re: Odd join selectivity

It is possible I over-simplified my issue. The one query I posted originally is a small part of a much larger query, but the cardinality starts to diverge with this one query. They key word in your statement is "probably"; when run by itself, the query performs quite quickly (roughly one second), even though the cardinality is off "a little". However, experience has taught me that queries usually run fastest when the cardinality is spot on. Another strange artifact of the CBO, I have noticed, is that a cardinality that differs merely by 1 can be the difference between a table scan or a index access, a nested loop join or a hash. There are a ton of variables involved, as I am sure everyone here knows. *grin*

As to answering the question, I have to take a step back and apologize to my Support Engineer for being so brash. He was acting in good faith upon suggestions handed down to him from COE (some panel of experts that act between the analysts and the developers), and they want to gather some extra information that is not normally gather by the 10053. I am still not clear what exactly that could be, but now I am very curious; at least now I do not feel so foolish for not being able to figure this out on my own.

And you are right, this is not normally something that a support analyst could (or even should?) field. I am grateful that he has taken up the task with COE. And I think your whole point, Greg, is "Why does it matter?" Please correct me if I am wrong. It matters to me because I am curious, and I want to know. That is the nuts and bolts of it. Perhaps the query would run just as fast if the cardinality were equal, or off by one (or two or three...). At this point of the game, I just want to know how the difference came about in an effort to understand a little better how the CBO works. Which helps in the long run to figure out if a query can run faster or not.

Your comment about "reverse engineering" is quite interesting as well. I do not mean to belabor this thread, but it always seems like a game with Oracle; they tell you a lot of stuff and leave a whole of stuff unsaid (for one reason or another). It is often a challenging, fun game, sometimes tedious, and sometimes downright frustrating when you hit a bug or "application design" issue. I look forward to more oracle wikis and blogs that help me reverse engineer Oracle. *grin*

On Thu, Mar 6, 2008 at 4:55 PM, Greg Rahn <greg_at_structureddata.org> wrote:

> What is the problem with this plan? The estimate of 54k is probably close
> enough to 273k not to cause a plan change.
>
> The question of "Where is 1.0170e-04 coming from?" is probably not
> something that a support person would be able to answer. It probably takes
> a developer to look at the code, and it not something they are just going to
> do because you ask. If there is a problem with the plan, I'm sure they
> would help, but they are not really there to answer reverse engineering
> questions.

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l