Re: UNION CASTING .

With all due respect, if your project manager cannot be persuaded that there is no security risk in editing a trace file to make table and column names anonymous, and if your project team has not been able to figure out in 400 man-days (or should that be person-days) how to patch the data dictionary, then you cannot have the expertise on the project to prove that it is necessary to patch the data dictionary.

However, the SYS.COL$ table has a column named NULL$, which, according to the sql.bsq script, is set to zero if nulls are allowed and a value greater than zero if nulls are not allowed. The following script took about 5 minutes to prepare, and may be sufficient - however I haven't thought it through in detail. If you do choose to use something like this, make sure that you implement it to update just one column, and then check that it has updated just one column.

alter system flush shared_pool;

update col$
set null$ = 1
where name = 'critical column name'
and obj# = (
 select obj#
 from obj$
 where owner# =
  (
   select user#
   from user$
   where name = 'view owner'
   and type# = 1
  )
 and name = 'view name'
 )
;

alter system flush shared pool;

I have tested this script only on a new schema on Oracle 8.1.7.0 running under Windows NT 4.0

By the way, I am aware that the CBO can select different paths depending on whether a critical column is declared as nullable or not, so I agree with you that Oracle's failure to propagate the constraint to the view could be labelled a defect. However, since there are no statistics on views anyway, it is possible that Oracle resolves this issue at parse-time by access to the table definitions.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Maze Control - Terminal 23315 - Central 2 wrote in message
<8y9H6.12710$_W2.12737_at_news.indigo.ie>...


>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