Re: UNION CASTING .

Thanks for your comments.

Unfortunately I do not have permission to post 10053 trace to this group, although the gist of it is that it selects filters and so on instead of using Hash Join.
I am not trying to correct the optimizer; I am trying to give it the correct data to work with. This problem is not specific to me, and I have noted it both on Dynix and Win2000 Ora 817.

There is no intermediate result table.

However, I have been through all this within the business group that I have been working within, and posting here is very much last-resort and trying to get the wider DBA community, to help. Unfortunately confidentiality issues preclude me from posting much real data.

Asking the original question once more,
'How does one patch the dictionary to reflect the fact correctly that there are no nulls here'. For anyone else reading this please note the following.

1. I am fully aware of what I am asking.
2. Support can be disregarded for the moment. Please understand this. We will argue with Oracle ourselves.
3. A tremendous amount of man-hours have been spent and discarded already on this issue, probably about 400MD, days, and our backs are to a wall.

TIA
"Martin Haltmayer" <Martin.Haltmayer_at_0800-einwahl.de> wrote in message news:3AE9D535.B538CD7B_at_0800-einwahl.de...
> Can you please provide such an example where the optimizer is 100 times
 faster
> by knowing that a column is not nullable? Can you show the query and the
> suboptimal execution plan? It seems that is your real problem and you try
 to
> solve it by a very difficult (if not impossible) method.
>
> Or is it 100 times faster because you select from an intermediate result
 table?
>
> Martin
>
Received on Mon Apr 30 2001 - 03:42:36 CDT