Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: UNION CASTING .
Thank you very much for the answer.
It has been the closest thing to a real answer yet. No disrespect to any of
the other answers - they have been trying to address this from a front-end -
patch the SQL rather than the DB.
In answer to the first point you make, I am a consultant and this is what
they told me had already had been spent.
If it seems like an exaggeration I am sorry.
I have no come-back whatsoever, though.
I cannot prove it is necessary to do this
I am only experiencing a problem and trying to resolve.
PS the recommended workaround is to create a table on the views. As I said
already, I do not have that many GB.
I will try your sample and see.
Thanks
Chris.
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:988623137.5980.0.nnrp-14.9e984b29_at_news.demon.co.uk...
>
> 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
>
>
>
Received on Mon Apr 30 2001 - 05:27:12 CDT